How to reconcile data in Excel

The four steps of every reconciliation

1. Normalise both files: same date format, amounts as numbers with the same sign convention, IDs trimmed and in one case.
2. Match rows: on a shared ID where one exists, otherwise on a combination such as date + amount, allowing a tolerance for timing.
3. List the unmatched rows on each side. These are the reconciling items.
4. Explain each item — a timing difference, a fee, a refund, a duplicate or a genuine error — and record the explanation next to it.

A reconciliation is finished when both sides agree after the listed items are accounted for: the bank balance, adjusted for items not yet in the ledger and items not yet in the bank, equals the ledger balance. The worked example below shows the arithmetic with real figures.

Step 1 in detail: normalising both files

Most failed matches are not real differences. They are the same transaction written two ways. Before matching anything, add helper columns to each file rather than editing the original values, so you can always see what came from the source system.

Pick one sign convention for the whole reconciliation, usually money in positive, money out negative, and convert both files to it. A bank statement normally already follows that rule. In the ledger's bank account, receipts are debits and payments are credits, so *debit − credit* gives the same signs as the bank. Get this wrong and every payment looks unmatched, because −620 and 620 never meet.

Text dates are the most common silent failure. If =ISNUMBER(A2) returns FALSE for a date cell, Excel is treating it as text and any date-window match will miss it.

Choosing the matching key

Matching with formulas

When both sides share an ID, a status column next to each list is enough:

=IF(COUNTIFS(Bank[Reference], [@Reference]) = 0, "Not in bank", "Matched")

Without an ID, match on amount and a date window. This counts bank rows with the same amount within three days of the ledger date:

=COUNTIFS(Bank[Amount], [@Amount], Bank[Date], ">=" & [@Date] - 3, Bank[Date], "<=" & [@Date] + 3)

A result of 1 is a clean match, 0 is a reconciling item, and 2 or more needs a manual look because several bank rows fit.

To see *which* bank row matched rather than just how many, return its reference with FILTER (Excel 365, Excel 2021 and Google Sheets):

=IFERROR(TEXTJOIN(", ", TRUE, FILTER(Bank[Reference], (Bank[Amount] = [@Amount]) * (ABS(Bank[Date] - [@Date]) <= 3))), "No match")

Excel 2019 has TEXTJOIN but not FILTER (FILTER needs Excel 2021 or Microsoft 365), so in Excel 2019 and earlier stay with COUNTIFS for the status and use a filtered view to inspect the candidates.

Duplicates: matching each row only once

COUNTIFS answers “is there at least one match?”. It cannot tell you that two identical £49.99 subscription charges in the bank should pair with two ledger entries, not one. If the ledger has only one, COUNTIFS still says “Matched” for both bank rows and the missing entry stays hidden.

The fix is an occurrence number: the first £49.99 on a side becomes 49.99|1, the second 49.99|2. Add this to each list (data starting in row 2, amounts in column C):

=ROUND(C2, 2) & "|" & COUNTIFS($C$2:C2, C2)

The range $C$2:C2 grows as the formula is copied down, so each row counts how many times its amount has appeared so far. Then match the two lists on this key with COUNTIF(OtherSide!F:F, F2). Two bank charges and one ledger entry now give one match and one reconciling item, which is the right answer. Sort both lists by date first so the first occurrence pairs with the first occurrence.

One-to-many matches: payouts against orders

Payment processors and marketplaces pay out in batches, so one bank deposit covers many orders, minus fees. Matching row to row fails; instead, total the orders per payout and compare that to the deposit.

=SUMIFS(Orders[Gross], Orders[Payout ID], [@[Payout ID]]) - SUMIFS(Orders[Fee], Orders[Payout ID], [@[Payout ID]])

When the difference is not zero, look for a refund or chargeback deducted from the same payout, or an order assigned to the next payout. The specific walkthroughs for Shopify orders and Stripe payouts, PayPal and the bank and Square sales and bank deposits cover where each provider's export shows fees and payout IDs; the exact column names vary by provider and export version.

Allowing a tolerance on amounts

Some differences are expected and small: currency conversion, rounding on VAT lines, a bank charge on an international transfer. Rather than treating them as unmatched, match within a tolerance and report the difference separately.

=COUNTIFS(Bank[Reference], [@Reference], Bank[Amount], ">=" & [@Amount] - 0.05, Bank[Amount], "<=" & [@Amount] + 0.05)

Keep the tolerance as small as the data allows and write it down. A 5p tolerance on rounding is defensible; a £10 tolerance hides real errors. Add a second column with the actual difference, so a reviewer can see every amount that was accepted as “close enough”.

Matching with Power Query

For reconciliations you repeat every month, load both files into Power Query and use Merge Queries three times: a left anti join for items only in the first file, a right anti join for items only in the second, and an inner join to compare amounts on matched rows. Refresh next month. See Power Query's join kinds.

1. Load each file with Data → Get Data → From File → From Workbook (or From Text/CSV) and choose Transform Data.
2. In each query, apply the clean-up steps: trim and upper-case the key (Transform → Format → Trim, then UPPERCASE), set the amount to Decimal Number, set the date to Date, and round the amount with Transform → Rounding → Round.
3. Close & Load both as Connection Only.
4. Choose Data → Get Data → Combine Queries → Merge. Pick the ledger on top and the bank below, click the key column in each (hold Ctrl to select several columns, such as date and amount), and choose Left Anti. The result is the ledger-only list.
5. Repeat with Right Anti for the bank-only list, and with Inner to put matched rows side by side.
6. On the inner result, add a custom column [Amount] - [Bank.Amount] and filter to non-zero values to find matched rows whose amounts differ.

Merge Queries matches exact values, so it cannot apply a ±3-day window on its own. For bank data without references, either match on amount only and filter the merged rows by date difference afterwards, or use the formula approach above. Fuzzy merge helps with misspelt names and references, not with dates or amounts.

Matching in Google Sheets

COUNTIFS, XLOOKUP and FILTER work in Google Sheets with the same logic, so the status formulas above carry over almost unchanged; use ordinary ranges such as Bank!C:C instead of Excel table names. Google Sheets has no Power Query, so for a repeatable process keep each month's exports in their own tabs (or pull them in with IMPORTRANGE) and let the formulas recalculate. A compact unmatched list for the ledger, with the bank amounts in column C:

=FILTER(Ledger!A2:D, Ledger!C2:C <> "", COUNTIF(Bank!C2:C, Ledger!C2:C) = 0)

That version matches on amount only; add the date window with a COUNTIFS inside MAP or a helper column if equal amounts are common in your data.

Worked example: bank statement vs ledger

Three reconciling items, each with a reason. Everything else matched one-to-one.

Here are the full September movements behind that table. Both sides opened the month at 5,000.00, already agreed in August.

The same matching rule in SQL (DuckDB syntax), with amount equal and dates within three days. Run on the data above, it returns exactly the three reconciling items:

SELECT 'Ledger only' AS side, l.date, l.description, l.amount
FROM ledger l
WHERE NOT EXISTS (
  SELECT 1 FROM bank b
  WHERE b.amount = l.amount
    AND b.date BETWEEN l.date - INTERVAL 3 DAY AND l.date + INTERVAL 3 DAY
)
UNION ALL
SELECT 'Bank only', b.date, b.description, b.amount
FROM bank b
WHERE NOT EXISTS (
  SELECT 1 FROM ledger l
  WHERE l.amount = b.amount
    AND l.date BETWEEN b.date - INTERVAL 3 DAY AND b.date + INTERVAL 3 DAY
)
ORDER BY side, date;

Now prove the balances agree once the items are accounted for:

Both adjusted balances are 4,145.30, so the reconciliation is complete. Posting the 14.20 fee brings the ledger to 4,145.30; the cheque and the deposit should appear on October's statement and clear next month. For the full month-end routine see reconciling a bank statement with the general ledger.

Common reasons the two sides differ

• Timing: cheques not yet cleared, deposits in transit, payouts that land a few days after the sale.
• Fees and deductions: processors pay out net of fees, so the payout is less than the sum of orders.
• Partial and combined payments: one payment for several invoices, or several payments for one.
• Refunds and chargebacks recorded in one system but not the other.
• Duplicates and typos: an entry posted twice, or a transposed amount (54 vs 45 — differences divisible by 9 are a classic sign).

Two quick tests narrow down an unexplained difference. If the gap is exactly twice an amount on the list, that item was probably entered with the wrong sign. If the gap is divisible by 9, look for two digits swapped. If the gap equals one item exactly, that item was missed on one side.

Troubleshooting a reconciliation that won't balance

Which method to use

Whatever you choose, keep the source exports unchanged in their own sheets and do all cleaning in helper columns or query steps. Auditors and colleagues need to trace each reconciling item back to the original line.

Repeating the reconciliation every month

1. Keep a workbook with fixed sheet names: Bank, Ledger, Matching, Items and Sign-off.
2. Each month, paste or load the new exports into Bank and Ledger with the same column layout (or point Power Query at a new file and refresh).
3. Before matching anything new, check last month's outstanding items: they should now appear in this month's data. Tick each one off.
4. Run the matching, copy the unmatched rows to Items and write an explanation and an owner for each.
5. Complete the balance proof on Sign-off and save a copy of the workbook as that month's record.

Items that roll forward for more than one month deserve attention: a cheque that hasn't cleared after several weeks may need cancelling, and a deposit that never arrives is a real loss rather than a timing difference.

How to check the result before sign-off

• Row counts: matched rows + unmatched rows on each side equal the row count of that file.
• Totals: the sum of matched amounts is the same on both sides (or differs only by items you have explained).
• Balance proof: adjusted bank balance equals adjusted ledger balance to the penny.
• Every item has a reason and, where an adjustment is needed, an entry has been posted or scheduled.
• Spot checks: pick three matched pairs at random and confirm they really are the same transaction.

Reconciliation guides by data pair

The same four steps apply everywhere, but each pair of files has its own key, its own timing gaps and its own traps:

• Payments and payouts: Stripe payments to invoices, Amazon FBA fees to the order report, delivery app payouts to till reports.
• Accounting: VAT return against the sales ledger, expense reports against card statements, three-way match of POs, receipts and invoices, budget vs actuals.
• Operations and people: payroll against timesheets, stock count against inventory records, WooCommerce orders against carrier invoices, tenant list against rent payments.
• Finding problems directly: vendors billed twice, mismatched totals between two reports and gaps in invoice numbers.

For side-by-side comparison of two versions of the same sheet, rather than two records of the same activity, see comparing spreadsheets.

Analistable can load both files and answer “which ledger entries have no matching bank transaction within 3 days?” in one question, running the SQL in your browser.

Every guide in this topic

• How to compare budget vs actuals in Excel

  Compare budget with actuals in Excel across files: map accounts, calculate variance and variance %, and flag favourable and adverse lines.

• How to do a bank reconciliation in Excel

  Do a bank reconciliation in Excel: match the bank statement to the cash ledger, list outstanding cheques and deposits in transit, and prove the balances agree.

• How to do a three-way match in Excel

  Three-way match in Excel: compare purchase orders, goods received notes and supplier invoices line by line, and flag quantity and price differences.

• How to match a membership list to payment records

  Find members who haven't paid, payments from non-members and failed direct debits by matching a membership list to payment exports in Excel.

• How to match Amazon FBA fees to your orders

  Turn Amazon's settlement report into one row per order — item price, referral fee, FBA fee, refunds — and match it to your order report to check every fee.

• How to match donations to Gift Aid declarations

  Check which donations can be included in a Gift Aid claim: match donations to declarations, apply date rules, calculate the claim and find lapsed donors.

• How to match Stripe payments to accounting invoices

  Match Stripe payments to invoices exported from QuickBooks or Xero: use the invoice number, handle partial and combined payments, and list unpaid invoices.

• How to reconcile a rent roll with rent payments

  Match rent received in the bank to the rent roll: identify payers by reference, handle partial and combined payments, and produce an arrears list.

• How to reconcile a stock count with inventory records

  Compare a physical stock count with system inventory by SKU and location: variances in units and value, items not counted, and consolidating two warehouses.

• How to reconcile a VAT return with your sales ledger

  Check a UK VAT return against the sales ledger: rebuild Box 1 and Box 6 from invoices, split by VAT rate, and explain differences from credit notes and timing.

• How to reconcile delivery app payouts with your till

  Match Uber Eats, Deliveroo and Just Eat payouts to orders in your till: commission, adjustments, refunds and missing orders, with formulas and a worked example.

• How to reconcile expense claims with card statements

  Match expense claims to company card transactions (Revolut, Amex, bank cards): amount and date matching, foreign currency, missing receipts, duplicates.

• How to reconcile PayPal transactions with your bank statement

  Reconcile PayPal activity with your bank: separate sales, fees and transfers, match withdrawals to bank deposits, and handle holds and currency conversions.

• How to reconcile payroll with timesheets

  Check payroll against approved timesheets and the rota: match employees and periods, compare hours and overtime, and find shifts without clock-ins.

• How to reconcile shipping carrier invoices with your orders

  Check carrier invoices against your shop orders by tracking number: weight adjustments, surcharges, duplicate charges and shipping you charged customers.

• How to reconcile shop orders with Stripe payouts

  Match Shopify or WooCommerce orders to Stripe payouts: link orders to charges, group them by payout, account for fees and refunds, find what's missing.

• How to reconcile Square sales with bank deposits

  Match Square card sales to the deposits in your bank account: group sales by deposit, account for processing fees and refunds, and spot missing deposits.

Frequently asked questions

How do I reconcile two spreadsheets in Excel?

Clean the key columns, match rows with COUNTIFS or XLOOKUP (or Power Query merges), list the rows that only appear on one side, and explain each difference.

How do I reconcile when there's no common ID?

Match on a combination of fields such as date and amount, with a small date window for timing differences, and review any row that matches more than once.

What is a reconciling item?

A row that appears on one side but not the other, or with a different amount, along with its explanation — for example a cheque that hasn't cleared.

Can Excel reconcile automatically every month?

Yes. Build the matching in Power Query with anti and inner joins, then refresh it with next month's files.

How do I stop one transaction matching two rows?

Give each row an occurrence number within its amount (49.99|1, 49.99|2) with a running COUNTIFS, and match on that key so each row pairs with at most one row on the other side.

How do I reconcile a payout that covers many orders?

Total the orders and fees for each payout ID with SUMIFS, then compare that expected payout to the bank deposit instead of matching individual orders.