Creating a Check Register in Microsoft Excel (or an Excel Checkbook) isn’t the most complex thing in the world to build, but it is one that you definitely want to spend the extra bit of time making sure it works. Not that you can’t easily go to your online checking account and see what your balance is when you are in a pinch, but keeping track of all of your expenses is something that everyone should do to allow you to better manage your finances.

I use this Excel check register in my personal finances to not only keep track of everything that I am spending, but also to help forecast where I will be tight or loose on my finances. This check register is simple enough to keep me in track, without all the other fancy bells and whistles that just add clutter.

So, if you are curious on how to create a check register in Excel yourself, please continue reading. If not, I have an already packaged an Excel Check Register Template, which is available download below.

Excel Check Register / Checkbook Template

Availability: Tested in Microsoft Excel 2007-2013
Features: Total Deposits, Total Withdrawals, Available Balance, General Account Information
Functionality: Excel table (used for check register/checkbook), data validated categories (available option in register)

Excel Check Register 75.15 KB 2963 downloads

Use this template to make a check register in Excel. ...

Creating the check register in Microsoft Excel

To create this check register it can be done in just a handful of steps. I’m only going to outline the minimum items you need to get the check register working, but feel free to add to this concept as much as you like. After all, you are making it, so it is yours to modify as you wish.

Identifying the columns needed for the check register

First up, outline the columns that you want in your register. At minimum, you should have the following columns identified, but feel free to add as many as you like.
[bullet_list icon=”check”]

  • Date – Got to store the date of the transaction somewhere.
  • Description – Identify what the transaction was all about
  • Withdrawal – Was this a withdrawal on the account
  • Deposit – Was this a deposit on the account
  • Balance – Finally, how much money I gots!

[/bullet_list]

Creating the check register and making it work

Again, simple is the way to go here. Add these columns to your worksheet on a single row, preferably at the top of your workbook in row 1. After these columns are entered, move to the second row (beneath) and go to the balance column. In that column, the balance should be a fixed number. This number will identify as your starting balance and all calculations will be generated off of this number (so make sure it’s right!).

Now that we got that out of the way, go to the third row and again go to the balance column and enter in a formula something like the below:

Concept: =IF(AND([withdrawal]="",[deposit]=""),0,[previous_balance]+[deposit]-[withdrawal])
Example: =IF(AND(E3="",D3=""),0,F2+E3-D3)

After you have this formula entered in your ‘Balance’ column, copy it down as many rows as needed and begin entering and tracking your expenses. See there, told you this was fairly straight forward. If you do run in to any snags along the way, download my example from above and look at the formulas that I am using to create the register. Enjoy!

Tags: