However you converted your statement, a row can go missing at a page break, two rows can merge, or a digit can be misread. You don't need to compare the sheet with the PDF line by line. A bank statement already contains its own check: the balance column.
The idea
For every transaction:
previous balance + credit − debit = balance on this row
If one row is wrong or missing, this fails on that row, so the formula points straight at the problem.
1. Lay out the sheet
The formulas below assume this layout. Adjust the column letters if yours differs.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Description | Debit | Credit | Balance | Check |
| 2 | Opening balance | 10,000.00 | ||||
| 3 | 01/04 | Salary | 50,000.00 | 60,000.00 | OK | |
| 4 | 02/04 | Rent | 20,000.00 | 40,000.00 | OK |
Row 2 holds the opening balance printed on the statement. Transactions start on row 3, oldest first. Debit and credit cells with no amount can stay empty, because Excel treats empty cells as zero in arithmetic.
2. Make sure the amounts are numbers
Converted amounts are often stored as text, which makes formulas return #VALUE! or wrong sums. Select
columns C to E and use Data → Text to Columns → Finish. Numbers should then be right-aligned.
If balances carry a Dr or Cr suffix (for example 1,250.00 Cr), convert them in a helper
column. With the original text in E3, this formula (Excel 2021, Microsoft 365 or Google Sheets) gives a plain
number, negative for Dr:
=LET(t, UPPER(TRIM(E3)),
n, VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(t, "DR", ""), "CR", ""), ",", "")),
IF(RIGHT(t, 2) = "DR", -n, n))Copy the helper column and use Paste Special → Values over the balance column, then delete the helper.
3. Add the row check
In F3, enter:
=IF(ROUND(E2 + D3 - C3 - E3, 2) = 0, "OK", "CHECK")
Fill it down to the last transaction. ROUND(…, 2) matters: without it, tiny floating-point leftovers
such as 0.0000000001 would mark correct rows as wrong.
To count the problems:
=COUNTIF(F:F, "CHECK")If your statement lists the newest transaction first
Some statements are in reverse date order. Then the previous balance is on the row below, and the opening balance belongs after the last transaction. Either sort the sheet oldest-first, or use this in F3 and fill down:
=IF(ROUND(E4 + D3 - C3 - E3, 2) = 0, "OK", "CHECK")To flip the order, first number the rows 1, 2, 3… in a spare column, then sort by that column from largest to smallest. Sorting by date alone can shuffle transactions that share a date.
4. Check the totals
Put the closing balance printed on the statement in a spare cell, say H1. If the transactions run from row 3 to row 500:
=IF(ROUND(E2 + SUM(D3:D500) - SUM(C3:C500) - H1, 2) = 0, "Totals match", "Totals differ")
If your statement prints total debits and total credits, compare SUM(C3:C500) and SUM(D3:D500)
against them too. A matching total with a failing row usually means two errors cancelled out, so check
both.
5. Read the results
- One CHECK, then OK again: that row's amount or balance was misread. Compare it with the PDF.
- CHECK on every row from some point onwards: a row is missing just above the first failure, often at a page break.
- CHECK where the gap is exactly twice the row's amount: the amount is in the wrong column (a debit recorded as a credit, or the reverse).
- Everything fails: the debit and credit columns may be swapped for the whole sheet, or the statement is in the opposite order to what the formula assumes.
Or let the converter do it
StatementSieve runs this check on every row while it converts, marks rows that don't add up as MISMATCH, and adds the result as a Check column and a Summary sheet in the export. It runs in your browser, so the statement isn't uploaded. Bank support is still experimental, so the check is there to be relied on, not skipped.