Find the Row That Breaks Reconciliation

TL;DR: When a statement export does not reconcile, do not re-check everything. Add an Expected Balance column, then highlight the first row where the balance chain fails. Fix that one row, whether it has a wrong column, decimal/comma error, duplicate, wrapped line, or missing transaction. The rest usually falls back into place.

If you’ve ever struggled with a bank statement export that almost works, you know the feeling: you have a spreadsheet full of transactions, everything seems reasonable, and yet the balance doesn’t match. Now you’re staring at rows as if they owe you money.

Here’s the good news: you don’t have to check the entire sheet again.

In most reconciliation issues, there’s usually a first row where things go wrong. Locate that row, and you often find the solution. Fix it once, and everything that follows often falls back into place.

This post presents a practical method you can use in Excel or Google Sheets, especially when dealing with PDF-to-Excel exports.

First: understand what “broken” actually means

Most bank statements and good exports follow a simple balance equation:

• New Balance = Previous Balance + Money In - Money Out

That’s it.

So when the numbers don’t reconcile, it’s often one of these issues:

• A transaction amount is wrong (typically a single digit, decimal, or comma issue).
• A credit is recorded as a debit (or vice versa).
• A row is missing.
• A row is duplicated.
• A description is wrapped and treated as a separate transaction.
• A row has shifted sideways, so the amount appears in the wrong column.

Your task isn’t to check everything. Your task is to catch the first time the balance chain breaks.

If you want the “why” behind this (and why 99% accurate exports still waste hours), this explains it in plain terms: Why 99% Accuracy Isn’t Enough in Bank Statement Extraction.

Step 1: Don’t start in the middle. Start at the top

This is where people often waste hours.

When the ending balance is off by £5 or $20, the instinct is to scroll around looking for “something that seems wrong.” That turns into a guessing game.

Instead, start with the first transaction row after the opening balance and work downward because when the balance chain breaks, you’ve found your starting point.

If your export includes a Balance column, you can do this quickly with a helper column.

Step 2: Add an “Expected Balance” helper column (2 minutes)

In Excel or Sheets, create a new column called Expected Balance.

Let’s assume your columns are:

• Balance (the balance for each transaction row)
• Credit (money in)
• Debit (money out)

On the first transaction row, your expected balance formula should reference the previous row’s balance.

A simple version looks like:

• Expected Balance = Previous Balance + Credit - Debit

In spreadsheet terms, this means:

• Take the balance from the row above
• Add Credit (if any)
• Subtract Debit (if any)

Then compare Expected Balance to the Balance column in that same row.

Don’t overthink formatting. You’re not building a perfect model; you’re trying to find the break.

If you want the full Excel reconciliation workflow (beyond just finding the break row), this guide walks through it step-by-step: How to reconcile bank statements in Excel.

Step 3: Add a “Match?” column to identify where it breaks

Next to Expected Balance, add a simple check:

If Expected Balance equals Balance → “OK” If it doesn’t → “BREAK”

You’ll usually see a clear run of “OK OK OK OK…” followed by “BREAK.”

That first “BREAK” row is the one that matters.

Not the last one. Not the closing balance. The first one.

Because after the first break, every row after it can look broken even if those rows are correct, since the previous balance is already wrong.

Step 4: When you find the first BREAK row, check these five things (in order)

Most issues will fall into one of these categories. Checking them in this order saves time.

1. Is the amount in the wrong column?

This is a common problem: a debit ends up in the credit column, or vice versa.

A quick test: if the transaction is clearly a payment (like rent, vendor payment, or card payment), it should be in Debit, not Credit.

Flip it, and re-check the balance chain.

2. Is there a missing decimal or comma?

This is another frequent issue.

Examples:

• 100.00 becomes 100.80
• 1,200.50 becomes 1200.50 (fine) or 12,005.0 (not fine)
• 5.00 becomes 500 (problematic)

Look closely at the amount on the PDF for that row.

3. Did a wrapped description create a false extra row?

Sometimes the statement has a long description that wraps to the next line. Some converters mistakenly treat that wrapped line as a new transaction with blank amounts.

That creates an extra row that disrupts your chain.

If you see a row with:

• No date
• No amount
• Just a continuation of text

It’s not a transaction row. It’s a continuation line. Delete it or merge it into the previous description.

4. Is a row duplicated (often at page boundaries)?

A common place for duplication is the first or last row on a page.

Converters sometimes grab a transaction at the bottom of Page 2 and then again at the top of Page 3. If you remove the duplicate, the balance chain often snaps back into place right away.

If you’re seeing column drift, split rows, or phantom transactions often, this checklist helps you spot the root cause faster: Why your PDF to Excel conversion failed (and how to fix it).

5. Is a row missing entirely?

This issue is less common than people think, but it happens—especially with statements that have unusual spacing or low-quality scans.

If everything looks clean but the break difference is consistent (like exactly one transaction amount), compare the PDF rows around the break and see if a transaction line exists on the PDF that was left out of the sheet.

When you add it, your chain restores.

Step 5: Confirm you’re done with one final test

Once you fix the first break row, scroll down and see if the “BREAK” flags disappear.

If they do, you’ve solved the problem.

If you still see breaks later, repeat the same process: Find the next first break. Fix it. Move on.

This approach keeps the work contained. No panic-checking. No random scrolling.

A quick sanity check: the statement summary.

Even when the running balance chain is clean, you still want to make sure the statement details match.

Most statements show:

• Opening balance
• Closing balance
• Total deposits (credits)
• Total withdrawals (debits)

A simple top-level check:

Opening + Total Credits - Total Debits = Closing

If this doesn’t match, something is still missing or misread, even if individual rows seem fine.

Why this method is faster than just looking at it.

Because it turns reconciliation into a search problem, not a judgment problem.

You’re not asking: “Which row looks suspicious?”

You’re asking: “Which is the first row that breaks the balance chain?”

That’s straightforward, repeatable, and fast.

And it’s exactly how experienced bookkeepers prevent a small mismatch from turning into a two-hour headache.

If you need to ask a client for a better PDF (without making it awkward), you can copy-paste wording from here: What to tell a client when their bank statement export doesn’t reconcile.

The honest takeaway

If you find yourself doing this often, it’s not because you’re slow.

It’s because many “PDF to Excel” exports are designed for casual conversion, not professional bookkeeping. They focus on getting something into rows, not on providing you with a reconciled, audit-ready dataset.

A tool that highlights the first break row automatically and keeps the PDF visible beside the table would transform this from a tedious task into a quick fix. That’s exactly what SmartBankStatement is built for: it focuses on reconciliation checks and review, so you can pinpoint breakpoints faster and export a ledger you can stand behind.

Until then, use the method above. It’s the fastest way I know to get unstuck and move on with your actual work.

Next step

If you’re doing this regularly, it’s worth using a workflow that’s built around reconciliation (not just extraction). You can try SmartBankStatement on a statement that previously caused problems and judge it by one thing: does it reconcile cleanly?

Related reading

• Why 99% Accuracy Isn’t Enough in Bank Statement Extraction
• How to reconcile bank statements in Excel
• What to tell a client when their bank statement export doesn’t reconcile