Transaction validation compares an export with its source statement. Bank reconciliation compares the bank's records with your independently maintained books. A zero running-balance difference helps with the first task; it cannot complete the second.
1. Prepare the statement and two separate records
Choose one account, one currency and a statement cutoff date. Keep the original statement, its opening and closing balances, all transaction pages, your cash book or ledger through that date, and the previous reconciliation's outstanding items. A copy of the bank export is not an independent ledger.
If a usable bank CSV or Excel download already exists, start there. For a supported PDF, PNG or JPG, use the statement extractor and export Excel or CSV. BankStatementMate supports clear English tables with separate Date, Description, Debit, Credit and Balance columns, up to 10 MB and five PDF pages. Scans use browser-side OCR and need careful source review. Follow the PDF-to-Excel guide for conversion instructions.
Save an untouched export as your reference. In a working workbook, use a Statement sheet for reviewed bank rows and a separate Books sheet for ledger records. Keep the original five fields in columns A:E. Add checks to the right; do not replace the extracted Balance values with calculated ones. The formulas below are additions you make in Excel, not fields generated by BankStatementMate.
2. Establish order, direction and numeric types
The example assumes a deposit account: positive Credit amounts increase its balance; positive Debit amounts decrease it. It does not cover a credit-card liability convention. Check the statement's headings and a known transaction before applying this convention. Do not subtract a debit that is already stored as negative without adapting the formula.
Use oldest-to-newest posting order, retaining the bank's sequence for transactions on the same date. For a newest-first statement, work in a separate copy and reverse the complete row sequence only if that reproduces the bank's actual posting order. Sorting by date alone can reorder same-day entries. Keep a source row/page reference outside A:E so you can return to the original.
Excel exports contain numeric amounts and text dates. CSV imports can turn amounts into text or interpret dates differently. Check decimal and thousands separators against the source. Formatting a text cell as Currency does not convert its underlying value. Microsoft documents that SUM ignores text values in referenced cells, so a plausible total can hide an ignored amount.
After confirming a blank Debit or Credit really means no movement on that side, put this optional type check in I2 and copy it down. I1 can be Numeric inputs?. TRUE means nonblank amounts are numeric and Balance is numeric; it does not validate their values.
=AND(OR(C2="",ISNUMBER(C2)),OR(D2="",ISNUMBER(D2)),ISNUMBER(E2))A FALSE result needs investigation before calculation. Use Excel's number conversion or import type settings after checking the source and locale. An unreadable or unexpectedly empty amount must remain unresolved, not silently become zero. The ISNUMBER documentation explains why numeric text fails this check.
3. Check every running balance in Excel
Row 1 contains headers: A Date, B Description, C Debit, D Credit, E Balance. The first transaction is row 2. Set F1 to Expected balance, G1 to Difference, H1 to Opening balance and H2 to the numeric opening balance read from the source. Check H2 with =ISNUMBER(H2) in an unused cell.
After the numeric and direction checks pass, enter these formulas. They use English Excel names and commas; a localized installation may use translated names or semicolons. Copy F2 and G2 down through every transaction row.
F2 — opening balance plus cumulative credits minus cumulative debits:
=ROUND($H$2+SUM($D$2:D2)-SUM($C$2:C2),2)G2 — statement balance minus expected balance:
=ROUND(E2-F2,2)The dollar signs keep the opening balance and start of each range fixed. In row 4, the ranges extend to D4 and C4. Blank amounts contribute zero only under the checked convention above. These formulas assume a currency recorded to two decimal places; ROUND controls decimal precision, not an acceptable error allowance. Investigate every nonzero difference.
4. Verify the fictional three-row example
This reuses our existing fictional workbook example and its digital PDF sample. It is not customer data. Set H2 to 1000.00. The dash in this table means a genuinely blank cell; do not type a dash into the worksheet. Scroll the table horizontally on a narrow screen to see the balance checks.
| Row / Date (A) | Description (B) | Debit (C) | Credit (D) | Balance (E) | Expected (F) | Difference (G) |
|---|---|---|---|---|---|---|
| 2 / 2026-09-01 | SYNTHETIC Salary | — | 2500.00 | 3500.00 | 3500.00 | 0.00 |
| 3 / 2026-09-02 | SYNTHETIC Rent | 1200.00 | — | 2300.00 | 2300.00 | 0.00 |
| 4 / 2026-09-03 | SYNTHETIC Coffee | 4.50 | — | 2295.50 | 2295.50 | 0.00 |
Independent arithmetic: 1000.00 + 2500.00 = 3500.00; 3500.00 − 1200.00 = 2300.00; 2300.00 − 4.50 = 2295.50. Total credits are 2500.00 and total debits are 1204.50. Compare the calculated final balance with the closing balance printed on the source, not only the copied last cell.
If Coffee is mistakenly entered as a 45.00 debit, the expected final balance becomes 2255.00 and G4 becomes +40.50 against the unchanged source balance. Inspect that row; do not edit Balance merely to force zero. This is an illustrative error scenario, not an additional extracted transaction.
5. Check completeness even when differences are zero
A matching final balance is necessary under these assumptions, but not sufficient evidence of correct data. A missing payment and an equal missing receipt can cancel out. Duplicate offsetting entries can also leave the final total unchanged. Even all running checks can pass if an omitted offsetting pair falls between two retained rows, or if dates or descriptions are wrong while amounts remain consistent.
- Compare every exported row with the original, including first and last transactions at page breaks. Exclude opening-balance labels and carried-forward summaries from transaction totals.
- Compare row counts and any bank-provided debit/credit totals where available. Check that every intended page was included.
- Flag repeated date, amount and description combinations for review. Repeated transactions can be legitimate; do not use Remove Duplicates without source evidence.
- When a difference begins, inspect that row and earlier rows for an omitted line, transposed digit or wrong debit/credit column. A wrong opening balance affects all later checks.
6. Match against the independent Books sheet
Import or copy your independently maintained ledger into Books for the same account and currency through the cutoff date. Include prior outstanding items still unresolved. Preserve its original references and use a consistent money-in/money-out convention; accounting debit/credit labels may be opposite to the bank's labels.
- Add a review status and a matched ledger reference beside each Statement row, and a corresponding bank row reference in Books.
- Compare amount, direction, reference and description, allowing for documented posting-date differences. A same-amount lookup alone is only a candidate match.
- Mark each confirmed one-to-one match on both sheets so a ledger entry is not reused. For one-to-many settlements, verify all component amounts and supporting records.
- Filter unmatched rows on both sides. Separate timing items from missing book entries, extraction mistakes and possible bank errors. Record the evidence and follow-up for each item.
For example, the fictional Coffee debit may be searched for as 4.50 money out in your own records. This sample does not supply an independent cash book, so it cannot demonstrate a completed reconciliation. Do not invent a matching ledger entry just because the statement contains the payment.
7. Explain differences and close the reconciliation
Outstanding payments and deposits in transit can cause timing differences. Bank charges, interest, returned payments or book errors may require supported ledger entries or corrections. Confirm the appropriate accounting treatment from the actual business records and accounting policies; do not infer a tax category from a bank description.
For a deposit account, a typical reconciliation summary starts with statement closing balance, adds documented deposits in transit and subtracts outstanding payments. Separately adjust the book closing balance for confirmed unrecorded receipts, charges and corrections. Bank errors require documented handling. Avoid counting an adjustment twice, and investigate any residual difference instead of adding an unexplained balancing entry.
Microsoft's bank reconciliation documentation describes comparing statement lines with ledger entries and resolving unmatched differences. Its software features are not BankStatementMate features. Retain your reviewed workbook, source statement, supporting records and outstanding-item list for the next period.
Limits and privacy
BankStatementMate performs supported statement extraction and Excel/CSV export. It does not provide automatic bank reconciliation, bookkeeping entries or a financial-result guarantee. You perform these spreadsheet checks after export. Unclear scans, missing pages, mixed currencies and unsupported layouts require further review or a clearer source.
The converter processes statement files in your browser. An exported workbook is a separate sensitive copy. If you upload it to cloud spreadsheets, accounting software or an external AI service, that service's access, retention and privacy conditions apply. Do not post real account data in public examples; share only the minimum needed with authorized reviewers.
Common questions
What formula checks a bank statement balance in Excel?
With positive debits in C, credits in D and an opening balance in H2, use =ROUND($H$2+SUM($D$2:D2)-SUM($C$2:C2),2) in F2 and copy down. Verify numeric inputs and chronological posting order first. This checks arithmetic, not independent book agreement.
Does a zero difference mean reconciliation is complete?
No. Check all source rows, match independent books, explain outstanding items and support any adjustments. Equal totals can hide omissions or offsetting errors.
Can I start from CSV instead of XLSX?
Yes. Import the CSV transaction export with explicit separators and number types, preserve the five fields, then add formulas in a workbook. Save the working file as XLSX to retain formulas and multiple sheets.