How to do a bank reconciliation in Excel
- Export both sides. Download the bank statement for the period as Excel or CSV, and export the bank account from your accounting software (the general ledger or cash book for that account).
- Load the two files and check the columns: date, description and amount. If your bank uses separate debit and credit columns, choose “Money in / money out”.
- Choose a date window. Payments and receipts often appear one to three days apart in the bank and in the books. ±3 days is a good start.
- Click Reconcile. Lines with the same amount inside the window are paired one to one; the description breaks ties.
- Work through the differences. “Only in bank” usually means fees, interest or direct debits not yet booked. “Only in ledger” is usually outstanding cheques and deposits in transit. Download the Excel file and keep it as your reconciliation working paper.
Private by design: bank data stays on your device
Bank statements and ledgers are some of the most sensitive files a business has. This tool reads and reconciles them inside your browser, in a background worker. Nothing is uploaded, there is no account, and no AI service sees your transactions.
The page only downloads its own code and the open-source library that reads Excel files (SheetJS). You can check it in the Network tab of your browser’s developer tools, or go offline after the page loads and keep working.
What is matched, and in what order
- 1. Exact amount. Amounts are compared in cents after normalising the sign, so 1,250.00 in the bank matches 1250 in the ledger. Rounding differences are not matched.
- 2. Date window. Only lines whose dates are at most N days apart are candidates. Closer dates win.
- 3. Description as a tie-breaker. When several lines have the same amount and the same distance in days, the most similar description wins (“TRANSFER ACME CORP” pairs with “Acme Corporation, invoice 1042”). The similarity engine ignores accents, case and suffixes such as Inc. or Ltd.
- 4. One to one, optimised. Every line is used at most once. Among identical amounts the tool finds the assignment that pairs the most lines, not just the first match it sees.
- 5. Optional: one to many by sum. One bank line can be matched with 2 to 4 ledger lines (or the reverse) that add up to exactly the same amount, with the same sign and inside the date window. To stay reliable it searches at most 20 nearby candidates and only accepts the match when exactly one combination works.
Sign conventions: why the bank and your books look opposite
A bank statement is written from the bank’s point of view: a debit takes money out of your account and a credit puts money in. In your own books the bank account is an asset, so it works the other way round: money received is a debit to the bank account and money paid is a credit. That is why the same payment appears as a “debit” in one file and a “credit” in the other.
The tool normalises both sides so that money in is positive. For each file choose either one signed amount column or two columns, and say which one is money in. If your export shows money in as negative, tick “Flip the sign”. If almost nothing matches but many amounts would match with the opposite sign, the result shows a warning.
| Transaction | Bank statement | Ledger (bank account) | Normalised |
|---|
| Customer pays an invoice | Credit 1,200.00 | Debit 1,200.00 | +1,200.00 |
| You pay a supplier | Debit 350.40 | Credit 350.40 | −350.40 |
| Monthly bank fee | Debit 12.00 | (not booked yet) | −12.00 |
Why it is hard with VLOOKUP or SUMIF
The usual spreadsheet method is a helper column with =COUNTIF(Ledger!C:C,C2) or a VLOOKUP on the amount. It breaks as soon as two transactions share the same amount: both bank lines find the first ledger line, and nothing stops the same line from being used twice. Lookups also ignore dates, so a payment from March can be “matched” with one from May, and they cannot find a single bank receipt that pays three invoices. A pivot table or SUMIF by date shows that totals differ, but not which lines cause the difference.
This tool does the one-to-one assignment, respects a date window and lists the exact lines that are left on each side.
Who it is for
- Small businesses and freelancersCheck the bank against the books every month without an accounting add-on.
- Bookkeepers and accountantsReconcile client accounts exported from any accounting software.
- AuditorsTest a reconciliation prepared by the client and list the unmatched items.
- TreasurersMatch several bank accounts or a card statement against expense reports.
- Non-profits and clubsMatch membership fees and donations against the bank feed.
- Payment processorsMatch payouts against the orders or invoices they settle, with the one-to-many option.
Accounting suites have reconciliation built in, and there are spreadsheet add-ons and AI services that ask you to upload your statements. This page is for when you just have two spreadsheets and want an answer without sending your bank data anywhere.
Known limitations
- Amounts must match to the cent. Bank charges deducted from a transfer, currency conversions or rounding differences are left unmatched for you to review.
- Many-to-many groups (several bank lines against several ledger lines) are not searched.
- One-to-many by sum is limited to 2–4 lines and 20 candidates within the date window, and is skipped when more than one combination fits.
- Dates written as text such as 03/04/2024 are ambiguous; check the detected order or set it under “Dates written as text”.
- One sheet per file is used and the files must have one row per transaction. PDF statements must be converted to Excel or CSV first.
- The result is a working paper to help you, not an accounting opinion. Review the pairs before you book adjustments.