I am an accountant by trade, but I love to spend my free time in my workshop building different things.
Keeping an accurate record of your checking account is vital. Using the check register that comes with your checkbook requires a manual calculation every time you make an entry. In my experience as an accountant, a high frequency of manual calculations results in a higher rate of errors. It is so easy to either add something wrong or even write it down wrong. I used to use Quicken because it was fully automated and had a lot of great tools built in, but it became expensive as they would require you to upgrade every few years to keep it fully functional. After that, I did a search for checkbook register templates and did not find anything that I liked. Most were very generic and were too basic for what I wanted. Using Microsoft Excel, I created my own checkbook register template.
Easy-to-Follow Step-by-Step Instructions
One of the best things about creating your own spreadsheet templates is that you can customize it how you want. You can add a Dashboard Tab that can be a quick look into what you decide to track. It could be income tax related items, such as property taxes, charitable giving, etc. It could be that you want to track your balance over time and place a line chart for that. Let us begin by opening a new workbook in Excel.
Start by renaming one of the tabs as the “Categories” tab. This tab will be where all of the potential income, expense, and whatever other categories you can think of are stored.
- In cell B4, type “Categories” and then format the cell as bold and change the alignment to center.
- In cell B5, enter “*** Income ***” as a section heading.
- Beginning in Cell B6 and continuing downward, enter any income categories that you want to track. I suggest having at least the following categories: Wages, Gifts Received, Interest Income, Dividend Income, and Miscellaneous Income. Feel free to customize as you see fit. You may want to break wages into more than one category if you have more than one income stream.
- Leave a blank after you last income categories and then enter “*** Expenses ***” as a section heading.
- Enter all of the expense categories that you want below the expenses heading. These can be as broad or as narrow as you wish. If you choose to enter any other headings, be sure to keep the same format with the three asterisks before and after the title.
- As a preference, I like to shade cell B4 as a darker color and change the font color to white. Also, highlight from B5 to a few cells past your last expense entry, shade this area a lighter color, and put a border around it to make it look nice and neat.
- You can still add more categories by inserting rows inside of the shaded area.
- Finally, highlight from cell B5 to the end of the shaded section. In the Name Box, which is to the left of the formula bar (it should read B5), type “Categories” into the box. This sets up a name range that we will use on the next “Checking Account Register” Tab.
Checking Account Register Tab
This will be the main tab of the spreadsheet. All of your transactions will be entered on this tab. It will also be useful in reconciling your account balance. Start by renaming one of the blank tabs as “Checking Account Register”.
- In cell A1, enter a title for your account register. I called mine “Checking Transaction Register.” Change the font size to 28.
- Highlight cells A1:J1 and shade the area whatever color that you like. If it makes sense, change the font color so that it is easily readable and looks nice. While you have this area highlighted, add a bottom border to it.
- In cell A4, enter “Date”.
- In cell B3, enter “Reference/”. In cell B4, enter “Number”.
- In cell C4, enter “Payee”.
- In cell D3, enter “Expense”. In cell D4, enter “Category”.
- In cell E4, enter “Memo”.
- In cell F4, enter “Deposit”.
- In cell G4, enter “Payment”.
- In cell H4, enter “Clr”.
- In cell I3, enter “Account”. In cell I4, enter “Balance”.
- In cell J3, enter “Cleared”. In cell J4, enter “Balance”.
- Highlight A3:J4 and shade the area whatever color that you like. Also, change the font color as appropriate. Change the alignment for all of these cells to center.
- Change the column widths so that the register takes up the whole screen. I would give you my widths, but depending on your settings, they are going to be different.
- Highlight cells A5:A4000 and change the format to “Short Date”, which can be done either by clicking the drop down on Excel’s “Home” tab or by right-clicking and selecting “Format Cells.” I chose row 4000 randomly and so you would not have to insert any rows any time soon. I would suggest typing “End” into cells A4000:J4000 as a placeholder for navigating the spreadsheet. I will explain why in more detail later on.
- Highlight cells B5:B4000 by clicking B5 and then hold down Shift-End-Down, which will highlight all the way down B4000. This is why I had you copy “End” across row 4000 in the last step. Using End-Down moves the cursor either down to the bottom of a list or to the next cell with something in it. Holding the Shift key down makes Excel highlight that section. Change the alignment of the cell to Left.
- Click on cell D5. On Excel’s “Data” menu, click on the “Data Validation” button and select “Date Validation” from the drop down. This will bring up an input box. Look for the “Allow:” drop down box, select “List” from the available choices. Under “Source:” enter “=Categories” into the box. Click “Ok.” Copy cell D5, using Shift-End-Down, paste the formula all the way down to row 4000. This will add a drop down box that has all of the expense categories that you entered on the “Category” tab.
- Highlight cells D5:D4000 and click on the “Conditional Formatting” button on Excel’s “Home” menu and select “New Rule. . .” This will bring up an input box. Under “Select Rule Type:”, select “Format only cells that contain.” Under the “Edit the Rule Description:” section, change the drop down that says “Cell Value” to “Specific Text.” In the box on the far right, enter “*” and then click on the “Format” button. Click on the “Font” tab and change the “Font” color to white. Then click on the “Fill” tab and select one of the red colors. Click “Ok” to close the format box and click “Ok” again on the rule-formatting box. This will change the cell color under “Expense Category” (Column D), to become red if you select one of the category headings.
- Highlight cells F5:G4000 by clicking in F5 and using Shift-End-Down and while continuing to hold the Shift key, hit the Left Arrow button once, which will highlight column G. Change the cell format to currency.
- Repeat the same process in I5:J4000.
- Highlight the entire Column H, change the alignment to center, and format it as bold.
- In cell I5, enter the following formula: =F5-G5.
- In cell I6, enter the following formula: =F6-G6+I5. This will automatically update your account total. Copy this formula down by copying cell I6 and holding down Shift-End-Delete and then paste the formula.
- In cell J5, enter the following formula: =F5-G5.
- In cell J6, enter the following formula: =if(H6=”C”,F6-G6+J5,J5). The idea of this formula is to help you to reconcile your checking account register versus your bank account balance. It only calculates the rows that have a “C” (Cleared) in column H. Whereas, Column I is your real account balance even though some things have not cleared the bank. Copy this formula down using the same method as above.
- Shade A5:H5 a darker color and change the font color to a lighter color.
- In cell C5, enter “Beginning Balance”.
- In cell F5, enter in your beginning checking account balance.
- I choose to format my template by shading every other row light blue and the other row as white. A quick tip to doing this is to do it for two rows. Highlight those rows and click on the “Format Painter” Icon on Excel’s “Home” tab. Highlight the next row (A-J) all the way to row 4000. I also added a border around every box in the register including vertical double lines in column F (left side), Column H (both sides), and Column J (Right side).
Using Your Checkbook Register
Your checkbook register is now ready to use. Enter in all of your transactions as they occur. Be sure to enter the date of the transaction, a reference number, expense category, any memo information, and enter the dollar amount in the appropriate column. Balancing the checkbook register can be done by typing a “C” in Column H when something clears the bank. The “Cleared Balance” should tie to your bank account. Balancing your account often is a great way to make sure that your records and the banks are accurate. I recommend taking a few minutes and reconciling it weekly. This is a great way to also guard against fraud because you will know if anything suspicious hits your account.
This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.
Jessica on March 05, 2020:
Is there an alternative way to the categories? I currently only have the web version and it will not let me rename the list Categories... any help with another way this can be done?
Eric on January 22, 2018:
Great setup instructions. They are very helpful. Any way to then create a list that shows the amounts for each category? For example, the total for "Wages" at a glance. ideally it would be great to do that for a selected date range...
Chandra on January 05, 2018:
This was a wonderful find. Exactly what I needed. I have spent the last year trying to teach myself how to use excel. I am still using the paper cheque register book. Needed a more efficient way to balance the cheque book. I could not get the shift-end-down to work. That would be so quick. Not sure what I am doing wrong. Also - do you actually use the quotation marks when you enter "categories" or "deposit" for example? Thank you in advance.
Beth on August 06, 2017:
Great! Thank you so much :)!
Robbie on May 11, 2017:
Thank you so much for the tutorial! I have been doing things the old fashioned way and it has become quite time consuming. This will definitely help a lot! I have a question about the "Category" column. When I click on the drop down box next to the cell, it doesn't show my list of categories. The only option it givens on which to click is my formula of "=Categories". I am sure it's something I did or did not do. Where did I go wrong? Thank you for your help.
Abby on December 07, 2016:
This was so simple to follow that even *I* was able to create it. THANK YOU. My only problem is what Jonathan and Amanda referenced above. I tried both formulas in the J column but if a previous entry does not have a "C" entered in the H column, then the H column can't compute a value. I get a #VALUE! in that cell. Any suggestions for this incredibly technology-challenged individual?
Jonathan on November 12, 2016:
With Excel 2016 you need to remove the last J5 from "=if(H6=”C”,F6-G6+J5,J5)" so it would look like: =if(h6="C",F6-G6+J5). I put above formula and Amanda's value in my checkbook and it looked like: =IF(H6="C",F6-G6+J5,IF(H6=ISBLANK(cell),"",cell)). Worked like a charm! Thanks for the ideas.
Amanda on October 20, 2013:
AH! I figured it out. I embedded an IF(ISBLANK(cell),"",cell) within the 'false value' of the formula you suggested. SWEET! thanks for the tutorial and the brainstorming!
Amanda on October 20, 2013:
Though, I must add that I agree with John: this tutorial is what I was looking for! I had my register mostly made, but needed a simple way to quickly and easily reconcile my register with my account. I'm very pleased with your reconciliation method! Too bad I couldn't have thought of it on my own, that would have saved me a lot of googling time. SO thank you for that idea! And thank you for reminding me of conditional formatting; I had wanted to color code some of my categories but was stumped. Since that was a 'duh' moment, I'm hoping my prior question about the formula will also have a simple answer. :)
Amanda on October 20, 2013:
The formula for the "Cleared Balance" column does not allow a way to keep the cell blank if there is no transaction data entered at all. I guess neither does the balance column. I hadn't noticed the Balance column having that problem, too, because I used an IF formula (if the date is blank leave the balance blank, otherwise add it up). For the Cleared Balance formula I tried imbedding another IF in the "false" value to keep the cell blank if there was no transaction entered. It didn't work. Any suggestions?
Eric Cramer (author) from Chicagoland on July 25, 2012:
You are welcome. I am glad that you can use the instructions. Let me know if you have any questions.
John on July 25, 2012:
Exactly what I was looking for. Thank you for making this ever so easy to follow tutorial for all of us out here.