# How to Build a Checkbook Register in Excel (Running Balance Formula Explained)

A checkbook register is just five columns and one formula. Here's exactly how to structure it so the running balance never breaks.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/how-to-build-a-checkbook-register-in-excel
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 9 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

Most people who stop balancing a checkbook don't stop because it's hard — they stop because a paper register is slow to add up and a banking app only shows what's already cleared, not what you've actually committed to spend. A checkbook register in Excel solves both problems with one formula, and it takes about ten minutes to set up correctly the first time.

The word 'formula' undersells it a little. There's really only one calculation doing all the work — a running balance that carries forward after every row — and almost everything that makes a register frustrating to maintain comes down to that one formula being interrupted somehow. Get the structure right once, and it stops being something you have to think about.

## The Columns Every Checkbook Register Needs

A working register needs surprisingly few columns. Everything beyond this core set (check numbers, categories, cleared status) is useful, but this is the minimum that makes the running balance formula work correctly.

**Minimum viable checkbook register layout**
| Column | Purpose |
| --- | --- |
| Date | The date the transaction happened — must be a real date value, not typed text. |
| Description | What the transaction was, in plain language you'll recognize a year from now. |
| Debit | Money leaving the account. Leave blank if this row is a credit. |
| Credit | Money entering the account. Leave blank if this row is a debit. |
| Running Balance | Previous balance, plus this row's credit, minus this row's debit — the one formula. |

## The Running Balance Formula

**Running balance (per row)**

```
Running Balance = Previous Row's Running Balance + This Row's Credit − This Row's Debit
```
- In Excel, if row 2 holds your opening balance and row 3 is your first transaction, row 3's formula is =E2+D3-C3 (adjust column letters to your own layout), then copied down for every following row.
- Only ever type into the Debit or Credit cell for a given row — never both. The formula assumes one or the other is blank.
- The very first row (your starting balance) is a typed number, not a formula — every row after it references the row above.

That single formula, copied down the column, is the entire engine of a checkbook register. Everything else in a more built-out workbook — monthly summaries, dashboards, reconciliation — is just a different view of the same running-balance column.

**Five rows, starting from a $500.00 balance**
| Date | Description | Debit | Credit | Running Balance |
| --- | --- | --- | --- | --- |
| — | Starting balance |  |  | $500.00 |
| May 1 | Paycheck deposit |  | $1,200.00 | $1,700.00 |
| May 3 | Rent | $850.00 |  | $850.00 |
| May 5 | Groceries | $96.40 |  | $753.60 |
| May 8 | Refund |  | $22.00 | $775.60 |

_[Figure: Running Balance After Each Transaction — The same five rows from the table above, plotted in order — one line, one formula, no separate math for deposits versus withdrawals.]_

## Debit or Credit — Never Both in the Same Row

A real bank transaction is either money leaving or money entering — never a mix of the two in a single line. Your spreadsheet should mirror that exactly: one amount column for outflow, one for inflow, and the running balance formula subtracts one and adds the other. The moment a row has a number in both columns, the running balance for every row underneath it is wrong, and it usually takes a while to notice, because the error doesn't look dramatic — the balance is just quietly off by a fixed amount from that point forward.

## Why Running Balance Formulas Break (and How to Stop It)

- Typing a number over the formula cell — the single most common failure. If a balance suddenly stops updating after new rows, check whether the formula is still there or got overwritten with a static number.
- Deleting a row instead of clearing its contents — removing a row from the middle of a formula range can shift or break references in the rows below it, depending on how the formula was written.
- Inserting a new row outside the existing table range — a row added below the last formula row won't inherit the formula automatically; insert inside the range so it carries down.
- Typing dates as plain text — a date like "5/1" typed as text instead of a real date will still display correctly, but any month or year-based summary built on top of it will silently exclude that row.
- Entering both a debit and a credit on the same row — covered above, but worth repeating: it is the most common source of a running balance that's wrong by a fixed, hard-to-spot amount.

_[Figure: A Paper Register vs. a Formula-Driven Spreadsheet — The columns are identical — the difference is entirely in who does the arithmetic.]_

## Add a Cleared Column Before You Need It

The five core columns are enough to track a running balance, but add one more early: a Cleared column (Yes/No, or Yes/No/Review) marking whether each transaction has actually posted at your bank. It costs nothing to maintain if you update it as items clear, and it turns your register into something you can reconcile against a bank statement instead of just a private log that happens to look similar to one. See [How to Reconcile a Bank Statement in Excel, Step by Step](/blog/templates/how-to-reconcile-a-bank-statement-in-excel) for exactly how that column gets used once you're ready to reconcile.

## Categorize as You Go, Not Later

A Category column (Groceries, Housing, Utilities, and so on) turns a register into something you can actually analyze, but only if the category names are typed consistently. "Groceries" on one row and "Grocery" on another will not group together in a summary or chart — they'll read as two separate categories. Setting up a fixed dropdown list of category names (Excel's Data Validation feature) and choosing from it instead of typing freehand is the difference between a category breakdown that's useful and one that's quietly fragmented across near-duplicate labels.

## From a Single Formula to a Full Reconciliation System

Once the running balance, the Cleared column, and consistent categories are in place, you already have the core of a full checking-account system — the same structure a more built-out workbook uses, just without the KPI rows, charts, and guided reconciliation checklist layered on top. Building that layer yourself is possible; it's also exactly what a pre-built template exists to save you from redoing every time you set up a new account.

If you'd rather start from a version that already has the running balance, cleared-status tracking, a guided monthly reconciliation, and a spending dashboard built in, the [Free Checkbook Register Excel Template](/excel-templates/checkbook-register-template) uses this exact same five-column structure underneath — it just adds the reporting layer on top so you don't have to build it from scratch.

## Frequently Asked Questions

**Do I need a check number column if I mostly use a debit card?**

No — a check number column is only useful for transactions actually made by paper check. If you rarely write checks, leave it out or leave it blank on card and transfer rows; it doesn't affect the running balance formula either way.

**Should the running balance formula use absolute or relative cell references?**

Relative references (like =E2+D3-C3, without dollar signs) are correct here, because you want each row to reference the row directly above it as you copy the formula down — an absolute reference would lock every row to the same starting cell instead of chaining correctly.

**Can I use this same structure in Google Sheets instead of Excel?**

Yes — the running balance formula is standard spreadsheet arithmetic and works identically in Google Sheets. Data validation dropdown lists exist in both, though the exact menu location differs between the two programs.

**What if I need to go back and insert a transaction I forgot, out of date order?**

Insert a new row in the correct date position (inside the existing formula range, not appended at the very bottom) and copy the running balance formula into it from the row above. Every row below it will recalculate automatically once the formula is in place.

**How is this different from just checking my balance in a banking app?**

A banking app shows what's cleared at the bank right now. A register shows what you've actually committed to spend, including checks and transfers the bank hasn't processed yet — which is usually the number that matters when deciding whether you can safely spend more.

## Related Reading

[How to Reconcile a Bank Statement in Excel, Step by Step](/blog/templates/how-to-reconcile-a-bank-statement-in-excel) — The next step once your register and running balance are working: comparing them against your actual bank statement each month.

[Free Checkbook Register Excel Template](/excel-templates/checkbook-register-template) — The same structure covered here, pre-built with cleared-status tracking, a guided reconciliation sheet, and a spending dashboard; download it free.

Once your register shows a stable month-end surplus, the [Emergency Fund Calculator](/calculators/emergency-fund-calculator) is a reasonable next step before committing that surplus elsewhere.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/how-to-build-a-checkbook-register-in-excel). Quote freely with attribution and a link to this page._