How to reconcile a bank statement and ledger in Excel
Reconcile bank and ledger data by standardizing sign, currency, precision, dates, and references; applying exact matches first; then using controlled date and amount tolerances for one-to-one and grouped candidates. Lock accepted matches and classify every exception so reconciled totals plus documented differences equal source balances.
Where errors enter the process
Bank feeds and ledgers rarely align row for row. Posting dates differ, fees are netted, batches combine payments, descriptions are truncated, and one system may separate debit and credit columns.
A method you can follow
- make consistent both sources: Create comparable amount, date, currency, and reference fields while retaining raw columns. Confirm sign and precision.
- Apply deterministic passes: Start with unique reference-plus-amount matches, then unique amount-plus-date matches. Lock accepted transactions.
- Evaluate grouped matches: Search controlled windows for fees, settlements, one-to-many deposits, and batches. Require totals to reconcile within explicit tolerance.
- Classify exceptions: Label timing differences, missing entries, fees, duplicates, reversals, and unresolved items. Bridge the remaining variance.
From the field · Small-business bookkeeper
Reconcile a small-business ledger with card fees and local-currency deposits
The sales ledger records gross customer receipts in EUR and USD, while the bank deposits net amounts after card fees in USD. Settlement dates trail sale dates and one bank deposit may cover several invoices, so a same-date exact match cannot finish the job.
Columns used in the example
transaction_id · invoice_id · posted_date · amount · currency · exchange_rate_date · fee · reference
Follow it with your own file
- Standardize sign, precision, currency, and reference fields without overwriting raw values.
- Lock exact one-to-one matches before applying amount/date tolerances.
- Test grouped invoice-to-deposit candidates and disclose the fee or exchange-rate bridge.
- Classify every remainder as timing, fee, duplicate, missing entry, or unresolved.
Check: Opening balance plus ledger movement plus documented reconciling items must equal the closing bank balance. Reconciled, classified, and unresolved items must account for every source transaction exactly once.
Matching is an ordered decision process
One fuzzy score is hard to audit. Ordered passes are explainable: exact references, then amount and date, then controlled tolerance, then grouped settlements. Every pass should report candidates, accepted matches, and conflicts.
Exceptions are part of the answer
Unmatched does not mean failed. It can mean timing, a missing journal, a fee, a reversal, or insufficient evidence. Categories turn a vague list into accounting work.
Sign-off questions
- Signs and currencies aligned
- Passes ordered
- Matched rows locked
- Groups balanced
- Exceptions classified
- Balances reconciled
Evidence worth tracing
- Exact amount matching produces several candidates.
- One ledger item matches multiple bank rows.
- Exceptions cluster within a few days.
- Matched rows still do not reconcile balances.
Reconciliation match passes
| Pass | Evidence | Confidence |
|---|---|---|
| Reference + amount | Unique made consistent reference and equal amount | High |
| Amount + date | Unique amount in an approved window | Medium to high |
| Description similarity | Text plus amount and date | Review |
| Grouped settlement | Candidate group sums within tolerance | Review |
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Reconcile a bank statement and ledger while explaining unmatched transactions. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Multi Table analysis · Exception report · Lineage · Reusable workflow
The essential checks
- make consistent debit and credit signs before matching.
- Use deterministic exact rules before fuzzy descriptions.
- Never reuse a transaction across accepted matches.
- Completion requires totals and exceptions to explain the source difference.
A traceable match table, a prioritized exception queue, and a bridge explaining the full bank-to-ledger difference.
Questions readers usually ask next
What date tolerance should I use?
Use the smallest window supported by posting behavior, often one to three business days, and document it. There is no universal safe value.
How should partial payments be matched?
Treat them as grouped candidates and require the selected payments to reconcile without reusing entries.
Can descriptions alone identify a match?
Usually not. Combine descriptions with amount, date, reference, and uniqueness evidence.