The core formula for a checkbook register in Excel is a running balance formula: =SUM($D$2:D2) entered in the Balance column, assuming deposits are in column D and the first data row is row 2. This single formula updates automatically as you add rows, and you copy it down the column to keep a perpetual running total.
Why a Running Balance Formula Works
A checkbook register tracks every transaction—checks, debit card purchases, deposits, and fees—and shows the current account balance after each one. Instead of typing a new balance by hand (which invites arithmetic errors), the SUM formula with a mixed reference locks the start point while letting the end point move. In =SUM($D$2:D2), the dollar signs fix the row and column for the first cell, so when you drag the formula down to row 3, it becomes =SUM($D$2:D3), automatically including the new transaction. This is the same pattern used in bank statements and personal finance templates.
Setting Up the Register Layout
Before entering any formulas, create these columns in row 1:
- A: Date – transaction date
- B: Description – payee or memo
- C: Check # – optional, for checks only
- D: Deposit (Credit) – money coming in
- E: Payment (Debit) – money going out
- F: Balance – running total
Enter your starting balance in F2 (the first row). For example, if you open the account with $1,000, put 1000 in F2. Then in F3, enter =F2+D3-E3—this adds any deposit in D3 and subtracts any payment in E3 from the previous balance. Copy F3 down to all rows you might use. This two-column approach (separate deposit and payment columns) is clearer than a single amount column because it lets you sum deposits and payments independently later.
The Two Main Formula Approaches
There are two common ways to structure the balance column, and each has its own use case:
- Previous balance method:
=F2+D3-E3in F3. This is simple and intuitive—each row references the one above it. It works fine for a static register but breaks if you insert or delete rows in the middle. - Running SUM method:
=SUM($D$2:D3)-SUM($E$2:E3)in F3 (assuming D2 and E2 hold the starting balance as a deposit or payment). This recalculates the entire total from the top, so it survives row insertions and deletions. Use this when you plan to add transactions frequently.
For the running SUM method, put your opening balance in D2 as a deposit (e.g., 1000) and leave E2 blank. Then in F2, enter =SUM($D$2:D2)-SUM($E$2:E2), which returns 1000. Copy F2 down. Every new row you add with a deposit or payment automatically updates all balances below it.
Formatting Currency and Avoiding Errors
Select columns D, E, and F, then press Ctrl+Shift+$ (Windows) or Cmd+Shift+$ (Mac) to apply currency formatting with two decimal places. This prevents rounding confusion when you enter amounts like 12.5 instead of 12.50. For negative balances, Excel will show them in red if you use the default accounting format, which is a helpful visual cue. To catch data entry mistakes, add a simple check row at the bottom: =SUM(D2:D100) for total deposits and =SUM(E2:E100) for total payments. The final balance should equal starting balance plus deposits minus payments—if it doesn’t, you have a typo or a missing transaction.
Common Problems and Fixes
Three issues trip up most people:
- Formula not copying: If you drag the fill handle and the balance stays the same, check that the formula references the correct cells. In the running SUM version, the first cell must be absolute (
$D$2), not relative (D2), or the range shifts incorrectly. - Blank rows showing zeros: If you copy formulas down 100 rows but only use 20, rows 21-100 will show the last balance repeated or zero. To hide them, wrap the formula in an IF:
=IF(AND(D3="",E3=""),"",F2+D3-E3). This leaves the cell blank until you enter a transaction. - Inserting a row mid-register: With the previous balance method, inserting a row breaks the chain because the new row’s formula references a blank cell. Switch to the running SUM method, or after inserting, copy the formula from the row above into the new row.
Advanced Features: Categories and Conditional Formatting
Once the basic register works, you can add a category column (G) and use SUMIF to track spending by category. For example, with categories in G2:G50 and payments in E2:E50, enter =SUMIF(G2:G50,"Groceries",E2:E50) to see total grocery spending. For visual alerts, use Home > Conditional Formatting > Highlight Cells Rules > Less Than, and set the rule to format cells in the Balance column that are less than 0—this flags overdrafts immediately. You can also apply Data Validation (Data > Data Validation) to the Date column to restrict entries to valid dates, preventing accidental text entries.
Comparing Excel Register vs. Bank Statements
A common misconception is that the Excel register should match your bank’s online balance exactly. It won’t, because banks include pending transactions and may not yet show checks you’ve written. The register is your own record; reconciliation is the process of comparing the two and noting differences. To reconcile, add a “Cleared” column (H) and mark each transaction with an “x” when it appears on your statement. Then use =SUMIF(H:H,"x",D:D) for cleared deposits and =SUMIF(H:H,"x",E:E) for cleared payments. The cleared balance should equal the bank’s statement ending balance. This is the same method accountants use, and it catches errors like double-entered amounts or forgotten fees.
Frequently Asked Questions
How do I start a checkbook register in Excel from scratch?
Open a blank workbook, label columns A through H as described above, enter your starting balance in F2 (or D2 for the SUM method), and then enter the formula in F3. Save the file with a name like “CheckbookRegister.xlsx”. You don’t need a template; the formulas are simple enough to build in under a minute.
Can I use this formula in Google Sheets?
Yes, the same formulas work in Google Sheets with identical syntax. The only difference is formatting: use Format > Number > Currency instead of Ctrl+Shift+$. The running SUM method is especially useful in Sheets because it handles inserted rows well, just like in Excel.
Why does my balance show #VALUE! error?
This usually means a cell in the Deposit or Payment column contains text instead of a number. Check for stray spaces, commas, or dollar signs typed manually. Remove them, or format the columns as numbers before entering data. Also ensure you haven’t accidentally included a header row in the SUM range.
How do I add a new transaction without breaking the formulas?
If you used the running SUM method, simply type the date, description, and amount in the next blank row. The balance column already has formulas copied down, so it updates automatically. If you used the previous balance method, you must copy the formula from the row above into the new row—or better, switch to the SUM method to avoid this step.
Is there a way to protect the formulas from accidental changes?
Yes. Select the balance column (F), then go to Review > Protect Sheet. Before protecting, unlock the data entry cells (A, B, C, D, E) by selecting them, right-click > Format Cells > Protection, and uncheck “Locked”. Then protect the sheet with a password. This prevents anyone from overwriting the formulas while still allowing new transactions.
Final Setup Example
Assume you open with $500. In D2 enter 500, leave E2 blank, and in F2 enter =SUM($D$2:D2)-SUM($E$2:E2). Then in row 3, you record a $35 check to the electric company: put the date in A3, description in B3, check number in C3, leave D3 blank, enter 35 in E3. Copy F2 down to F3, and it shows =SUM($D$2:D3)-SUM($E$2:E3), which equals 500 – 35 = 465. Add a $200 deposit in D4, and F4 becomes 665. This pattern scales to hundreds of rows without any manual recalculation.
Related
- Excel Calendar Template with Notes? How to Make One
- Excel Calendar Template for Content Planning
- Excel Calendar Template for Events: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Compare Records in Two Excel Sheets with FormulasTo compare records in two Excel sheets, match each row by a shared, unique ID rather than by…
- Function x in Math: Meaning, Notation, and ExamplesA function is a rule that assigns exactly one output to each allowed input. In the notation f(x),…
- Excel Formula Checklist v6 AK16: Full GuideThe Excel formula checklist v6 AK16 refers to a specific version (v6) of a community-maintained formula auditing checklist,…
- Excel Find and Remove Text: Replace, Formulas, and TipsTo find and remove text in Excel, press Ctrl+H, enter the text in Find what, leave Replace with…
- Excel Table Filter Button Greyed Out? Fixes That WorkIf Excel’s filter button is greyed out, first press Esc to leave cell-edit mode, ungroup any selected worksheets,…
- Microsoft Excel Filter Duplicates: Find or Remove ThemExcel does not have a dedicated “filter duplicates” command in a column’s filter menu. To show repeated records,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.