Please ensure Javascript is enabled for purposes of website accessibility

How to Balance Your Checkbook Using an Excel Spreadsheet

By Motley Fool Staff – Mar 27, 2016 at 11:44PM

You’re reading a free article with opinions that may differ from The Motley Fool’s Premium Investing Services. Become a Motley Fool member today to get instant access to our top analyst recommendations, in-depth research, investing resources, and more. Learn More

For some people, a simple spreadsheet is the best way to handle financial matters.

Many people have trouble keeping their checkbooks balanced. Now that debit cards have made it easy to have money electronically transferred out of your bank account without even having to write a check, it takes extra effort to make sure you're tracking your money appropriately. With the help of a simple spreadsheet, many people manage their finances more closely and keep tabs on exactly how much money they have available on a day-to-day basis. Below, we'll look at how to set up a spreadsheet to help you balance your checkbook.

How to build the spreadsheet
Your check register can be a great guide to start your checkbook-balancing spreadsheet. You'll want to include columns for check numbers if any, transaction date, a description, and the amount of payment or deposit. A final column keeps the running balance, and you can add a separate column to hold a checkmark when items actually hit your account.

The only thing that's particularly tricky is figuring out how to do a running balance. In general, you'll want to do a formula that takes the balance from the previous line, adds in any amount in the deposit column, and subtracts out any amount in the payment column. That way, your balance will go down when you write a check or make a payment, and it will go up when you make a deposit -- just like you'd expect.

Balancing your checkbook
Each month, you'll get a statement that includes your recent checking account activity. Occasionally, the final balance will match up to one of the running balance figures in your spreadsheet. If that's the case, you can usually conclude that you're in balance.

Most of the time, though, some items will get credited to or debited from your account slightly out of order. In that case, you'll need to adjust your running balance to account for the missing items before comparing them to the balance on the statement. To do so, take the running balance for the last item to hit your account. Then add back in any payments that didn't hit your account, while subtracting any deposits that don't show up on your statement. Once you've done that, the balances should match up.

There are specialty software programs that use interfaces with banks to do much of this work automatically. For some, though, using a spreadsheet is a more satisfying and reliable way to make sure your finances are behaving the way you think they are.

This article is part of The Motley Fool's Knowledge Center, which was created based on the collected wisdom of a fantastic community of investors. We'd love to hear your questions, thoughts, and opinions on the Knowledge Center in general or this page in particular. Your input will help us help the world invest, better! Email us at [email protected]. Thanks -- and Fool on!

We Fools may not all hold the same opinions, but we all believe that considering a diverse range of insights makes us better investors. The Motley Fool has a disclosure policy.

Invest Smarter with The Motley Fool

Join Nearly 1 Million Premium Members Receiving…

  • New Stock Picks Each Month
  • Detailed Analysis of Companies
  • Model Portfolios
  • Live Streaming During Market Hours
  • And Much More
Get Started Now


Motley Fool Returns

Motley Fool Stock Advisor

Market-beating stocks from our award-winning analyst team.

Stock Advisor Returns
S&P 500 Returns

Calculated by average return of all stock recommendations since inception of the Stock Advisor service in February of 2002. Returns as of 12/01/2022.

Discounted offers are only available to new members. Stock Advisor list price is $199 per year.

Premium Investing Services

Invest better with The Motley Fool. Get stock recommendations, portfolio guidance, and more from The Motley Fool's premium services.