Check Register Formula in Excel for a Running Checkbook Balance

Get the running balance right once and the register takes care of itself. Convert your statement PDF into rows with the bank balance kept, then test your formula against it line by line. One-time 15 page demo, no credit card.

One-time 15 page demo, no credit card
Bank running balance kept for checking
Excel, CSV, and JSON output

PDF, JPG, PNG, BMP, HEIC, TIFF, MT940, BAI2, NACHA, camt.053, CSV

Upload your bank statement

Extract:

The checkbook formula, in short

With payments in column E, deposits in column F, and the balance in column H, the first balance cell is =F2-E2 and every cell below is =H2+F3-E3, filled down. That is the whole checkbook formula. If you ever sort the list, use =SUM($F$2:F3)-SUM($E$2:E3) instead, because it sums from the top rather than trusting the row above.

Why Checkbook Formulas Go Wrong

The arithmetic is simple. The errors come from how the formula is written, filled, and fed with data.

Blank Rows Break the Chain

A blank row in the middle of the register makes the next balance treat the empty cell as zero, or shows a column of the same number all the way down.

Text That Looks Like a Number

Amounts pasted from a PDF often arrive as text with a dollar sign or a trailing space. Excel skips them in sums, so the balance is quietly off.

Sorting Scrambles the Balance

A formula that points at the row above recalculates against the wrong neighbor once the rows are sorted by payee or amount.

Signs Mixed in One Column

Keeping payments and deposits in one amount column works only if every payment is negative. One positive payment adds instead of subtracts.

Fill Down Stops Short

New rows typed below a plain range do not get the formula, so the last few balances are blank or typed by hand.

No Way to Spot the First Bad Row

Once the final balance disagrees with the bank, you cannot tell which of three hundred rows started the drift without a comparison column.

Check Every Row Against the Bank Balance

Convert the statement and you get the balance the bank calculated for every transaction. Put it next to your formula and the first row where they part is the row with the error.

Bank Balance per Row

The running balance printed on the statement comes through as its own column, ready to compare with your formula.

Real Numbers, Not Text

Amounts arrive as numbers with the sign in the right column, so SUM counts every one of them.

Separate Payment and Deposit

Debits and credits stay in separate columns, matching the two column register layout the formulas expect.

Check Numbers Kept

The check number column lets you match cleared checks by number instead of by amount.

Many Statements at Once

Convert a run of months in one batch and test the formula across the full period.

Excel, CSV, or JSON

XLSX for the register, CSV for Google Sheets, JSON if the register lives in your own tool.

How to Add a Running Balance to a Check Register in 4 Steps

From an empty column to a balance you can trust.

1

Lay Out Payment and Deposit

Put payments in column E and deposits in column F, both as positive numbers, with the opening balance as the first deposit in row 2.

2

Write the First Balance

In H2 enter =F2-E2. This is the opening balance carried into the register.

3

Fill the Running Formula

In H3 enter =H2+F3-E3 and fill it down, or convert the range to a table so new rows get it on their own.

4

Test Against the Statement

Paste the converted statement balance into column I and use =ROUND(H3-I3,2) to find the first row that disagrees.

Who Needs a Reliable Register Formula

Anyone whose spreadsheet balance has to match the bank, not just look plausible.

Small Business Owners

Running an operating account in Excel and deciding what to pay based on the register balance.

Bookkeepers

Auditing a client spreadsheet that stopped agreeing with the bank months ago.

Association Treasurers

Handing a register to the next treasurer with formulas that still work after they add rows.

Accountants

Rebuilding a register from statements and proving it to the cent before the close.

Transaction Types We Handle

Opening balance
Checks
Deposits
Card payments
ACH debits
Bank fees
Interest
Voided checks

The basic running balance formula

Assume the register has Date in A, Number in B, Payee in C, Category in D, Payment in E, Deposit in F, Cleared in G, and Balance in H, with headers in row 1 and the opening balance as a deposit in row 2.

CellFormulaWhat it does
H2=F2-E2Starts the balance from the opening deposit
H3 and down=H2+F3-E3Previous balance plus money in minus money out
H3 and down, blank safe=IF(AND(E3="",F3=""),"",H2+F3-E3)Leaves future rows empty until you type an amount
H3 and down, sort safe=SUM($F$2:F3)-SUM($E$2:E3)Sums from the top, so it never depends on a neighbor cell

The blank safe version has one catch: if a row in the middle is left empty, the row below it refers to an empty text value and returns an error. Either keep rows filled or use the sort safe version, which handles gaps without complaint.

The same formula inside an Excel table

Turn the register into a table with Ctrl+T and name the columns. A structured version of the sort safe formula then reads like a sentence and fills itself into every new row:

=SUM(INDEX([Deposit],1):[@Deposit])-SUM(INDEX([Payment],1):[@Payment])

INDEX with 1 anchors the range at the first row of the column, and [@Deposit] ends it at the current row. Sort the table by date before you read the result, because a running balance only means something in date order.

One amount column instead of two

Some registers keep a single Amount column with payments as negative numbers. The running balance is then =SUM($E$2:E3). It is shorter, but one payment typed without the minus sign adds instead of subtracts. If you use this layout, add a check such as =COUNTIFS(D:D,"<>Income",E:E,">0") to count positive amounts outside income categories.

Cleared balance and outstanding items

The register balance tells you what you have. The cleared balance tells you what the bank shows. Put C in column G for every row the statement includes, then:

ResultFormula
Cleared balance=SUMIFS(F:F,G:G,"C")-SUMIFS(E:E,G:G,"C")
Outstanding checks and payments=SUMIFS(E:E,G:G,"<>C")
Deposits in transit=SUMIFS(F:F,G:G,"<>C")
Does it agree with the statement=IF(ROUND(ClearedBalance-StatementEnding,2)=0,"Agrees","Check rows")

The cleared balance should equal the ending balance on the statement. When it does not, the next section finds the row.

Find the first row that disagrees with the bank

Convert the statement PDF and paste the bank running balance into a spare column, say I, next to the cleared rows. Then add =ROUND(H3-I3,2) in column J. The first non zero value marks the row where the register and the bank split, usually a typo, a missed fee, or a payment entered as a deposit. Fix that row and every balance below it corrects itself. If a gap divides evenly by 9, look for two swapped digits.

Amounts that came in as text will not add up. =ISTEXT(E3) shows them, and multiplying the column by 1 or using Data, Text to Columns turns them back into numbers. Converted statements from BankXLSX arrive as numbers already, which removes this problem at the source.

Formula for Google Sheets

Google Sheets has no structured table references in a classic range, so use the sort safe range version, =SUM($F$2:F3)-SUM($E$2:E3), and fill it down. For a single formula that fills the whole column, =ARRAYFORMULA(IF(E2:E="",IF(F2:F="","",SUMIF(ROW(F2:F),"<="&ROW(F2:F),F2:F)-SUMIF(ROW(E2:E),"<="&ROW(E2:E),E2:E)),SUMIF(ROW(F2:F),"<="&ROW(F2:F),F2:F)-SUMIF(ROW(E2:E),"<="&ROW(E2:E),E2:E))) works but is slow on long registers, so most people keep the fill down version.

If you are setting up the register itself, start with the check register template in Excel. For the month end proof with both sides of the account, use the bank reconciliation template, and to get the bank balance column itself use the PDF bank statement to Excel converter.

Why a Bank Balance Column Beats Guessing

1
Formula for the whole register
0
Difference when the register ties
9
Divides the gap on a transposition

Security & Privacy

  • Your statements stay yours
  • Uploads are encrypted with 256-bit encryption and you can delete your files at any time.

Common Questions About the Check Register Formula

Previous balance plus deposit minus payment. With the balance in H, deposits in F, and payments in E, enter =F2-E2 in H2 and =H2+F3-E3 in H3, then fill down.

Use a formula that sums from the top of the column, such as =SUM($F$2:F3)-SUM($E$2:E3), and sort by date before reading the balance. A formula that points at the row above gives wrong results after any other sort.

Calculation is set to manual, or the amounts are stored as text. Press F9 or set Formulas, Calculation Options to Automatic, then check the amount cells with =ISTEXT and convert any text values to numbers.

Mark cleared rows with C and use =SUMIFS(F:F,G:G,"C")-SUMIFS(E:E,G:G,"C"). The result should equal the ending balance on your bank statement.

Wrap the formula in IF, for example =IF(AND(E3="",F3=""),"",H2+F3-E3), or keep the register as an Excel table so the formula only exists on rows you add.

Convert the statement into Excel, put the bank running balance next to your register balance, and subtract the two. The first row with a non zero difference is where the error starts.