Bank Reconciliation in Excel (Workflow)

A spreadsheet is enough to reconcile most accounts. You do not need dedicated software — Excel (or any compatible spreadsheet) gives you sorting, filtering, lookups, and conditional formatting, which together cover the whole job. This post walks through a practical bank reconciliation Excel template workflow: lay out the two sides, match what cleared, flag what did not, sum the adjustments, and arrive at a reconciled balance.

If you are new to the concept, start with what a bank reconciliation is and come back here for the hands-on version.

Step 1: Get clean rows on both sides

Reconciliation is really a matching exercise, so the first job is to get both sides into tidy, column-aligned rows. You need two tables:

• Book rows — your own cash ledger for the period, exported from your accounting tool or kept in a spreadsheet.
• Bank rows — every line the bank actually processed during the same period.

Bank statements often arrive as PDFs, which do not paste cleanly into a grid. Convert the statement to spreadsheet rows first so dates, descriptions, and amounts each land in their own column — the bank statement converter produces clean rows you can drop straight into your workbook. Consistent columns are what make the later matching steps work.

Set up each table with the same column structure, for example:

Date	Description	Amount	Type	Matched?

Keep amounts in a single signed column (deposits positive, payments negative) or split them into debit and credit columns — either works, as long as both tables use the same convention.

Step 2: Normalize the data

Small inconsistencies break matching, so spend a minute tidying both tables:

• Make sure the Amount column is a real number, not text. Spreadsheets sometimes import figures as text, which quietly fails any sum or lookup. Check the alignment or the cell format.
• Standardize dates to a single format.
• Trim stray spaces from descriptions.
• Confirm the sign convention is identical on both sides.

Sort each table by amount or by date. Sorting alone often makes matching pairs sit side by side and reveals duplicates at a glance.

Step 3: Match cleared items

A transaction that appears on both the bank side and the book side has cleared. Your goal is to mark every cleared pair so that whatever is left over is, by definition, a reconciling item.

There are a few ways to do this in a spreadsheet, and they vary in name between versions, so use whichever your edition offers:

• A lookup or match function. In the book table, add a helper column that searches the bank table for the same amount (and ideally the same date). When it finds a match, mark the row "cleared." Lookup-style functions return a value when they find a match and an error or blank when they do not, which is exactly the flag you want.
• Conditional formatting to highlight duplicates. Combine both tables' amounts into one column, then apply duplicate highlighting. Amounts that appear twice are likely a cleared pair; amounts that appear once are candidates for outstanding or unrecorded items.
• A pivot or grouping view. Group by amount to count how many times each figure appears across the two sides.

Matching purely on amount can produce false positives when two different transactions happen to share a value. Where that risk exists, match on a combination of date and amount, or eyeball the descriptions before you mark a row cleared. Tick the Matched? column on both tables as you confirm each pair.

Step 4: Flag outstanding and unrecorded items

Whatever is left unmatched falls into two buckets:

• In your books but not on the statement — outstanding checks and deposits in transit. You have recorded them; the bank has not processed them yet.
• On the statement but not in your books — bank fees, interest credited, or transactions you have not entered yet.

Filter each table to show only the rows still marked unmatched. Those filtered lists are your reconciling items, and they drive the adjustment math in the next step. (For a ready-made layout with these sections already built out, see the bank reconciliation template.)

Step 5: Sum the adjustments and compute the reconciled balance

Now build a small reconciliation block, typically off to the side or on a second sheet. The logic mirrors a manual reconciliation:

Start from the bank statement ending balance, then adjust it for items the bank has not yet processed:

• Add deposits in transit (money you recorded that the bank has not credited yet).
• Subtract outstanding checks (money you recorded that the bank has not paid yet).

Separately, start from your book ending balance and adjust for items the bank already processed but you have not recorded:

• Add interest or other bank credits.
• Subtract bank fees and charges.

A clean way to lay this out:

	Amount
Bank statement ending balance	5,000
+ Deposits in transit	0
− Outstanding checks	(250)
Adjusted bank balance	4,750
Book ending balance	4,800
+ Bank interest	0
− Bank fees	(50)
Adjusted book balance	4,750

Use a sum over your filtered outstanding-items list to populate each adjustment line, rather than typing figures by hand — that way the totals update if you reclassify a row.

The reconciliation is complete when the adjusted bank balance equals the adjusted book balance. Add a check cell that subtracts one from the other; when it reads zero, you are done. If it does not, the difference tells you how much is still unexplained.

Step 6: When the two sides don't agree

A non-zero difference typically traces back to one of these causes:

• A transaction marked cleared that was actually a false match on amount.
• A transposed or mistyped figure (a difference divisible by 9 is a classic sign of a digit swap).
• An amount stored as text, so it was excluded from a sum.
• A sign-convention mismatch between the two tables.
• A genuine reconciling item you have not classified yet.

Re-filter the unmatched rows, recheck the largest amounts first, and confirm every adjustment line actually sums its source list. Work through the items until the check cell hits zero.

Keeping it repeatable

Once the workbook is set up, each new period is mostly mechanical: paste in clean book rows, drop in fresh bank rows, refresh the lookups and filters, and read the check cell. One step in this cycle is getting the statement into rows in the first place, which is why starting from a converted statement can help. Save the structure as your reusable bank reconciliation Excel template and reconcile on a regular schedule so differences surface while they are still easy to trace.