How to Balance Your Checkbook With Templates and Spreadsheets
Tips for Using an Excel, Google Sheets, or Paper Check Register
Do you want to know exactly how much you have available to spend from your bank account? Would you like to catch errors (including bank errors and mistakes you’ve made) before they cause major problems? Balancing your bank account helps you keep track of everything in your account, and it’s a relatively easy task.
You may already record the checks you've written in your check register, but there are additional ways to track the activity in your accounts.
- Excel Spreadsheets: For Microsoft Office users, there’s a simple Excel spreadsheet template as well as more robust versions that track your spending categories for you.
- Google Sheets: To track everything in your Google account, use the classic checkbook version or go with the simplest possible design.
- Open Office: Open source fans also have a template available.
- Pen and Paper: If you prefer to go analog, check the back of your bank statements—there’s probably a template printed there. You can also download and print a PDF form.
How to Balance Your Account
No matter which format you use, you should be able to accomplish two critical tasks:
- Know how much money you have available for spending.
- Review transactions to spot errors or signs of identity theft in your account.
Any of the templates above will help you do that. To get started, grab your most recent account balance. Traditionally, you’d get that number from your monthly statement, but you can also get an up-to-the-minute balance online. (There’s still value in balancing your account each month, even if you can see your balance online—it helps you catch mistakes and encourages mindful spending.)
Look for an entry in your account called "ending balance," "previous ending balance," or "beginning balance." Enter this figure on your form or spreadsheet.
Add Outstanding Deposits
Next, add any missing deposits and credits to the balance above. These are items that haven't yet shown as transactions in your account, but that you’re certain will be credited.
For example, you might have deposited funds at an ATM over the weekend. You know from experience that your bank will credit the full amount to your account on Monday. Or you might know that your paycheck always arrives in your account on a certain day.
Enter each of these credits or deposits individually. For spreadsheets and templates that have separate columns for deposits and withdrawals, put these transactions under the column labeled “Credits.”
Subtract Outstanding Payments
Then, subtract outstanding items such as withdrawals and payments that haven't yet shown up as transactions but which you know will hit your account soon. For example, you might have written a check to somebody who has not yet cashed it. You should have a record of that check in your check register. Or, you might know that your mortgage payment will be deducted automatically soon. Make sure to subtract that amount.
On your template or spreadsheet, these figures go in the column labeled “Debits.”
Do the Math
You now have the following:
- The balance reported by your bank
- Additions to your account
- Items to subtract from your account
Once you've added the deposits and subtracted the debits, you’ll see the new balance of your account.
Look through every transaction on your bank statement (or online) and compare any checks paid to your check register. If you’re not sure what something is, figure it out. It could be a sign of identity theft, a bank error, or a even a “gray charge”—those little charges you don't notice but which can quickly add up, such as an unused subscription you keep meaning to cancel, a monthly fee that keeps creeping upward, or a new charge from a company when you've already canceled. Reviewing your transactions lets you find any pesky, bank account-draining charges like these and take care of them for good.
One easy way to review transactions is to mark the transactions that are legitimate and that you’ve cross-referenced with your check register.
- If you’re doing this on paper, put a checkmark next to each item in your check register after you find it on your bank statement (and put a checkmark next to each item on your statement after you find it in your check register).
- If you’re doing this in a spreadsheet, flag the transactions that you’ve reconciled with something, perhaps a “1.” That way, you can easily sort your spreadsheet to find any transactions that are missing that flag. Alternatively, color the cells of reconciled transactions so that you can easily scan and see if anything is missing.
Now that you’ve reviewed every transaction, your account should be free of any surprises. But you can do even more to optimize your bank account.
- Set up alerts so that your bank automatically notifies you of any large withdrawals, or if your balance falls below a certain level. Most banks and credit unions can send text or email alerts based on rules that you specify.
- Keep a buffer of cash in your account so that you can absorb any surprises. Insufficient funds fees can cost $35 or so, and your bank can still apply those charges even if you opt out of overdraft protection.
- Evaluate your overdraft options and decide if you want overdraft protection on your account. If you rely on overdrafts to fund your spending (or “avoid embarrassment”), there’s a problem. But if you keep it there just in case of emergencies (and therefore never paying the fees), it can make sense. Some options, like a transfer from savings or an overdraft line of credit, may be less expensive than traditional overdraft protection.