How to Create a Checkbook Register in Excel
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”.
How do you keep your checkbook 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.