How to do a bank reconciliation in Excel
By the Analistable team · Updated · 6 min read
Put the bank statement and the ledger's cash account on two sheets. Match transactions by amount and date (within a few days), mark each as matched, then list what's left: deposits in transit and outstanding payments (in the ledger, not yet in the bank) and bank-only items such as fees and interest (in the bank, not yet in the ledger). Adjusted bank balance must equal adjusted ledger balance.
Part of our guide: How to reconcile data in Excel
Match the transactions
On the ledger sheet:
Match =IF(COUNTIFS(Bank[Amount], [@Amount], Bank[Date], ">="&[@Date]-3, Bank[Date], "<="&[@Date]+5) > 0, "Matched", "Not in bank")Do the same on the bank sheet against the ledger. Amounts must use the same sign convention (money out negative on both sheets). Review any amount that appears more than once on the same day by hand.
The reconciliation statement
| Line | Amount |
|---|---|
| Balance per bank statement | 12480 |
| + Deposits in transit | 1250 |
| − Outstanding payments (cheque 1042) | -620 |
| = Adjusted bank balance | 13110 |
| Balance per ledger | 13124.2 |
| − Bank fees not yet recorded | -14.2 |
| = Adjusted ledger balance | 13110 |
Both adjusted balances are 13,110.00, so the account reconciles. The fee needs a ledger entry; the deposit and cheque will clear next month.
When it doesn't balance
- Difference divisible by 9 → probably a transposition (540 entered as 450).
- Difference equal to twice a transaction → an entry posted with the wrong sign.
- Difference equal to one transaction → a missed or duplicated entry; filter both sheets for that amount.
- Opening balances differ → last month's reconciliation didn't carry its outstanding items forward.
Make it repeatable
Keep one workbook per account with a sheet per month, carrying outstanding items forward. Or load both files in Power Query and use left and right anti joins to list unmatched items automatically each month — see reconciling data in Excel.
Card processors and PayPal add another layer between sales and the bank: reconciling PayPal with your bank statement.
Carrying items forward
Outstanding items at the end of one month must clear in the next. Start each month's reconciliation by listing last month's outstanding items and ticking them off as they appear on the new statement; any still outstanding after two or three months (an uncashed cheque, a deposit that never arrived) needs action.
Step by step in Excel
- Export the bank statement as CSV for the exact period, and the ledger's cash account transactions for the same dates.
- Paste each into its own sheet and format as a table (Ctrl+T, or Cmd+T on a Mac); name them Bank and Ledger.
- Make the signs consistent. If the bank export has separate Paid in and Paid out columns, add
=[@[Paid in]]-[@[Paid out]]as a single Amount column. - Add the Match column to both tables with the COUNTIFS formula above.
- Filter each table to unmatched rows and classify them: timing (deposit in transit, outstanding payment) or error (missing entry, wrong amount).
- Fill in the reconciliation statement and check both adjusted balances agree.
In Google Sheets the same COUNTIFS works with ordinary ranges instead of table names. The dynamic FILTER function needs Excel 2021 or Microsoft 365; in Excel 2019 and earlier, use AutoFilter on the Match column instead.
Second example: finding a transposition
Suppose the adjusted balances differ by 90.00. The difference divides by 9, so look for a transposed amount. Filter both sheets for unmatched rows:
| Sheet | Date | Description | Amount |
|---|---|---|---|
| Ledger | 2 Sep | Supplier A | -450 |
| Bank | 3 Sep | SUPPLIER A | -540 |
The supplier was paid 540 but the payment was posted as 450, so the ledger's cash balance is 90 too high. Correct the ledger entry rather than adding a reconciling item: errors are fixed at source, and only timing differences belong on the statement.
Matching with SQL
With many transactions, two anti joins list everything unmatched on each side. This SQLite version uses the same −3 / +5 day window:
-- Ledger entries not found in the bank
SELECT l.*
FROM ledger l
WHERE NOT EXISTS (
SELECT 1 FROM bank b
WHERE b.amount = l.amount
AND b.date BETWEEN date(l.date, '-3 day') AND date(l.date, '+5 day')
);
-- Bank lines not found in the ledger
SELECT b.*
FROM bank b
WHERE NOT EXISTS (
SELECT 1 FROM ledger l
WHERE b.amount = l.amount
AND b.date BETWEEN date(l.date, '-3 day') AND date(l.date, '+5 day')
);On a sample with the transposed supplier payment above, the first query returns the −450 ledger entry, the deposit in transit and cheque 1042; the second returns the −540 bank line and the bank fee. Like COUNTIFS, this doesn't stop one bank line matching two identical ledger entries, so review duplicate amounts by hand.
Matching problems
| Symptom | Likely cause | Fix |
|---|---|---|
| Nothing matches | Opposite sign conventions on the two sheets | Multiply one Amount column by −1 |
| Dates won't compare | Bank dates imported as text | Convert with DATEVALUE or Text to Columns |
| One bank credit, several ledger receipts | Bank paid in several cheques as one deposit | Group ledger receipts by paying-in slip and match the total |
| Card settlements don't match sales | Processor pays net of fees | Reconcile the processor first, then match its payouts |
| Matched item reappears next month | Opening outstanding list not cleared | Mark cleared items with the date they cleared |
Card processor payouts are a common source of the one-to-many problem — see reconciling Stripe payouts and PayPal transfers before matching them here.
Spreadsheet or accounting software?
If your accounting package has a bank feed, use its reconciliation screen for day-to-day matching. A spreadsheet is still useful for catching up several months at once, for accounts without a feed, for auditors who want the full working, and for checking a year-end balance independently of the software that produced it.
Checking the result
- The adjusted bank balance equals the adjusted ledger balance to the penny.
- Every reconciling item is either a timing difference that clears next month or a ledger correction that has been posted.
- Last month's outstanding items have either cleared this month or are still listed, with their age.
- Items older than a few months are investigated rather than carried forward indefinitely; a cheque that never clears may need to be written back.
Common mistakes
- Starting from a bank balance on a different date from the ledger's cut-off — use the statement balance at the exact month-end date.
- Adding a ledger correction to the reconciliation statement as a reconciling item instead of posting it.
- Ticking off items by amount alone when the same amount appears several times in a month, such as a regular standing order.
- Forgetting card and payment processor clearing accounts, which make sales look unmatched in the bank.
Several bank accounts
Reconcile each bank account separately against its own ledger account, even if they are with the same bank. Transfers between your own accounts should appear twice in the bank data (out of one, into the other) and net to zero in the ledger; an unmatched transfer usually means one side was posted to the wrong account or on a different date.
Frequently asked questions
- How do I do a bank reconciliation in Excel?
- Match bank and ledger transactions by amount and date, list the unmatched items, and show that the adjusted bank and ledger balances are equal.
- What are deposits in transit?
- Money recorded in your ledger that hasn't appeared on the bank statement yet, usually because it was deposited at the end of the period.
- What if my bank reconciliation is out by a small amount?
- Look for fees or interest not yet recorded, then for transpositions (differences divisible by 9) and sign errors.