Your bank feed already matched it — so why is the reconciliation still out?

How-to · Updated September 2026 · ~20 min read

The feed pulled 214 lines in from the bank. QuickBooks matched 209 of them to transactions that were already there, you ticked the last five yourself, and the Reconcile screen still reads Difference 2,372.75. That screen tells you the total is wrong. It will not tell you which line is wrong, and it will not let you leave until you either find it or write the difference off to a suspense account you will have to explain next year.

This is not the reconciliation the textbooks describe. There are no outstanding cheques to list — the feed brought everything across. What there is instead is a duplicate created when the feed imported a payment somebody had already keyed by hand, a line that got Excluded in a hurry last March, and a transaction that was edited in July after being reconciled in April. All three are close to invisible inside the ledger. All three are obvious the moment you put the bank's own statement next to the ledger's export in one workbook and let a formula do the ticking.

Below: which two exports to take and where each system hides them; a free three-sheet template that flags every line on either side with no partner; and the five causes in the order that finds them fastest. Download the free bank reconciliation template (.xlsx) — no sign-up, no email gate.

Scope: this is the template page. The matching method in depth lives in reconciling a bank statement in Excel with AI, the scripted version in the Python bank reconciliation tutorial.

The two exports you need (and where each system hides them)

Two files, side by side. The first is the statement from the bank itself — the CSV, OFX or QIF you download from online banking. Not the feed. That distinction is the whole point of the exercise: the feed is the thing under suspicion, so a reconciliation that takes the feed as its evidence has proved nothing except that the feed agrees with itself.

The second is the ledger's own record of the same account over the same dates. Where to take it:

Check one thing before you paste either of them, because it is the single biggest cause of a first attempt that reads UNMATCHED on every row: the sign convention. Most bank exports carry money out as a negative figure. Plenty of ledger exports carry it as a positive figure in a column headed Spent, or split money in and money out across two separate columns. If the two sides disagree, nothing will ever match on amount and the sheet will look catastrophically broken when it is merely mis-signed. Fix it once — a helper column of =-C2 pasted back as values, or a subtraction that collapses two columns into one signed amount — before you go any further.

While you are looking at the export, confirm the dates are real dates rather than text. A date column Excel has read as text makes every date criterion below fail silently.

What the template does with those two exports

Three sheets, all formulas live, nothing locked. It is one of the free Excel templates for accountants published here, alongside the P&L, balance sheet and close checklist.

The Matched? column on each sheet is a COUNTIFS against the other sheet's Amount column. Paste both sides and every filled line reads matched or UNMATCHED; unused rows stay blank. The unmatched lines are your worklist.

One layout rule, because the formulas ship pre-filled and point at fixed columns: dates go in column A and amounts in column C on both sheets. If your export puts them somewhere else, move the columns rather than rewriting the formulas — it is quicker and it keeps the two sheets mirror images of each other.

Bank rec is one step in a wider close: the month-end close checklist template sequences the 20 tasks around it (walkthrough).

Why a fed ledger still disagrees with the bank

The textbook gives three causes and all three are still true. Timing: you posted a cheque on the 29th and the supplier banks it on the 4th, so both records are correct at different points in the same transaction. Items only one party knows about: the bank charged 80 in fees and credited 12 of interest, and your ledger never heard. Errors: a transposed figure, a payment entered twice, an amount on the wrong side — and yours are far more common than the bank's.

What those three do not describe is the month where a feed has been running and the system is already telling you almost everything matched. Five things produce that, and each leaves a different fingerprint in the export.

1. The feed imported something that had already been keyed by hand

Somebody entered the supplier payment on the day it went out. Two days later the feed brought the same payment across and it was added rather than matched to the existing one. The ledger now holds it twice; the bank holds it once. In the export you are looking for two ledger rows with the same amount, dates a day or two apart, and descriptions that look nothing like each other — because one was written by a person and one by the bank. This is the commonest single cause in a fed ledger, and it gets its own section below.

2. A feed line was excluded rather than matched

In QuickBooks Online, excluding a downloaded transaction moves it to the Excluded tab, where it is not added to the books and will not be downloaded again. That is the right thing to do with a genuine feed duplicate and the wrong thing to do with a transaction nobody could identify at half past four last March. The fingerprint is distinctive: the line is on the bank statement, and there is no ledger row for it at any date and no amount near it either. You will never find it by searching the ledger, because it is not in the ledger — you have to open the excluded list in the system and read it. A line excluded by mistake can be sent back with Undo on that tab.

3. A prior period was changed after it was reconciled

A transaction dated inside a period you have already signed off gets added, edited, voided or deleted afterwards. Every line in this month matches perfectly and the rec is still out, because the figure you started from has moved underneath you. The fingerprint: a difference that is constant, corresponds to nothing in this month's lines, and was not there last month. This one also has its own section below, because proving it needs a different test.

4. The bank re-posted a line under a different date

A payment is reversed and re-presented, or a card transaction authorises on one date and settles on another. The amount is identical and the date is not, so a strict amount-and-date match fails while an amount-only match happily pairs the row with the wrong partner. A date window handles most of this. A reversal-and-re-presentation pair needs a person, because the bank is showing three lines where the ledger holds one.

5. A transfer between two fed accounts was recorded from both ends

Money moves from the current account to the deposit account. Both accounts have a feed, so the same transfer appears in both, and if it was entered from each side rather than matched as a transfer there are now two transactions for one movement. The fingerprint is a pair of equal and opposite amounts on the same day with no customer or supplier attached to either.

The two-column proof still applies — even with nothing outstanding

A bank reconciliation adjusts both balances independently until they meet, rather than forcing one to the other. That is still the proof, and it is still what the Reconcile screen is doing behind the scenes. A feed does not remove any of it; it just leaves fewer lines to put on each side.

Bank side — start from the bank statement's ending balance:

• Add: money you have recorded that the bank has not yet processed. Classically a deposit in transit; with a feed, usually a transaction the feed has not brought across yet.
• Subtract: payments you have recorded that have not yet cleared. Classically an outstanding cheque; with a feed, usually something posted after the statement cut-off.
• Add: bank errors that understated the account (the standard OpenStax example: +320).

Book side — start from your own cash or general ledger balance: add income the books never recorded (bank interest), subtract expenses the books never recorded (account charges), subtract NSF or dishonoured cheques, and add or subtract your own recording errors (the same example deducts 240 here).

The rule that keeps you straight: items the bank already knows about but your books do not go on the book side. Charges, interest earned and bounced cheques are all book-side. Only the timing items adjust the bank statement side — the things you know about and the bank does not yet.

You are finished when the two adjusted figures agree: "after the bank reconciliation is prepared accurately, both the bank balance and the company balance will be the same amount." The template's DIFFERENCE row is that test, automated.

Which items need a journal entry

Only the book-side ones: "the transactions that the bank is aware of but the company is not must be journalized in the entity's records." Deposits in transit and outstanding cheques generate no journal entry at all — the timing simply has not caught up. That is the commonest mistake in a manual rec: "correcting" an outstanding cheque with a journal creates a real error where there was none.

The feed-era version of the same mistake: a duplicate is deleted or voided, not journalled away. Posting a correcting journal on top of a duplicated payment leaves you with three transactions where there should be one — the original, the duplicate, and a journal that reverses one of them. The balance is right and the supplier account, the VAT return and the expense analysis are all now wrong.

How the Matched? formula works

The template's matching flag is a COUNTIFS. Microsoft's documented syntax is:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

criteria_range1 and criteria1 are required; further range/criteria pairs are optional, up to a documented 127 pairs, and they need not be adjacent. The logic is AND across pairs — "if all of the first cells meet their associated criteria, the count increases by 1" — and it returns a count, not a value, which is exactly what a yes/no flag needs.

The Bank statement sheet's Matched? column (D2 filled down to D201) asks whether the amount appears anywhere in the Ledger's Amount column; the Ledger runs the mirror image:

=IF(C2="","",IF(COUNTIFS(Ledger!$C:$C,C2)>0,"matched","UNMATCHED"))
=IF(C2="","",IF(COUNTIFS('Bank statement'!$C:$C,C2)>0,"matched","UNMATCHED"))

Three things to notice. The outer IF(C2="","", …) leaves unused rows blank instead of stamping 200 rows with UNMATCHED before you paste anything. The sheet name sits in single quotes — Excel requires them whenever a sheet name contains a space. And the whole-column $C:$C means pasting past row 201 only needs the formula filled down.

Add the date window — feeds post one to three days late

Amount alone will happily match a 500 payment in January to a 500 payment in March. If both exports carry dates, add a date pair, keeping the blank guard:

=IF(C2="","",IF(COUNTIFS(Ledger!$A:$A,A2,Ledger!$C:$C,C2)>0,"matched","UNMATCHED"))

Against a feed, an exact date pair is usually too strict to be useful. The two sides rarely carry the same date: the feed stamps the bank's posting date, while the ledger holds the date the transaction was entered or dated by whoever raised it. A window is therefore the normal setting here rather than a refinement. Use the operator-and-ampersand pattern from Microsoft's documentation (=COUNTIFS(A2:A7,"<" & A6,B2:B7,"<" & B4)):

=IF(C2="","",IF(COUNTIFS(Ledger!$A:$A,">=" & A2-3,Ledger!$A:$A,"<=" & A2+3,Ledger!$C:$C,C2)>0,"matched","UNMATCHED"))

Three details matter. The Ledger date range appears twice, once per criterion — that is required: COUNTIFS(range, criteria1, criteria2) is not valid syntax. Every range must have the same rows and columns as the first, which is why they all stay whole-column once one of them is. And both sheets need real dates in column A; a blank date makes A2-3 evaluate as −3 and the row reads UNMATCHED even when the amount exists.

Three days either side is a sensible starting point for a feed. Widen it if your bank is consistently slower, but be aware of the trade: every extra day of tolerance pulls more candidates into range, and past about a week you start generating false matches that you then have to unpick by hand. A rec that matched everything on a fourteen-day window has told you nothing.

If you would rather match on one key than on two criteria pairs, combine the date and amount columns into a single text key on both sheets and point a one-pair COUNTIFS at that instead.

Two documented quirks if you rewrite the formula: wildcards work in criteria (? = any single character, * = any sequence, tilde ~ escapes a literal one), and a criteria argument pointing at an empty cell is treated as 0 — the reason for the blank guard. COUNTIFS works in Excel 2016 and later, including Microsoft 365 and Excel for the web.

Finding the duplicate the feed created

The Matched? column compares one sheet against the other. It cannot see a transaction that appears twice on the same sheet, which is exactly what a feed duplicate is — and both copies will read matched, because there is a partner on the bank side for one of them. A second column, pointed at the ledger's own amounts, finds them:

=IF(C2="","",IF(COUNTIFS(Ledger!$C:$C,C2)>1,"CHECK",""))

Put that on the Ledger sheet in a spare column and it stamps CHECK against every amount that appears more than once in the ledger. Most of them will be innocent — a standing order of the same amount each month, four identical subscription charges. You are reading the list, not acting on it.

The tell that makes it worth reading: when the whole difference equals exactly one transaction on the sheet, you are looking at a duplicate or an omission rather than an error. An error of transcription almost never produces a difference that matches a real transaction to the penny. So take the difference, search both amount columns for it, and start there. If the amount appears twice in the ledger and once on the statement, you have found it.

The harder variant is the pair that does not share a date. One copy came from the feed and carries the bank's posting date and the bank's description; the other was typed and carries the invoice date and the supplier's name. Same amount, dates a few days apart, descriptions with nothing in common. The amount-only COUNTIFS above still catches it, which is why this check deliberately does not include a date criterion. Sort the ledger by amount and the pairs sit next to each other; the full method is in removing duplicates in Excel, and matching the descriptions rather than the amounts is in fuzzy matching in Excel.

Run the same formula on the Bank statement sheet as well, pointed at 'Bank statement'!$C:$C. A bank export downloaded twice across overlapping date ranges duplicates the statement side instead, and it produces an identical-looking difference from the opposite direction.

When the opening balance is the problem, not the month

Every line matches. Both sheets are clean. The difference will not move. At that point stop looking at this month, because the problem is almost certainly that the figure you started from is no longer the figure you finished on last month.

It happens because someone edited, voided or deleted a transaction dated inside a period that had already been reconciled. Nothing about this month changed; the history did. You can prove it from the exports alone: filter the ledger export to the transactions marked cleared or reconciled, sum the amount column, and compare the result to the closing figure on the prior period's statement. If those two disagree, the month in front of you is innocent.

Both of the big systems will then tell you what moved.

• QuickBooks Online has a Reconciliation Discrepancy report, reached from the reconciliation history, which lists the transactions that were changed since a past reconciliation, what changed and the effect on the balance. The figure to drive to zero is the total discrepancy; once it reads nil you can reconcile normally even though the historical entries are still listed. If the report does not account for the whole difference, the Audit log filtered to that account and to the period since the last completed reconciliation will show deletions and voids the report misses.
• Xero has the Bank Reconciliation Summary, which sets out three figures as at the report date: Balance in Xero (everything entered in Xero for that account), Statement balance calculated from the feed, and a bank statement balance that you type in from your online banking. When the reconciliation is genuinely up to date all three agree. Which pair disagrees tells you where to look: Xero against the calculated statement balance points at transactions that were never reconciled, and the calculated balance against your actual bank balance points at missing, duplicated or deleted statement lines.

The remedy afterwards is a lock date, so that the closed period cannot move again. Every one of the five systems has one, and they behave differently — the month-end close checklist covers each of them and what the client can still change after you have locked. Note too that some systems make this irreversible in the other direction: Zoho Books warns that "the opening balance cannot be edited once the transactions have been reconciled."

Working the unmatched items

Filter both sheets to UNMATCHED and triage each line into five buckets — four you can see in the filter, and one you cannot. Cleared lines are easier to keep track of if you cross them off as you work — strikethrough in Excel covers Ctrl+5 and a conditional-formatting rule that strikes reconciled rows automatically.

1. On the bank, not in the ledger. Charges, interest, a forgotten direct debit, a returned cheque. Book-side adjustments, each needing a journal entry.
2. In the ledger, not on the bank. Outstanding cheques and deposits in transit. Bank-side adjustments, no journal entry.
3. On both sides but flagged unmatched. Nearly always an amount discrepancy or a sign problem — see troubleshooting below.
4. Genuinely wrong. A duplicate posting, a transaction belonging to another account, fraud. Investigate before adjusting.
5. On both sides, twice. The feed duplicate. It reads matched on both sheets and therefore never appears in this filter at all — it is the one bucket you have to go looking for, using the >1 check above rather than the UNMATCHED list.

Enter bucket-1 totals into the book side of the Reconciliation sheet ("bank credits not recorded", "bank charges / fees not recorded") and bucket-2 totals into the bank side ("deposits in transit", "outstanding cheques"), then read the DIFFERENCE row. Only the shaded input cells need typing; the balances and DIFFERENCE are formulas.

The standard adjusting journals

Every book-side line becomes a journal. The three most common, in textbook wording:

Interest credited by the bank runs the other way: debit Cash, credit Interest Income.

If you reconcile inside an accounting system instead, the same completion test applies. Zoho Books, for example, puts it at Banking > select the account > Gear icon (top right) > Reconcile Account, and tells you to "make sure that the Closing Balance and Cleared Amount are the same and the difference is zero". One warning from the same help page, which is the reason the opening-balance section above matters so much: "The opening balance cannot be edited once the transactions have been reconciled."

When it will not balance: the order that finds it fastest

Work these in sequence rather than re-reading the whole sheet. The order below is arranged for a ledger with a feed running, which is not the order a textbook gives — the tests that assume a lot of hand-typed figures have moved to the bottom, because a feed types very few.

1. Does the difference equal exactly one transaction?

Take the difference and search both amount columns for it. A difference that matches a real transaction to the penny is a duplicate or an omission, not an error of transcription — and in a fed ledger it is the likeliest single answer. If the amount appears twice on one side and once on the other, you have it. If it appears on the bank statement and nowhere in the ledger, go and look at the excluded list before assuming it is missing. Full method: removing duplicates in Excel.

2. Check the opening balance

If last month's closing balance was never rolled forward, or someone edited a prior-period transaction after sign-off, every line can match and the totals still be out by a constant. Compare this month's opening figure to last month's reconciled closing figure, then use the system's own discrepancy report as set out above.

3. Divide the difference by 2

This catches an amount posted on the wrong side: 34,100 − 33,900 = 200; 200 ÷ 2 = 100; dividends of 100 posted as a credit instead of a debit. In a fed rec it usually surfaces as a sign convention problem rather than a posting error — a bank export where outflows arrive negative while the ledger stores them positive, or a receipt that came across the feed as a payment. If the conventions disagree, multiply one Amount column by −1 in a helper column and match against that.

4. Divide the difference by 9

A whole-number result is the classic sign of a transposition error — two digits swapped. The standard worked example: 35,800 − 34,000 = 1,800; 1,800 ÷ 9 = 200; equipment entered as 5,300 when it should have been 3,500. Search for a figure whose digits, reordered, give the right value. Treat it as a strong hint, not proof — the source says only that you "may have transposed a figure", a shifted decimal (100 versus 1,000) divides by 9 too, and differences can be divisible by 9 by coincidence. This test earns its keep on a hand-keyed ledger and rather less on a fed one, which is why it now sits fourth: a feed does not fat-finger an amount.

5. If none of the tests fire

The textbook's honest answer: "you will have to go back through one step at a time until you find the error." Halve the problem — reconcile the first fortnight, then the second. And balancing is not proof of correctness: "we can have errors and still be mathematically in balance." Two compensating errors reconcile perfectly.

Where this template stops

It matches on amount only (plus date if you add the second pair), so it cannot handle:

• One deposit covering several invoices. A 4,800 credit settling invoices of 1,500, 1,300 and 2,000 reads UNMATCHED on both sides forever — no single ledger line equals 4,800.
• Descriptions that differ. CARD PMT ACME TRADING L.L.C 4471 versus Acme Trading LLC gives the formula nothing to use (fuzzy matching in Excel is the formula workaround).
• Duplicate amounts. Two identical 500 payments both flag matched even if only one exists on the other side — COUNTIFS counts occurrences rather than pairing rows one-to-one, so check repeated amounts by eye.
• A PDF-only statement. Get the data into cells first: converting a bank statement PDF to Excel.
• Anything the ledger export leaves out. The sheet can only see what you paste into it, so a feed line that was excluded rather than posted is invisible to it — it is not in the ledger export, by definition. The template will show you that the bank line has no partner; it cannot tell you the reason is an exclusion. For that you have to open the excluded list in the accounting system and read it yourself.

For a few hundred lines a month those limits are manageable. Past a few thousand, manual triage stops being viable.

One option for what the template cannot reach

That is the gap HISAB 360 is built for: a paid Excel add-in with an AI chat panel docked in the workbook. For the scenario on this page it matches statement lines to ledger rows on amount plus a date tolerance, handles many-to-one deposits, matches descriptions fuzzily, flags duplicate amounts and writes a reconciliation report into the sheet. It also reads the ledger side straight from QuickBooks Online, Xero, Zoho Books, Odoo or FreshBooks, so you are pulling one export rather than two. Pricing: $7/month or $60/year if you bring your own AI key, $10 or $20/month for HISAB's own AI, or $199 once for a perpetual licence.

The honest limits: Windows desktop Excel only (365 or 2019+) — not Mac, not Excel on the web. Paid after a 15-day free trial of every feature plus 50 AI credits, no card, once per machine; there is no permanently free tier. Connecting an accounting system means authorising it yourself over OAuth. And any write back to a ledger should be reviewed before approval, then reconciled afterwards — an AI match is a proposal, not an audit conclusion.

If your reconciliations fit inside the free template, use the free template. Keep a copy per bank account and it carries you through most months for nothing.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo and FreshBooks. The 15-day trial is the full product, no card required.