How to automate a bank reconciliation in Excel

Start with your bank's own tools

If your accounting system has bank feeds and matching rules that already clear most lines, keep using them. Excel automation earns its place when the system leaves a recurring residue: several bank accounts, deposits that bundle many receipts, references that are missing or truncated, or a ledger outside a modern accounting package.

Step 1: Get both sides into the same shape

Import the bank statement export and the ledger detail for the cash account, ideally with Power Query from a folder so each month is a refresh. Give both tables the same columns: date, amount with one sign convention, reference, description and a source row ID.

Clean the reference fields. Bank descriptions often add prefixes such as "ACH CREDIT" or "EFT". Extract the part that matches your ledger reference into its own column.

Step 2: Match in passes, strictest first

1. Exact. Same amount and same reference. These clear first and need no review.
2. Amount and date window. Same amount within a few days, only where exactly one candidate exists. If two candidates qualify, hold both for review rather than picking one.
3. Grouped. One bank deposit equal to the sum of several ledger receipts, such as a batch of cheques or card settlements. Group ledger items by deposit reference or date and compare the totals.
4. Known timing items. Outstanding cheques and deposits in transit that will clear next period.

Each pass only sees what earlier passes left unmatched. That ordering is what keeps a loose rule from creating a false match.

Step 3: Build the reconciliation statement

The output is the familiar statement: balance per bank, plus deposits in transit, less outstanding payments, equals balance per ledger. Every reconciling item should link back to its source row. The unexplained difference should be zero, and if it is not, the reconciliation status should say so.

Step 4: Keep the exceptions short and specific

Items that no pass could match go to an exceptions list with the reason: no candidate found, several candidates found, or amount mismatch. Bank fees, interest and returned items often appear here first. Once their pattern is clear, they can become rules of their own.

Common mistakes

• Matching on amount alone in the first pass, which pairs unrelated items with the same value.
• Deleting matched rows instead of flagging them, which removes the audit trail.
• Rolling forward old reconciling items without checking they eventually cleared.
• No check that the statement balances used agree to the bank and the ledger.

The same multi-pass approach drives the Excel workflow demo and the larger reconciliation reference build, both on synthetic data.