Guides

How to Reconcile a Bank Statement in Excel After Converting PDF to CSV

A converted statement is not a reconciled statement. The CSV gives you the bank's lines in a spreadsheet — it does not tell you whether those lines agree with your books. That second job is the reconciliation, and it starts the moment the conversion passes its own checks. Convert the PDF with the bank statement converter, prove the file balances, and only then open Excel. This guide covers the second half: matching every line, explaining every difference, and ending with two balances that agree.

Reconciling starts after the conversion, not before

Do not reconcile from a file you have not checked. If the conversion dropped a row or flipped a sign, the reconciliation will chase a difference that was never in your books. Run the conversion checks first — our guide on how to check a bank statement conversion takes five minutes — and reconcile only a file that balances on its own.

You also need your own side of the story: the cash book or ledger for the same account and the same dates, exported to Excel. A reconciliation compares two records. One side alone proves nothing.

  • The statement CSV for one account, one period, balance-checked.
  • Your cash book or ledger export for the same dates.
  • The closing balance of the last reconciliation — this month starts where last month ended.
  • Any bank notices about fees, interest or returned items you have not recorded yet.

Step 1: Clean the converted CSV before Excel sees it

A converted CSV is rarely reconciliation-ready. It carries the statement's layout with it: running balances, summary rows, and dates and amounts in the bank's own shapes. Clean those out before you match anything, or every formula below will trip over them.

  • Put all dates in one format, for example 2026-03-14, and make Excel store them as real dates, not text.
  • Make money in and money out one signed Amount column: deposits positive, withdrawals negative. Two unsigned columns will break the matching.
  • Delete the running Balance column, the account number, and any opening, closing or summary rows. They are not transactions.
  • Remove currency symbols, commas inside numbers, and blank rows.
  • Keep one transaction per row, even when the PDF wrapped its description over two lines.
If a description wrapped across two lines in the PDF, the conversion may have split it into two rows. Join them before you reconcile, or one transaction will look like two — and the second half will never match anything.

Step 2: Set up the reconciliation sheet

Use one workbook with three sheets: the statement, the cash book, and the reconciliation itself. Keep the two source sheets untouched — paste the data in and never edit it there. All the working happens on the third sheet.

SideWhat goes in itWhere it comes from
Bank sideOpening balance per the bank, every statement line, closing balance per the bankThe converted CSV
Book sideOpening balance per your records, every ledger line, closing balance per your booksThe cash book export
DifferenceBank closing balance minus book closing balanceA formula — this is the number the rest of the job explains
The reconciliation sheet: two sides, one difference.

The difference is the whole job in one number. If it is zero, the two sides already agree and you are done. Usually it is not zero — and the next two steps explain it away, item by item, until nothing is left unexplained.

Step 3: Match every line to your books

Work through the two lists chronologically, oldest first, and tick off every transaction that appears on both sides with the same date and the same amount. Do not skip ahead: reconciliation is sequential, and a match found out of order hides the gap it was covering.

  1. Sort both lists by date, then by amount.
  2. Match exact pairs first: same date, same amount, same direction.
  3. For the rest, match on amount alone, then check the date is within a few days — a cheque written on the 28th may clear on the 2nd.
  4. Mark each matched pair so it cannot be matched twice.
  5. Leave everything unmatched in view. Those rows are the next step, not a failure.

Formulas speed this up on long statements. XLOOKUP or VLOOKUP on amount finds the partner of a line in the other list, and SUMIFS adds up everything already matched so the unmatched remainder stays visible. But a formula only proposes matches — you confirm them. Two different payments for the same amount are not the same payment.

Step 4: Explain the difference with reconciling items

The unmatched rows are not errors. Most of them are timing: money the bank has recorded and you have not, or the reverse. List each one on its own side, and adjust the balances until the two agree.

ItemAppears onWhat to do
Deposits in transitYour books only — the bank has not credited them yetAdd to the bank balance
Outstanding chequesYour books only — the bank has not cleared them yetSubtract from the bank balance
Bank fees and chargesThe bank only — you have not recorded them yetSubtract from the book balance, then record them
Interest earnedThe bank onlyAdd to the book balance, then record it
Direct debits and standing ordersThe bank only, if you missed themSubtract from the book balance, then record them
Errors — a mistyped amount, a duplicated lineEither sideCorrect the side that is wrong; never plug the difference
The usual reconciling items, and which side each one belongs to.

Add the items to the side that is missing them, and the two adjusted balances should meet. If they do, the period is reconciled. Write the date and the adjusted balances down, and keep the sheet: next month starts from this closing balance.

Never force the two sides together with a plug figure in a suspense account. A plug hides the missing item, and the same difference will return next month, larger and harder to find.

Step 5: When the balances still do not agree

A difference that survives the reconciling items means something is wrong in the data, not in the arithmetic. Work back in this order, and check one suspect at a time.

CheckWhat to look forWhy it is first
Opening balanceThis month's opening balance against last month's reconciled closing balanceA wrong start poisons everything after it
The conversionA row the PDF had and the CSV lost, a sign flipped, a summary row counted as a transactionConversion errors outnumber bookkeeping errors
Transposed digitsA difference divisible by 9 — 540 instead of 450, for exampleA classic typing slip, quick to test
A duplicated lineThe same date and amount twice in one listEasy to miss, easy to prove
An unrecorded bank itemA fee, charge or correction on the statement with no book entryThe bank's side is usually right about its own charges
Finding a stubborn difference, in the order that wastes the least time.

If the totals still differ after all five, go back to the conversion itself. Our guide on why a converted bank statement does not balance works through the same hunt from the conversion side, and the two guides meet in the middle.

Traps the conversion itself leaves behind

Some differences are born in the PDF-to-CSV step, not in your books. Knowing the four usual traps keeps you from correcting the wrong side.

  • Sign flips: a withdrawal converted as a deposit, or the reverse. The row count is right and every total is wrong.
  • Split rows: a wrapped description became two rows, so one transaction looks like two and the second half never matches.
  • Opening balance as a transaction: the statement's opening balance converted as a line of its own. It inflates the month by the whole balance.
  • Duplicate pages: a statement page scanned twice converts the same lines twice. The totals double, and the reconciliation chases a ghost.

Each trap leaves a fingerprint: a sign flip breaks the line sum, a split row breaks the row count, an opening-balance row breaks the first week's matches. Run the conversion checks again before you touch the books.

A monthly habit that keeps the books honest

Reconcile every month, in the same week the statement arrives. A month's statement takes under an hour once the habit exists; a year's worth takes a weekend and a fight. Each month's closing balance becomes next month's opening balance, so an unreconciled month poisons every month after it.

Keep the routine in one place: convert the PDF with the bank statement converter, check the file, reconcile it in Excel, and file the statement, the CSV and the reconciliation together. When the accountant asks, the answer is a folder, not a memory.

Sources and further reading

Put it into practice

Convert a statement now and watch the balance check run on your own file. The first 10 pages each month are free, and exports are never cut short.

Convert a statement See plans

More guides

How to Convert a Bank Statement PDF to CSV · How to Convert a Bank Statement PDF to Excel · How to Import Bank Statements into QuickBooks (QBO Files) · OFX vs. QBO vs. QIF: Which Format Should You Download? · Why a Converted Bank Statement Does Not Balance — and How to Fix It · How to Check a Converted Bank Statement Is Correct · Scanned vs. Digital Bank Statements: Why One Converts and the Other Does Not · How to Convert a Password-Protected Bank Statement PDF · How to Convert Many Bank Statements at Once (and Merge Them Cleanly) · Is It Safe to Convert Bank Statements Online? A Privacy Checklist · How to Import Bank Statements into Xero