Every month ends the same way for a lot of small businesses: the bank says one number, the bookkeeping spreadsheet says another, and nobody is quite sure why. The gap gets noted, the month gets closed anyway, and six months later the discrepancy is large enough that untangling it takes a full day.
Bank reconciliation is the discipline that prevents that. It is the process of matching your records against the bank statement line by line, explaining every difference, and proving that the two sides agree once timing and bank-side items are accounted for. This guide explains how to reconcile a bank statement in Excel, what adjustments to make, and where an automated Excel Difference Checker saves the most time.
What Bank Reconciliation Actually Means
At its core, reconciliation answers one question: after accounting for everything we know about, do my records and the bank agree?
Crucially, the two balances almost never match on the first try, and that is normal. Money moves at different speeds through the two systems.
Your books record a payment when you write or authorize it. The bank records it when it actually clears. Between those two moments sits a timing gap, and that gap is the entire subject of reconciliation.
A reconciliation has three parts:
- Matching the transactions that appear on both sides.
- Identifying the items that appear on only one side.
- Adjusting both balances for those items until the two agree.
The third step is what makes reconciliation more than a comparison exercise. Finding a difference is the easy part. Explaining it, and adjusting correctly, is the actual work.
Bank Reconciliation vs. Bank Statement Comparison
These two get used interchangeably, and conflating them is why so many reconciliation attempts stall halfway.
Bank statement comparison finds differences between two sets of transaction data. It tells you which rows differ. It does not tell you why, and it does not produce an adjusted balance.
Bank reconciliation goes further. It explains each difference, classifies it as a timing item, a bank-side item, or a genuine bookkeeping error, and adjusts until both sides tie to the same number.
Put simply:
Compare → Find differences → Investigate → Adjust → Reconcile
Comparison is the first two stages. Reconciliation is the whole chain.
If your goal is only to find which rows changed between two files, our guide on How to Compare Bank Statements in Excel and Find Differences covers that narrower job in more depth. This article picks up where it ends and walks through the adjustment and tie-out stages that turn findings into a closed month.
What You Need Before You Start
Gather four things before opening a spreadsheet:
- The bank statement for the period, ideally as Excel or CSV. If your bank only issues PDFs, extract the transactions first with the AI Bank Statement Converter.
- Your accounting records for the same period, exported to Excel or CSV.
- The closing balance from the previous reconciliation. If last month was never reconciled, start there instead of starting this month.
- Any bank notices about fees, interest, chargebacks, or returned items you have not yet recorded.
The previous closing balance matters more than people expect. Reconciliation is sequential: each month's starting point depends on the prior month being correct. Skipping a month and jumping forward means the unexplained gap compounds.
The Bank Reconciliation Process in Excel
Here is the full sequence. It looks long written out, but most of it becomes routine after two or three cycles.
Step 1 — Set Up Both Sides
Create one workbook with two clearly labeled sections.
On one side, the bank column: opening balance per bank, all bank transactions for the period, and closing balance per bank.
On the other, the book column: opening balance per your records, all recorded transactions, and closing balance per your books.
Lay them out so a reader can see both totals side by side. Half the value of a reconciliation is that it is legible to someone else later, including your accountant.
Step 2 — Match Transactions Line by Line
Work through the two lists and tick off every transaction that appears on both sides.
Match on more than amount. Two $250 charges in the same month are not necessarily the same transaction. Use date, description, and reference number together, and be especially careful with round numbers that recur.
In Excel, a practical approach is a Matched column on each side with a simple marker, or a lookup formula that flags whether each row has a counterpart.
Do not skip ahead. Work chronologically from the opening balance forward.
Step 3 — List the Unmatched Bank Items
These are transactions the bank recorded but your books have not. The usual categories:
- Bank fees and service charges: Monthly maintenance, wire fees, merchant charges.
- Interest earned or charged: Often the single most commonly missed item.
- Automatic debits: Subscriptions, loan payments, or tax debits set up directly with the bank.
- Returned items and chargebacks: A customer payment that bounced, or a card dispute resolved against you.
- Bank errors: Rare, but they happen, and the bank needs documentary evidence to correct them.
Each of these belongs in your books. Add them, because they are real activity you simply did not know about yet.
Step 4 — List the Unmatched Book Items
These are transactions you recorded that the bank has not cleared. Almost all of them are timing.
- Outstanding checks: You issued the payment; it has not been presented.
- Deposits in transit: You recorded a receipt; the bank posted it after the statement cut-off.
- Post-dated or scheduled payments: Recorded in your books, releasing later.
These do not get added to the bank side. They are legitimate, and they will clear in the next period. The point is to list them explicitly so the gap is explained rather than mysterious.
Step 5 — Calculate Both Adjusted Balances
Now adjust each side for its own unmatched items.
Adjusted bank balance:
Closing balance per bank
+ Deposits in transit
- Outstanding checks
= Adjusted bank balance
Adjusted book balance:
Closing balance per books
+ Interest earned
- Bank fees
- Returned items / chargebacks
+/- Bank errors
= Adjusted book balance
Note the asymmetry, because it trips people up constantly. Timing items adjust the bank side. Bank-side items you never recorded adjust the book side. Getting those backwards produces a reconciliation that appears to work while being wrong.
Step 6 — Confirm the Two Agree
If the adjusted balances match, the period is reconciled.
If they do not, something is still missing. Work back in this order:
- Re-check the opening balance against the prior reconciliation.
- Look for a transposed amount, which produces a difference exactly divisible by 9.
- Look for a duplicated or omitted transaction.
- Check the date range, since a transaction posted on the first or last day easily falls outside one file but not the other.
Do not fudge a difference into a plug account to force the two sides to tie. A reconciliation that balances by accident is worse than one that does not balance, because it hides the error instead of surfacing it.
Worked Example: A Monthly Reconciliation
Suppose a small agency reconciles August.
Starting point:
| Item | Amount |
|---|---|
| Closing balance per bank | $18,420.00 |
| Closing balance per books | $19,115.00 |
The books are $695 higher. Here is what the line-by-line review turns up:
Unmatched bank items (not yet in the books):
| Item | Amount |
|---|---|
| Monthly service fee | -$25 |
| Merchant processing fee | -$118 |
| Interest earned | +$12 |
| Returned customer check | -$350 |
Unmatched book items (not yet cleared by the bank):
| Item | Amount |
|---|---|
| Outstanding check to contractor | -$900 |
| Deposit in transit | +$1,000 |
Adjusted bank balance:
$18,420 + $1,000 - $900 = $18,520
Adjusted book balance:
$19,115 - $25 - $118 + $12 - $350 = $18,634
The two do not tie. There is still a $114 gap, which means a fourth category: something is misrecorded, not merely mistimed.
In this case, further digging finds a $114 software charge entered twice. Correcting that duplicate brings the adjusted book balance to $18,520, matching the bank exactly.
Note what made the difference visible: adjusting both sides properly, rather than assuming the first imbalance was just timing.
Handling the Unmatched Items
How you resolve a difference depends on what kind it is.
| Type of difference | What it means | How to resolve |
|---|---|---|
| Outstanding check | Payment issued, not cleared | Leave it; it clears next period |
| Deposit in transit | Receipt recorded, not posted | Leave it; it posts next period |
| Bank fee | Real cost you had not recorded | Add to your books |
| Interest earned | Real income you had not recorded | Add to your books |
| Returned or bounced item | Income that did not materialize | Reverse it in your books |
| Duplicate entry | Bookkeeping error | Correct your books |
| Transposed amount | Bookkeeping error | Correct your books |
| Bank error | Bank recorded it incorrectly | Contact the bank with documentation |
Two rules follow from that table. Timing items stay as timing items and resolve themselves next month. Everything else gets corrected now, in the period where it occurred.
Where an Automated Difference Checker Fits
Matching two 800-row files by eye is where most of the hours go, and it is also the part least suited to human attention.
An automated comparison tool narrows the dataset to the rows that actually differ, so you start the reconciliation with a shortlist instead of a haystack. BankGPT's Excel Difference Checker compares two Excel or CSV files and identifies records that were added, removed, or changed.
The practical division of labor looks like this:
Tool: find which rows differ
You: decide why, classify it, adjust
Tool: confirm the totals tie
The judgment step cannot be automated, because deciding whether a difference is a timing gap or a bookkeeping error requires context the file does not contain. But surfacing the differences automatically removes the most tedious part of the month-end routine.
Once the differences are on screen, the six steps above take the rest of the way.
Common Problems When Reconciling Bank Statements
The Opening Balance Was Never Verified
If last month was never reconciled, this month's starting point is unverified and the gap carries forward. Fix the earliest unreconciled period first and work forward.
Different Date Ranges
Banks cut statements on their own calendar. Make sure both files cover exactly the same window, or a transaction on the boundary will look like a difference when it is just a cut-off artifact.
Debit and Credit Signs
One system stores withdrawals as negative amounts, another as positive values in a Debit column. Normalize the signs before matching, or every outflow will appear unmatched.
Merchant Descriptions Differ
A bank may show AMZN Mktp US where your records say Amazon Marketplace. Same transaction, different text. Match on amount and date when descriptions diverge.
Rounding and Currency
Small rounding differences across many rows add up. If the gap is a few cents, check decimal handling before hunting for a missing transaction.
Split or Combined Transactions
A single bank deposit may represent three invoices you recorded separately. Expect to match some rows one-to-many rather than one-to-one.
Reconciling Out of Sequence
Jumping to the current month while older periods are unreconciled compounds every error. Always work forward from the last verified point.
Best Practices for Monthly Reconciliation
- Reconcile every month, not once a year. Twelve small reconciliations take far less time than one large one, and problems are still traceable.
- Start from a verified opening balance. An unverified starting point invalidates everything downstream.
- Work chronologically. Do not skip periods.
- Keep the reconciliation as a saved file. It is a record that the month was closed and reviewed.
- Record bank-side items immediately. Fees and interest belong in the period they occurred.
- Never plug a difference. If it does not tie, something is unresolved.
- Use a tool for the matching step. Let software find the differences and spend your judgment on explaining them.
- Document recurring items. A monthly wire fee should be anticipated, not rediscovered each month.
The goal is not a tidy spreadsheet. It is confidence that the balance you are making decisions on is real.
Who Benefits Most From Bank Reconciliation?
- Small business owners can trust their cash position before making hiring or inventory decisions.
- Bookkeepers catch errors while they are still small and attributable.
- Accountants receive periods that are already closed, which turns review into verification.
- Finance teams can produce accurate reporting without a year-end cleanup project.
- Auditors get a documented trail showing each period was reviewed and tied out.
If your bank only provides PDF statements, start with the AI Bank Statement Converter to extract structured transactions before you begin matching.
Frequently Asked Questions
How do I reconcile a bank statement in Excel?
Set up the bank side and the book side in one workbook, match transactions that appear on both, list the items that appear on only one side, adjust each side for its own unmatched items, and confirm the two adjusted balances agree.
What is the difference between bank reconciliation and bank statement comparison?
Comparison identifies which rows differ between two datasets. Reconciliation explains each difference, classifies it as timing or error, and adjusts both balances until they tie. Comparison is the first stage; reconciliation is the complete process.
Why do my adjusted balances not match?
Usually one of four things: the opening balance was never verified, a transaction is duplicated or missing, an amount is transposed, or the two files cover different date ranges. Work through those in order before assuming a more complex cause.
Do outstanding checks get added to the bank balance?
No. Outstanding checks are subtracted from the bank balance, because the bank has not yet paid them. They adjust the bank side, not your books, and they clear in the following period.
How often should I reconcile bank statements?
Monthly, for most businesses. High-volume accounts may warrant weekly reconciliation. The key is consistency: an unreconciled period makes every subsequent period harder to trust.
Can I reconcile if my bank only provides PDF statements?
Yes, but convert them first. Extract the transactions into Excel or CSV with the AI Bank Statement Converter, then reconcile the structured data.
Should I adjust my books for bank fees?
Yes. Bank fees, interest, and returned items are real activity that belongs in your records for the period in which they occurred. Leaving them unrecorded is what causes balances to drift over time.
Conclusion
Bank reconciliation is not about producing a spreadsheet that balances. It is about knowing, with evidence, that the number you are running your business on is correct.
The monthly sequence is always the same: start from a verified opening balance, match what appears on both sides, list what appears on only one, adjust each side for its own items, and prove the two tie. Most of the difficulty people experience comes from skipping the opening balance or from treating a genuine error as if it were a timing difference.
The matching step is where the hours go, and it is the one part worth handing to software. Use the Excel Difference Checker to surface the rows that differ, then apply the six steps above to explain and resolve each one. If your statements are still PDFs, start with the AI Bank Statement Converter to get them into structured form.
Do it monthly, and year-end stops being a cleanup project.
