TL;DR:
Reconciling means proving that your records and the bank's records tell the same story, and explaining every difference.
Put both lists in a sheet, sort them the same way, match each line, and flag what has no partner.
You are done only when the adjusted bank balance equals the adjusted ledger balance.
You open the bank app and it says one number. Your own records say another. Nothing is necessarily wrong: a cheque you wrote has not cleared yet, a deposit made on the last day has not posted, and the bank took a fee you did not know about. Reconciling is the job of finding every one of those gaps and showing that they fully explain the difference.
When a reconciliation earns its keep
Do it once a month for every account you run money through, and always before you close a month, send a report or file anything. The longer you wait, the harder it is to remember what a mystery charge was. A monthly pass of twenty minutes beats an annual afternoon of detective work.
What to have open before you start
The bank statement for the month, or a CSV or spreadsheet download of its transactions if your bank offers one.
Your own ledger for the same dates: the sheet, bookkeeping file or list where you record money in and out.
The opening balance both sides agree on, which is last month's reconciled ending balance.
A blank sheet with two tabs named Bank and Ledger.
Use one sign convention on both tabs: money in is positive, money out is negative. Mixing a separate debit column on one side with signed amounts on the other is the most common reason a match fails for no visible reason.
The four moves: lay out, sort, match, flag
Lay out both lists the same way: On each tab use the columns Date, Description, Amount, and a fourth column called Key. Paste the bank lines on one tab and your ledger lines on the other. Keep the original data; do not retype amounts.
Sort both by date, then amount: Sorting puts identical items near the same row on both tabs, which makes the eye check quick. Sorting does not do the matching, but it makes the leftovers obvious.
Build a matching key: Floating point decimals can trip a comparison, so convert the amount to whole cents: in D2 on both tabs enter
=ROUND(C2*100,0)and fill down. Now an amount of 64.50 becomes the whole number 6450 on both sides.Flag lines with no partner: In E2 on the Bank tab enter
=IF(COUNTIF(Ledger!$D:$D,D2)=0,"not in ledger","ok"). On the Ledger tab enter=IF(COUNTIF(Bank!$D:$D,D2)=0,"not at bank","ok"). Fill both down and filter for anything that is not ok.Explain every flag: Each flagged line is either timing (it will clear next month), a bank item you have not recorded yet, or an error in your records. Write the reason in column F next to it.
One caution about the flag formula: it only asks whether the same amount exists somewhere on the other tab. If you pay 50.00 twice and the bank shows it once, both of your lines look fine. When amounts repeat, add the date to the key, for example =TEXT(A2,"yyyy-mm-dd")&"|"&ROUND(C2*100,0), or count the occurrences on both sides and compare.
Worked example: matching a June statement to a ledger
These numbers are made up for the example. Both sides start the month at 2,500.00. Here is the bank statement:
Date | Bank description | Amount |
|---|---|---|
Jun 1 | Deposit, client A | +1,200.00 |
Jun 3 | Rent | −900.00 |
Jun 5 | Supplies | −64.50 |
Jun 9 | Deposit, client B | +450.00 |
Jun 12 | Software | −29.00 |
Jun 15 | Monthly account fee | −12.00 |
Jun 20 | Fuel | −48.30 |
The statement's ending balance is 2,500.00 + 1,200.00 − 900.00 − 64.50 + 450.00 − 29.00 − 12.00 − 48.30 = 3,096.20.
Your ledger has the same first four items, then software and fuel, plus three lines the bank does not show yet or does not know about:
Date | Ledger description | Amount |
|---|---|---|
Jun 1 | Client A payment | +1,200.00 |
Jun 3 | Rent | −900.00 |
Jun 5 | Supplies | −64.50 |
Jun 9 | Client B payment | +450.00 |
Jun 12 | Software | −29.00 |
Jun 20 | Fuel | −48.30 |
Jun 28 | Cheque 1042, to a contractor | −200.00 |
Jun 30 | Client C deposit, paid in on the last day | +300.00 |
The ledger's ending balance is 2,500.00 + 1,200.00 − 900.00 − 64.50 + 450.00 − 29.00 − 48.30 − 200.00 + 300.00 = 3,208.20. The two balances differ by 3,208.20 − 3,096.20 = 12.00 in the ledger's favor, which is a good hint, but you must account for each flagged line rather than guess.
After sorting and the flag formulas, three lines are not ok. The bank's 12.00 fee is not in the ledger. The ledger's cheque for 200.00 and the 300.00 deposit are not at the bank. Now explain each one:
Cheque 1042, −200.00: written but not cashed yet. This is an outstanding cheque, pure timing. It will appear on next month's statement.
Client C deposit, +300.00: paid in on the 30th but not posted. This is a deposit in transit, also timing.
Account fee, −12.00: a real transaction you forgot. Add it to the ledger now. This is not timing, it changes your books.
The ending balance check
Adjust each side for the items only it is missing and see whether they land on the same number.
Bank side:
Statement ending balance 3,096.20
+ deposit in transit 300.00
− outstanding cheque 1042 200.00
= adjusted bank balance 3,196.20
Ledger side:
Ledger ending balance 3,208.20
− account fee not yet booked 12.00
= adjusted ledger balance 3,196.20
Difference: 3,196.20 − 3,196.20 = 0.00A difference of zero is the finish line. In the sheet, put the final check in one cell, such as =ROUND(B20-B27,2), pointing at your two adjusted balances, so you see 0 at a glance. If it is not zero, the number you get is a clue: a difference of exactly one of your amounts suggests a missing line, and a difference of exactly twice one of your amounts suggests that line was entered with the wrong sign.
Mistakes that waste an hour
Starting from the wrong opening balance. Confirm it equals last month's reconciled figure before you match anything.
Matching by description instead of amount. Banks shorten and rewrite names; amounts do not change.
Forcing a match. If you plug a number to make the difference zero, you have hidden the problem, not solved it.
Leaving flagged lines unexplained. Every line gets a reason in writing, even the harmless ones, so next month starts clean.
Reconciling in the same file you edit daily with no copy. Save a dated copy once it balances.
Doing it faster
The speed comes from the routine: same two tabs, same key formula, same flag formulas every month, so the only new work is explaining the flags. If you want a hand reading a formula you do not trust, DuckyHelper can look at your sheet on screen and talk you through it while you work. It never replaces checking the final zero yourself.
General information only: This is a plain explanation of a bookkeeping routine, not accounting advice. For tax filings or anything your accountant relies on, check with a professional.
Frequently asked questions
How often should I reconcile?
Monthly is the usual rhythm because bank statements come monthly. Busy accounts with many daily transactions can be checked weekly, which keeps each pass small.
What if the difference is small and I cannot find it?
Do not write it off as rounding. Re-add both lists, check for a transposed pair of digits (like 45.60 entered as 45.06), and look for a line entered twice. A difference that is a multiple of 9 often points to swapped digits.
Get started
