Bank Statement vs General Ledger Reconciliation in Excel/CSV

Bank statement vs general ledger reconciliation sounds simple: prove that cash in the bank matches cash in the books.

In practice, it is usually a messy two-file matching job:

• Bank statement CSV from the bank portal
• General ledger export from QuickBooks, Xero, NetSuite, Sage, or another system
• Different date formats
• Bank fees that appear separately
• Deposits that group multiple invoices
• Checks, card settlements, refunds, chargebacks, and timing differences
• Transactions that are present in one file but missing in the other

Many teams still solve this with VLOOKUP, XLOOKUP, SUMIFS, helper columns, filters, copied formulas, and manual highlighting. That can work for small files, but it becomes fragile once the row count grows or the bank groups transactions differently than the ledger.

FireLookup gives finance teams a guided way to compare Excel and CSV files, find matched and unmatched rows, and export a review-ready reconciliation result without rebuilding formulas every month.

What bank-to-ledger reconciliation is trying to prove

A bank-to-ledger reconciliation answers four questions:

1. Which bank transactions are already recorded in the ledger?
2. Which ledger transactions have cleared the bank?
3. Which transactions are missing, duplicated, delayed, or misclassified?
4. What exceptions need review before month-end close?

The output should not just be a green checkmark. A useful reconciliation should produce matched transactions, bank-only transactions, ledger-only transactions, possible fuzzy matches, grouped matches where one bank deposit equals several ledger rows, and a repeatable review trail.

The common Excel workflow

A typical Excel reconciliation process looks like this:

1. Export the bank statement as CSV or Excel.
2. Export the general ledger cash account for the same period.
3. Clean headers, dates, signs, currency symbols, and blank rows.
4. Add helper columns for normalized amount, date, reference, or memo text.
5. Use XLOOKUP or VLOOKUP to match IDs or amounts.
6. Use SUMIFS to compare totals by date, customer, deposit batch, or reference.
7. Filter #N/A, zero differences, duplicate amounts, and unmatched rows.
8. Manually investigate exceptions.
9. Copy the output into a review workbook.

This is familiar, but it has weak spots.

| Issue | Why it causes problems |
|---|---|
| Different transaction descriptions | Bank memos rarely match ledger descriptions exactly. |
| One-to-many deposits | One bank deposit may represent multiple invoices, Shopify payouts, Stripe batches, or checks. |
| Duplicate amounts | Matching on amount alone creates false positives. |
| Date timing differences | A ledger date and cleared bank date may be a few days apart. |
| Formula drift | Copied formulas, helper columns, and hidden filters can change between workbook versions. |
| Review trail | It is hard to prove which rule matched which transaction. |

For a formula-based tutorial, see FireLookup's guide to bank reconciliation in Excel with XLOOKUP and SUMIFS. If your files are recurring or exception-heavy, a dedicated reconciliation workflow is usually safer.

A cleaner reconciliation workflow

1. Prepare the bank statement file

Export the bank statement for the period you want to reconcile. Keep transaction date, posted date, description, reference/check number, debit, credit, net amount, and balance if available. Do not overwrite the original export.

2. Prepare the general ledger file

Export the cash account or bank account register for the same period. Keep transaction date, document/reference number, name/customer/vendor, memo, debit, credit, net amount, and account. If debits and credits are separate, create a normalized amount column in a copy of the file.

3. Match obvious exact transactions first

Start with high-confidence matches: exact amount plus reference, check number, transaction date, or batch/deposit ID. Exact matching removes the easy rows so review time goes to exceptions.

4. Use fuzzy matching for description differences

Bank descriptions often contain processor names, truncated references, or extra text. Ledger descriptions may include customer names or invoice numbers. Fuzzy matching can surface likely matches when descriptions are close but not identical.

5. Handle grouped deposits and one-to-many matches

This is where many Excel reconciliations become painful. One bank deposit may equal several customer payments, a Shopify/Stripe payout may include fees and refunds, and a marketplace settlement may contain dozens of order-level rows. In these cases, one-to-one matching is not enough.

FireLookup supports grouped matching so you can test combinations and export the grouped result for review. See the FireLookup use case page for bank statement to ledger reconciliation.

What to review after matching

Bank-only transactions

These appear in the bank file but not the ledger. Common causes include bank fees, interest income, automatic payments, unrecorded deposits, duplicate bank exports, fraud, or unexpected charges.

Ledger-only transactions

These appear in the ledger but not the bank file. Common causes include outstanding checks, deposits in transit, future-dated transactions, reversed or voided entries, or transactions posted to the wrong bank account.

Possible matches

These are near matches that need judgment: same amount with date off by a few days, similar memo text with no shared reference, same check number with different sign, or grouped totals that require supporting detail.

Try a sample reconciliation output

How FireLookup helps

FireLookup is designed for people who already have Excel and CSV exports and need a faster way to compare them.

Instead of building a new workbook each month, you can:

1. Upload the bank statement file and the general ledger export.
2. Choose the columns that should match.
3. Run exact, fuzzy, or grouped matching.
4. Review matched and unmatched rows.
5. Export the reconciliation result.

This is useful when you are tired of VLOOKUP/XLOOKUP chains, SUMIFS exception checks, helper columns, Power Query steps only one person understands, macros that break when headers change, or manual highlighting.

FireLookup does not replace accounting judgment. It helps you get to the review stage faster with cleaner matched and unmatched outputs.

Bank-to-ledger reconciliation checklist

Before you call the reconciliation complete, confirm:

• The bank statement period matches the ledger period.
• Opening and ending balances were checked separately.
• Debits and credits use the same sign convention in both files.
• Duplicate amounts were not blindly auto-matched.
• Timing differences are documented.
• Grouped deposits have supporting detail.
• Bank-only and ledger-only exceptions were reviewed.
• The final matched/unmatched output was saved for audit support.

For a broader step-by-step workflow, read how to reconcile Excel and CSV files.

FAQ

Can I reconcile a bank statement to a general ledger in Excel?

Yes. Many teams use Excel formulas such as XLOOKUP, VLOOKUP, COUNTIFS, and SUMIFS to match bank transactions to ledger rows. This works best when files are small and matching rules are simple.

What columns should I match on?

Start with amount plus a stronger identifier such as reference number, check number, payment ID, batch ID, or invoice number. If those are unavailable, use amount plus date and description. Avoid matching on amount alone unless the result is manually reviewed.

How do I handle one bank deposit that equals multiple ledger transactions?

Treat it as a grouped or one-to-many match. The combined ledger rows should tie to the single bank deposit amount, with enough supporting detail for review. FireLookup supports grouped matching for this type of reconciliation.

What should unmatched rows mean?

Unmatched rows are not automatically errors. They may be timing differences, outstanding checks, deposits in transit, fees, interest, duplicate exports, or transactions posted to the wrong account.

Does FireLookup connect to my bank or accounting system?

FireLookup works with files you export, such as Excel and CSV. Upload your bank statement export and ledger export, configure matching rules, and export the result.

Start with your next reconciliation file

If you already have a bank statement CSV and a ledger export, you can test the workflow without changing your accounting system.