I haven’t written on a topic like this before, but I confess. I’m an Excel junkie. I love spreadsheets! As a joke, I even had my wife add something to her wedding vows about learning to at least like Microsoft Excel in the future.
I’ve been keeping my checkbook and house budget in Excel for a long time. I was a former Quicken user for many years, but the constant upgrades and cost became unpalatable. So I turned to Excel 6 years ago and it has worked well.
I have tried other sample Excel checkbook spreadsheets in the past, and they were decent. But they all had a problem. Any attempt to move (cut/paste) transactions around would result in #REF errors in the formula that’s used to calculate the current balance. So I’d constantly have to re-copy the correct formula to the affected cells. Not difficult. But definitely annoying! My version fixes that.
Why Use Excel for a Checkbook Register?
There are a lot of good reasons to keep a copy of your checkbook register in Excel. This includes:
- It’s easy to go back in time to find an old transaction. Most bank websites will limit or cripple your ability to see older transactions. Or they’ll offer your statements in PDF format, but searching across many PDF files becomes way too tedious.
- You can quickly see how much you’ve been spending in a particular category. Yes, that can be frightening! But helpful nonetheless.
- You can forecast how the rest of the month looks by entering bills that you know will come due, and then figure out how much money you’ll have left for other things. This is where my Excel spreadsheet shines over the others.
For me, the latter is a truly compelling reason to use Excel. If you’re like me, every month can be a little different for expenses, so rather than just wing it, I like to know how tight things might be in the ole bank account. My sample spreadsheet will help, and being able to enter future expenses is important. This is where all of the other sample spreadsheets I’ve seen ARE NO GOOD.
Download
Click here for my old version featured in the YouTube video. To see all of the latest versions of the Excel checkbook register spreadsheet, please visit this site dedicated to that content
What follows is a short video to explain the basics. You may want to watch this in a larger window so feel free to go straight to this YouTube link to do so.
Google Sheets Users
This spreadsheet can be imported into Google Sheets, and it will work. However, you’ll have to manually add rows as needed and copy the formula for ‘balance’ down to new rows.
Update! (Feb 2020) – Goal Tracking for Debt
The latest version includes a new sheet called Card Debt, which has a simple table to enter your current credit card balances along with the starting balance (usually the highest balance you’ve had on the card).
There are bar charts to help visualize your progress which includes a goal chart that consolidates the progress on all of your balances. You can read all about it here.
New to Using Excel?
If you need some help with how to delete rows, insert rows and/or how to fix or re-copy formulas, here’s a short video that covers all of that.
Thank you for your sample sheet. I’ve been running an excel sheet for well over a decade and it has been needing a refresh for a while.
I made an improvement to your form, where I conditionally hide the balance for the blank cells under your last entry (so the final balance doesn’t show repeating down the entire column).
In Col G (Balance) I went to conditional formatting and entered the condition formula “=$G13=$G12” (my manual entry balance is on G12 and my first calculated balance is in G13 since I added some rows up top for personal reasons).
Then I click the cell format button, and on the Number tab select it to be Custom (last selection).
In Custom (on the right) I enter the format of “;;;” (3 semicolons) which will effectively hide the text.
Now when the balance amount is equal to the balance above it, the value gets hidden for a nice clean and finalized look.
Thanks for the note! That’s an interesting way to suppress repeating balances! Thanks for sharing!
Thank you so much. I’ve been trying to make a register on my own for a while and had no luck. This is very helpful and I really like the credit card addition. Thanks for sharing.
Logan and Ben
I am new in Excel. I like very much your sample sheet. I want to use your sample sheet with the improvement that hides the repeated balance.
Please, can either one of you (Logan or Ben) post a step by step instruction on how to put the formula in the the cells so that I can make your sample sheet works for me? I will appreciate it greatly. Thanks, Kes
Hi Kes, I tried to duplicate Ken’s trick where I used the formula of “=$G5=$G4” (based on the sample spreadsheet where G5 has the first calculated balance), but my end result didn’t work out. I also tried “=$G2=$G1” which almost works but the last balance that should show is hidden. So I wasn’t able to duplicate Ken’s success.
Hi Logan. Thank you so much for sharing this! I’m having some trouble. When I erase all the entries that your template has so that I can start with a clean sheet, it doesn’t seem to work. Any suggestions?
I figured it out. I realized that I just needed to delete cells individually and not touch the balance cells.
Thanks for your note. I think I’ll add a note or something to help new users know to not touch the balance cells. Cheers!
Thank you for putting this together. It’s very nicely done. I own a small business, but am having trouble trying to figure out how to set up and use the pivot table. Could you possibly do a video on how to set up a pivot table for the check book example video you posted previously? It would be greatly appreciated.
Thank you so much,
Dave
Hi Dave, it would probably be a while before I could create a video on that topic. But in the meantime, here are two highly upvoted videos on creating pivot tables in Excel that I hope will help.
Video 1
Video 2
Love the spreadsheet as an alternative to the infernal Quicken. Question: I added a ton of categories and updated the pivot table as per your video, but it’s not picking up all the categories. Is there a limit? I have 56!
Hey Trent, glad you’re finding it useful. On the issue with the categories, is it the pie chart and/or pivot table that’s not picking up all your categories? Or is it a problem when entering transactions in your checkbook sheet?
Is there a way to reconcile your spreadsheet when I get my bank statement?
Hi Kristi, that’s a great question. Back in my days of using Quicken, I would go through the tedious task of reconciling transactions. I felt doing so was especially important since I wrote a fair number of paper checks, and I needed to make sure everything was accounted for. I don’t know about you but nowadays, I rarely write any checks. So the task of reconciling isn’t as important. In the “Part 2” video, I show how I often download recent transactions from my bank website and copy/paste them into my checkbook spreadsheet. That’s what I recommend instead of the old approach of a tedious “reconcile”.
But some users have added a new column to this checkbook spreadsheet, and they label it as Reconcile. And they just go through their statements and mark that new column with an “X” beside each row as they review their statements. Let me know if you need some steps on adding a new column to the spreadsheet?
Thank you for designing this!! I split my credit card bills into categories when I was using Quickbooks. Is there a way an easy way to split them in excel? Exxample: $250 credit card bill but $125 auto maintenance and $125 groceries. Thank you in advance for your help.
I remember that feature in Quicken. It was nice! And with Quicken, (like you said) you could categorize each sub-item so that reports and charts would allocate them to any unique categories. Unfortunately, it’s not possible in a spreadsheet like this to do what Quicken can do.
Two workarounds that I can think of are: (1) list each item in separate rows as if they were individual charges, and as long as your numbers are good so that it matches with the total bank charge, your balance in the spreadsheet should stay accurate. Or, (2) just put it as a single entry, but add some extra info into the description line, e.g., Post office ($12 for stamps; $23 for box; $5 for tape)
If you like the pie chart stuff, I’d go with the first option so that your categorization of expenses is reflected in the chart.
Thank you for sharing this! I would like to split a credit card into categories. Example: $250 credit card bill but $125 in auto and $125 in groceries. What is the best way to do this in excel? Thank you!