Guides

How to reconcile a bank statement in Excel

A bank reconciliation answers one question: does your record of the account agree with the bank's? If it doesn't, you want to know exactly why. Excel is well suited to this, provided the bank data in the sheet is complete and correct to begin with.

This guide works in two stages. First, verify the statement data itself. Second, match it against your ledger or cash book and explain the difference.

Stage 1: verify the statement data

If you converted a PDF statement into Excel, check the conversion before reconciling anything. A dropped row or a flipped sign will otherwise look like a reconciliation difference, and you'll waste time searching your ledger for a problem that's in the import.

Set up the statement data like this:

Fill the formulas in F and G down to the last row.

Read the Difference column:

• All zeros: the data is complete and the signs are right.
• A non-zero value that appears at one row and carries forward: a transaction is missing or wrong at that row.
• A difference exactly double a transaction amount: that transaction's sign is reversed.
• A difference that appears and disappears a row later: two lines are out of order, or a single transaction was split across two rows.

Highlight problems automatically with conditional formatting. Select column G, choose Home → Conditional Formatting → New Rule → Use a formula, and enter:

=ABS(G3)>0.005

with a fill colour. Using a tolerance rather than <>0 avoids false alarms from floating-point rounding.

If the statement has no running balance, use the totals instead:

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

and compare that to the closing balance printed on the statement. This confirms the total but won't tell you which row is wrong, so check row counts per page as well.

Stage 2: match bank to ledger

Put your ledger or cash book entries for the same account and period on a second sheet, with the same structure: Date, Description, Amount (signed: money in positive, money out negative). Do the same with the bank data, adding a signed Amount in column H: =D3-C3.

Simple matching on amount

On the bank sheet, add a column that counts matching ledger entries:

=COUNTIFS(Ledger!$C:$C, H3)

where H3 is the bank amount and Ledger!C:C is the ledger amount column. A result of 0 means no ledger entry has that amount. Do the same in reverse on the ledger sheet.

Amount-only matching breaks down when the same amount appears several times (subscriptions, payroll). Tighten it with a date window:

=COUNTIFS(Ledger!$C:$C, H3, Ledger!$A:$A, ">="&A3-3, Ledger!$A:$A, "<="&A3+3)

This counts ledger entries with the same amount within three days either side. Adjust the window to suit how quickly your payments clear.

Pulling the matching entry across

To see which ledger entry matched, use XLOOKUP (Excel for Microsoft 365 and Excel 2021 onward):

=XLOOKUP(H3, Ledger!$C:$C, Ledger!$B:$B, "No match")

In older versions, INDEX/MATCH does the same job:

=IFERROR(INDEX(Ledger!$B:$B, MATCH(H3, Ledger!$C:$C, 0)), "No match")

Both return the first match only. When you have duplicates, mark matched pairs manually with a reference column (for example, write the ledger row number next to the bank line) so each entry is used once.

Sorting out the unmatched items

Filter both sheets to show unmatched lines. Each one falls into a category:

A useful check for transposition errors: if a difference is exactly divisible by 9, two digits may have been swapped.

The reconciliation summary

Put this at the top of the workbook. It's what a reviewer will read.

Link every figure to the underlying lists with SUMIFS on a Category column rather than typing numbers in. If the difference isn't zero, the summary tells you there's still an unexplained item.

Carry outstanding items forward. Next month, they should clear on the statement; anything still outstanding after a few months deserves investigation.

Where Rowsafe helps

Stage 1 is where Rowsafe fits. It converts text-based PDF statements into spreadsheets in your browser and runs the running-balance check on every row for you, flagging any row that doesn't add up before you export.

• No upload. The PDF is processed locally; your client's statement isn't sent to a server. You can confirm this in browser developer tools.
• Verified input. You start Stage 2 knowing the bank data is complete, or knowing exactly which row needs attention.
• Exports: CSV on the free plan (3 statements a day). Excel (XLSX), QBO, OFX, Xero CSV and JSON, plus batch conversion, on Pro at $12/month. See pricing.

Rowsafe doesn't do the ledger matching in Stage 2; that's your spreadsheet or accounting software. It also doesn't read scanned statements yet, and unusual layouts may need a column fix in the preview.

FAQ

How often should I reconcile? At least monthly, when each statement arrives. Busy accounts benefit from weekly reconciliation using the bank's transaction download.

Why use ROUND in the difference formula? Excel stores numbers in binary floating point, so sums of decimals can leave tiny residues like 0.0000000001. Rounding to two decimals removes them.

My difference is a small round number. Where do I look first? Bank fees and interest, which often aren't in the ledger until you see the statement.

Can I reconcile directly in QuickBooks or Xero instead? Yes, and for ongoing work you probably should. The Excel method is useful for historical periods, catch-up work, and accounts outside your accounting software. See the QuickBooks and Xero guides for importing statements.