How to check a bank statement reconciles in Excel (the one formula that matters)

Whenever transactions leave a PDF and land in a spreadsheet — by copy-paste, OCR, a converter or retyping — something can go wrong: a row dropped, a row doubled, a minus sign lost, 1,250.00 read as 1250 and .00 in the next cell. None of these are visible by eye in a 200-row sheet.

Fortunately every bank statement contains its own checksum: the running balance. Here's how to use it.

The check

Every statement prints an opening balance and a closing balance. If your sheet is right:

opening balance + total money in − total money out = closing balance

In Excel, with money in (credits) in column D and money out (debits) in column C:

=B1 + SUM(D:D) - SUM(C:C)

where B1 holds the opening balance. Compare with the closing balance. Equal to the cent? The sheet is complete and every sign is right. (Two errors can cancel out — rare, and the row-by-row check below catches it.)

Finding the row that breaks it

If the totals differ, the difference tells you what to look for:

• Difference equals one transaction's amount → that transaction is missing (or doubled if the sign is reversed).
• Difference equals twice an amount → that transaction has the wrong sign (a credit read as a debit or vice-versa).
• Difference is a round number like 1,000 or 100 → a thousands separator was lost (1,250.00 became 250.00) or a decimal point shifted.

To find the exact row, add a computed balance column and compare with the balance printed on the statement:

F2:  =B1 + D2 - C2          (first row: opening + credit − debit)
F3:  =F2 + D3 - C3          (then fill down)
G2:  =ROUND(F2 - E2, 2)     (E = balance column from the statement; fill down)

The first row where column G is not zero is the first wrong row. Everything above it is correct.

Statements listed newest-first

Some banks (several UK current accounts, most credit cards) print the most recent transaction at the top. The running balance then runs backwards. Either sort ascending by date before running the check, or start from the closing balance and subtract downwards. A converter that understands statement order — Rowvert does — handles both without you noticing.

Statements with a single signed amount column

Many statements don't have separate debit and credit columns; they show one Amount column with a minus sign, brackets, or "DR"/"CR" after the number. In Excel:

• Brackets: =IF(LEFT(A2,1)="(", -VALUE(MID(A2,2,LEN(A2)-2)), VALUE(A2))
• DR/CR suffix: =IF(RIGHT(A2,2)="DR", -VALUE(LEFT(A2,LEN(A2)-2)), VALUE(LEFT(A2,LEN(A2)-2)))

Then the check becomes opening + SUM(amount) = closing.

Spotting an edited statement

The same arithmetic catches most crude alterations. If someone has changed one amount in a PDF editor, the running balance printed on the following lines no longer follows — column G goes non-zero from that row onward and never returns to zero. Lenders, landlords and mortgage brokers use exactly this check; see Rowvert for mortgage brokers. It is not proof of fraud (a genuine bank error looks the same) but it tells you where to look.

Doing it automatically

Rowvert runs this check on every file: it extracts the opening and closing balances, recomputes the running balance line by line, and marks any row that doesn't fit as Verify. You can remove a flagged row before downloading, and the Excel file's Summary sheet records whether the statement reconciled. That's the difference between "the PDF converted" and "the numbers are right".

Related: How to convert a bank statement PDF to Excel · Scanned bank statements and OCR.