A community member asked if there was anything like a “Checkbook Register” that not only includes the date, description and amount of a transaction, but a running balance so you can see what remains in your account. A few others have asked for similar functionality, and I thought the code required is similar to a few templates I’ve already released or are in the works, so I was able to throw this together pretty quickly.
Getting a reliable running balance requires having a reliable starting balance. Because of this, the “Account Register” template relies on the “Account Reconciliation” template. You can find information on installing and using Account Reconciliation here. Once installed, take the time to fully reconcile the accounts that you’d like to use in the “Account Register” template. “Account Register” gets it’s list of accounts, and their opening dates and balances from “Account Reconciliation”, so it’s an important step. Once you have that done:
Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data. (The instructions are for the desktop version of Excel, the web version is not recommended for the installation but does work once the Account Register sheet is installed.)
At this point your new template should be functional and linked to your local workbook’s data.
Usage
Once the template has been copied, it’s a simple matter of choosing the account you want to see the register for, and the time period you’d like to see the transactions from, both selections are highlighted in green. Everything looks correct in my setup (the balances match those on my Balance sheet), but use caution and don’t base any important financial decisions on this template without double-checking the numbers! Let me know if you run into any problems or if the numbers aren’t coming up right.
Notes
I kept this pretty simple, mimicking the functionality of a real checkbook register. Note that it’s use isn’t limited to checking accounts, it should work fine with any accounts that you reconcile transactions for in Account Reconciliation. There’s also a Google Sheets version here.
We are so happy you continue to build and share your templates on both Google and Excel, @jpfieber. This is another great addition to this community’s personal-finance toolset.
Hi Super Hero, I’ve enjoyed trying many of your amazing worksheet templates that you have shared with us this year. Your template examples have vastly improved my Excel skills.
I’m very happy that I tried Tiller Money and it helps me to have the wonderful skills, customize your templates and create my own worksheets to fit my needs. I’m now looking for tax-related templates to help me prepare annual tax returns.
Anyway, I just downloaded this account register template and attempted to edit links to change source, but unfortunately, it failed due to error.
Do you already have the Account Reconciliation template installed and configured? Account Register references that sheet to get accurate account information.
Oh, it requires Account Reconciliation template that I no longer use. I created my own worksheet to simply manage checking account and credit account only.
Thanks for creating this. For most of my accounts it worked well, but some of them were giving incorrect results. I found that some transactions were missing from the register. By eliminating the “Unique” filter in the helper data formula I was able to correct this. Was there a reason to include this?
You’re right, I likely recycled that code from somewhere else and didn’t realize in this instance, UNIQUE not only isn’t needed, it could cause problems. Thanks for finding this, I corrected in the shared template and bumped the version to 1.13.
Hi. I really like this functionality. I downloaded the account register template and moved it into my foundation template. I changed the data source as needed.
However, when I try to select an account, all it shows is #calc!
My accounts are showing in the accounts tab and balances in the balances tab.
