Excel Formula for Checkbook Balance? Simple Steps

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for a checkbook balance is =SUM(B2:B100) for deposits and =SUM(C2:C100) for withdrawals, then subtract the two: =SUM(B2:B100)-SUM(C2:C100). For a running balance that updates row by row, use =D2+B3-C3 in cell D3, assuming column D holds the running balance.

Why Use Excel for a Checkbook?

Most people switch to Excel because a paper register forces manual addition and subtraction, which is error-prone. Excel automates the math, lets you sort transactions by date or category, and makes it easy to spot bank errors. Unlike a bank’s online view, a spreadsheet shows your own records, including pending checks and deposits that haven’t cleared. This is the core of reconciling—matching your records to the bank’s statement.

A basic checkbook spreadsheet usually has columns for Date, Description, Withdrawal (or Debit), Deposit (or Credit), and Balance. You can add a Category column later for budgeting. The key is to set up the formulas once, then just type amounts in the correct columns.

Setting Up the Basic Running Balance

Assume your headers are in row 1, and data starts in row 2. Put your starting balance in cell D2. Then in cell D3, enter:

=D2-B3+C3

This assumes B is Withdrawal and C is Deposit. Drag the formula down from D3 to fill the column. Each row will show the running balance after that transaction. For example, if D2 is 1000, B3 is 50, and C3 is 0, D3 returns 950. If C4 is 200 and B4 is 0, D4 returns 1150.

For a cleaner look, use the IF function to hide the balance until a transaction is entered. In D3, use:

=IF(AND(B3="",C3=""),"",D2-B3+C3)

This leaves D3 blank if both B3 and C3 are empty. This prevents long strings of repeated balances and makes the register easier to read.

Using SUM for a Quick Total

If you prefer a simple total at the bottom instead of a running balance, put all deposits in one column and all withdrawals in another. At the bottom, use:

=SUM(C2:C100)-SUM(B2:B100)

Add your starting balance to that result. For example, if you start with 500, total deposits are 1200, and total withdrawals are 300, the formula returns 900. Then add the starting balance: =500+SUM(C2:C100)-SUM(B2:B100) gives 1400.

This method is faster to set up but doesn’t show a balance after each transaction. It’s fine if you only need a monthly total.

Handling Outstanding Checks and Deposits

A common mistake is thinking your bank balance equals your checkbook balance. It doesn’t, because of checks you’ve written that haven’t been cashed yet, or deposits that haven’t cleared. To account for this, add a column called “Cleared” (E) with a Y or N. Then use SUMIF to total only cleared transactions.

Assuming E2:E100 has Y or N, use:

=SUMIF(E2:E100,"Y",C2:C100)-SUMIF(E2:E100,"Y",B2:B100)

This gives your cleared balance, which is what the bank should show after processing. Compare that number to your bank statement. If they differ, look for missing transactions or bank fees. This is the essence of reconciliation.

Formatting and Data Validation Tips

To keep your register accurate, format the amount columns as currency. Select B2:C100, then press Ctrl+Shift+4 (Windows) or Cmd+Shift+4 (Mac) to apply currency format. You can also go to Home > Number Format > Currency.

Add data validation to the Cleared column to avoid typos. Select E2:E100, go to Data > Data Validation, choose List, and enter Y,N as the source. This creates a dropdown so you only enter Y or N.

For dates, use the DATE function or type them as mm/dd/yyyy. Excel stores dates as numbers, so you can sort by date later. To sort, select all data, go to Data > Sort, and choose the Date column.

Common Problems and How to Fix Them

One frequent issue is the running balance formula showing a negative number when it shouldn’t. That’s usually because a withdrawal was entered in the deposit column or vice versa. Double-check the column headers.

Another problem is the balance not updating when you add a new row. If you drag the formula down only to row 100, but you add row 101, the formula won’t be there. Either drag the formula down further in advance, or convert your data range to an Excel Table (Ctrl+T). Tables automatically extend formulas to new rows.

If you see #VALUE!, it usually means a cell has text instead of a number. Check for spaces or apostrophes in amount cells. Use the CLEAN function to remove non-printable characters, but it’s simpler to re-enter the amount.

Comparing Excel to Paper or Apps

Paper registers are simple but require manual math and are easy to lose. Mobile apps like Mint or YNAB sync with banks automatically, but they may not let you record a check before it clears. Excel sits in between: you control everything, no internet needed, and you can customize categories and reports. The downside is you must enter transactions manually, which some people find tedious.

If you’re comfortable with formulas, Excel is free (if you already have Office) and fully flexible. If you want automatic syncing, an app might be better. But for learning and control, Excel is hard to beat.

Frequently Asked Questions

How do I get the balance to show only after I enter a transaction? Use the IF formula shown earlier: =IF(AND(B3="",C3=""),"",D2-B3+C3). This leaves the cell blank until you type an amount.

Can I use Excel on my phone to track my checkbook? Yes, the Excel mobile app works, but the formulas are the same. The layout may be cramped, but you can enter transactions and the running balance will update. Just be careful with the small touch targets.

What if I have multiple accounts? Use separate sheets or separate columns. For separate sheets, copy the same structure to each sheet. For separate columns, you’d need more complex formulas, so separate sheets are easier.

How do I reconcile with my bank statement? Compare your cleared balance (using SUMIF) to the bank’s ending balance. If they match, you’re done. If not, check for bank fees, interest, or transactions you missed. Mark each transaction as cleared as you verify it.

Is there a built-in checkbook template in Excel? Yes, search for “checkbook register” in File > New. It includes a running balance formula and a reconciliation section. You can modify it to suit your needs.

Can I protect the formulas so I don’t accidentally delete them? Yes, select the cells with formulas, go to Format Cells > Protection, check Locked, then protect the sheet via Review > Protect Sheet. This prevents accidental edits.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.