Menu Close

How to Create a Checkbook Register in Excel

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.

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)

YouTube Video entitled How to Create a Checkbook Register in Excel