I originally posted my free Microsoft Excel Checkbook Register in 2019. After lots of positive feedback on YouTube, I was recently inspired to update it. There’s now a snazzy but easy to follow dashboard, improved categorization options, and a few other minor fixes.
The download link will be near the bottom, but first let me share about the tweaks and changes.
Types, Categories and Sub-Categories
The first notable change regards category settings for each transaction. In order to make the new dashboard charts work, there are now sub-categories, categories and types. But the good thing is, you only have to choose a sub-category and the last two columns will automatically populate thanks to a function in Excel called XLOOKUP.
Bug Fix for categories after adding new rows
The previous version had a problem after you added new rows. The category drop-down stopped working in newly added rows. This was associated with having a “Total Row” turned on in the table. So the new version has that turned off so that the data validation for categories continues to work for newly added rows.
Running subtotal no longer repeats on empty rows
Previously, the running subtotal would repeat all of the way to the bottom of the register. For some, it was annoying to see that. So now there’s conditional formatting on that column to hide the subtotal unless there’s something in the description field.
New dashboard
The new version has a worksheet entitled Dashboard, which shows totals of expenses per month along with a breakdown by broad categories and also sub-categories. There’s a slicer control on the left-side which lets you click on a particular month, and the charts will automatically change to reflect the values for that chosen month.
The green download button above will open a ZIP file with THREE Excel files inside. One of them is a macro-enabled version that has a very simple macro inside. It’s connected to a button that will refresh the pivot tables with your latest register entries. The other two won’t have a macro.
January 2024: To see all of the latest versions of the Excel checkbook register spreadsheet, please visit this site dedicated to that content
If you’d rather not download a ZIP file and/or if you’d prefer the version without a macro, here’s the non-macro version.
To see a brief walk-through of the new version, see the YouTube video below.
See my earlier post on the basics of using Excel for a checkbook register.
Hi Logan, I am trying to work with the NEW updated April 2022 version of the checking3 registers with macros from the above zip/download. Every time I add from last/bottom entry of the list and tab to make additional rows of Sub-Category, Category, Type the register – thereafter, the register only shows Sub-Category correctly and Category + Type become columns of #NAME? – effectively rendering the concept mute. I have tried and tried to work around it and I don’t know what else to do. Any advice, cz I REALLY like the table format with all your bells and whistles you’ve graciously & expertly included. I’d LOVE to be able to make it work the way it should, as per watching ALL your youtube videos. IDK and am frustrated. Hope you get this message and are able to assist, somehow.
I am traveling today but will look into this soon.
I too am experiencing this same problem. Have you been able to resolve?
Hi Steve, I had emailed the other fellow directly but never heard back. I’ve been trying to reproduce the issue but haven’t been able to as of yet. Can you let me know which file (macro or non-macro) you are using? And maybe how many extra rows of categories/sub-categories have been added?
Hi Steve, I think I found the issue. I corrected the issue in the download which existed in the 3rd register sheet (register3). The formulas were missing from the Category and Type columns. You can fix your existing spreadsheet by copying the formulas in the earlier cells in the Category and Type columns. Let me know if you need help with that.
Hi there, I think I found the issue. I corrected the issue in the download which existed in the 3rd register sheet (register3). The formulas were missing from the Category and Type columns. You can fix your existing spreadsheet by copying the formulas in the earlier cells in the Category and Type columns. Let me know if you need help with that.
Hello,
I don’t understand how to fix the problem, from where should I copy the correct formulas?
A good formula will be any of the cells above in the same column. It usually doesn’t matter which “good cell” you copy from, but it should be from a cell higher up in the same column where you are seeing an error or problem. Here’s someone else’s short video on how to copy formulas.
https://www.youtube.com/watch?v=zcA9MMskIRg