How to Create A Checkbook Register in MS Excel

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version
Creating checkbook register in Excel

If you have a checking account with your bank, or a checking account + an ATM card, you might need this Excel tips on creating a spreadsheet version of your checkbook register. In this way, you can easily monitor your checking account, your account balances and expenditures anywhere you are.

This MS Excel method is a kind of simulation of a Checkbook software by http://www.datawaregames.com called "Checkbook" which is available at Amazon. However, since this Excel approach is using only a formula, no VBA, there are no fancy behavior included. It will simply allow you to record your issued checks, clear each transaction so that it will reflect your correct balance.

Checkbook register softwareSample checkbook

Checkbook Register Features

In this Checkbook Register, you can:

  • Input information like Date, Check Number, Transaction description, Debit Amount, Credit Amount, and clearing or posting status
  • Input Opening Balance that is equal to your checking account balance
  • Clear checks issued to reflect the correct balance of your account
  • Monitor your ending balance

Creating the Excel Checkbook Register

This demo will be using MS Excel 2010 but this will also work with MS Excel 2007 or 2013 though the provided screen shots may be different.

  1. Open a new and blank Excel worksheet
  2. In cell B2, type Date
  3. In cell C2, type Check Number
  4. In cell D2, type Transaction Description
  5. In cell E2, type Cleared
  6. In cell F2, type Debit
  7. In cell G2, type Credit
  8. In cell H2, type Balance
  9. Resize the columns so that the expected entries will fit well.
  10. Resize Column A as well since we started the register in Column B
  11. Select or highlight the cell range B2:H20
  12. Click on Insert tab, then click on Table in the Tables group.
  13. In the Create Table pop-up, check the option "My table has headers"
  14. Then click OK button.
    Creating a table in MS Excel
  15. Then the selecting cell range should be converted to a table like the image below. Then you can change the look of your register by applying a pre-defined Table Style. In this sample, I am using Table Style Medium 16.
    Sample table layout in Excel

Now, the checkbook register is almost ready for your entries. The next thing that we need to do is assign a location where you can enter your Opening Balance. This is the balance of your checking account at the time of monitoring. You will also need to monitor your Ending Balance or Current Balance. In this sample, we need to use cells D22 and E22 for this purpose.

Opening and Ending balances

NOTES:

The Opening Balance is where you enter your current or checking account balance. This will not change any time you enter your transactions in the register. This is the base of the calculation of your Ending Balance.

The Ending Balance on the other hand is the running balance of your register. It automatically reflects the debit and credit transactions you entered even though the checks are not yet cleared. The actual status of check clearance or posting is shown in the Balance Column H.

  1. Now, let us deal with the control of check clearing as required in Column E. We have 2 options available. First is using a check mark to indicate that the check is cleared. Second is we can use Yes or No reponses. In either options, we will use "Data Validation" so that Column E cells will just allow the check mark, Yes or No in their values.
  2. We will use a helper cell or cells that we will just hide. In this sample, we use A2 and put a check mark there.
  3. When the check mark is entered in cell A2, resize column A to hide the content.
  4. Highlight cell range E3:E20 to apply data validation rules.
  5. Click Data tab/menu, then click Data Validation in Data Tools group.
  6. For the Settings, select List and check the 2 check boxes.
  7. For the Source, click on the hide button then select cell A2 containing the check mark, or simply enter the formula =$A$2 then click OK.
    Using data validation for check clearing

At this point, your checkbook register is now almost ready except the Balance Column H and the Ending Balance in cell D23. In order to calculate the running balance in the Balance Column, here are few restrictions:

  • If the transaction is a credit, like a bank deposit, it shall be added to the running balance automatically
  • If the transaction is a debit, like a check issued for groceries and other bills, clearance status must be applied.
  • If the check is cleared, you must put the check mark in column E. That is the only time that the amount is debited in the running balance,
  • Otherwise, if the check is not yet cleared, the amount is not yet debited, and a message "Check clearing/posting" will be displayed instead.

In order to complete the Balance column H, please follow the steps below:

  1. Select cell H3, this is the very first cell in the table and it will be using a unique formula utilizing the Opening Balance.
  2. In H3, enter the formula =D22-F3+G3. This means that the first line will use take the opening balance, and then add or subtract the line transaction.
  3. When you press Enter, the formula is copied to the rows down column H. This is not right, so click H4 and then enter this formula:
    =IFERROR(IF(ISBLANK(G4),IF(AND(ISBLANK(E4),ISBLANK(F4)),"",IF(E4="","Waiting for clearing or posting",H3-F4)),H3+G4),"")
  4. Then hold down the fill handle of cell H4 and drag down to copy the formula.

The formula above simply performs the following:

  • It will first check IF the Debit AND the Credit cells are blank. If they are, then the balance will show a blank cell.
  • If the transaction is a credit, it will be added to the balance automatically.
  • If the transaction is a debit, it will first check if the issued check is cleared or not. To clear a check, simply put the check mark in the "Cleared" column.
  • Of the check is cleared, it will be debited to the balance.
  • If the check is NOT yet cleared, it will display the message "Check clearing/posting" and the amount is NOT yet debited in the Balance column. However, the Ending Balance will always reflect the actual running balance of the account regardless of the check clearing status.

With the formula above populated in the Balance column, you are almost ready to enter transaction details in the sheet. The last thing and also the most important thing in the sheet is the Ending Balance calculation.

This is done with a trick - that is, using a helper column. In this sample, I will be using column J as the helper column. In the final file that is available for download at the bottom of this post, column J will be hidden. But I will show it here for screen shot purposes.

What will be done in column J is simply this: it should emulate the entire Column H, except that, it will NOT check the clearing status of the debit transactions. That is, regardless of the status of the check, eaither a checkmark is there or not, the debit amount will be deducted from the running balance. In order to do this, here is the formula:

  1. Select cell J3 and enter this formula:
    =D22-F3+G3
  2. Select cell J4 and enter this formula:
    =IF(AND(ISBLANK(F4),ISBLANK(G4)),"",J3-F4+G4)
  3. That formula will simply check if the Debit AND the Credit values are blank. If they are, then the result shall be blank. If not, then it shall perform the debit-credit operations againt the running balance above it.
  4. Now, select J4 and drag the fill handle downwards to copy the formula.
  5. With few transactions entered, it shall look like this:
    The helper column to compute ending balance
     
  6. With the fomula copied to the cells below J4, you are now ready to HIDE column J and then calculate the Ending Balance in D23.
  7. To hide column J, right-click on the column heading (the letter J itself) to select the entire column, then select Hide.
    Hiding the helper column
  8. At this point, you are now ready to calculate the Ending Balnce using the helper column J. The idea is, the Endling Balance will always be the BOTTOM value in the column J.
  9. So select cell D23 and then enter this formula:
    =LOOKUP(9.99E+307,J:J)
  1. That formula will just search the lowest or the bottom value in the entire column J since that value is always be the running balance of the account. It shall now look like this (with the helper column hidden.
    The ending balance
  2. As you might have noticed, in the balance column, the last figure is not the same as the Ending Balance since the former depends of clearing status of the last check while the later disregards check clearing status.

If you want to play with the completed sample Checkbook Register file, you can download it using the link below.

file_attachments: