I originally shared my Microsoft Excel Checkbook Register with the world in 2019 and you can download the newest version here. But you might find it rewarding to learn how to create a basic checkbook register in Excel on your own! The YouTube video below will walk you through how to create an Excel checkbook from scratch. It will feature a robust formula for calculating the balance that will not break if you delete or add new rows in the register.
Summary of Features
- Supports one bank account
- Drop-down list to optionally categorize your transactions
- Reminder of last check number used (always displayed at the top)
- Reminder of current balance (always displayed at the top)
There are three formulas that are mentioned in the video below for the Excel checkbook register. They are listed here for your copy/paste convenience.
Compatibility Note: You’ll need Microsoft Excel 2013 or newer for these formulas to function. If you get an error when putting any of these formulas into a spreadsheet, you likely have a version of Excel that’s too old. Getting a “circular reference error” can be okay, but “there’s a problem with this formula” will usually mean your version of Excel is too old.
Excel formula for checkbook balance
=INDEX([Balance],1)+SUM(INDEX([Deposit],1):[@Deposit])-SUM(INDEX([Withdrawal],1):[@Withdrawal])Excel formula to lookup the last check number
=LOOKUP(2,1 / (C:C<>""),C:C)Excel formula to lookup the last (current) balance
=LOOKUP(2,1 / (G:G<>""),G:G)