Bank Statement Spreadsheet Quality Checks

Design columns that preserve evidence

Retain posting date, any value date, verbatim description, debit, credit, running balance, page, and source sequence. Merchant normalization, category, transfer group, confidence, and reviewer notes should occupy separate analytical fields.

Reconcile row counts and control totals

Compare transaction counts for each page with the imported rows and total debits separately from credits. Reproduce the opening-to-closing change while accounting for fees, interest, reversals, or adjustments printed outside the main ledger.

Evaluate blanks according to the field. A missing running balance can reflect the statement layout, but a transaction row without a value normally belongs in review.

The control sheet should compare expected with extracted pages, expected with exported rows, printed with exported debit and credit totals, and the calculated balance variance. If 86 expected rows become 87 exported rows, block release even when values agree; the extra line could be a duplicate, malformed continuation, or header carrying no value, any of which may damage later imports.

Place release controls on a dedicated sheet. Record source filename or stable identifier, expected and exported page count, visible and exported transaction count, printed and calculated debit totals, printed and calculated credit totals, opening and closing balances, unresolved fields, and review date. A workbook that balances but contains an extra zero-value header row should still fail the row-count control. Downstream imports can treat that row as data even though it has no effect on the period balance.

Spreadsheet release checks

Readiness requires recorded control results that another person can reproduce, not an undocumented assurance that checks occurred.

Completeness

Account for every source page and transaction, including any exclusion and its documented reason.

Control totals

Demonstrate that debit, credit, opening, and closing calculations match the verified statement layout.

Traceability

Attach a source page and sequence reference to each workbook row.

Review status

Expose corrections and unresolved values through fields, without depending on color or hidden comments.

Detect duplicate and shifted rows

Use date, source description, value, direction, and page to flag possible duplicates, then decide each case manually. Repeated legitimate purchases and split settlements may share all those characteristics.

A temporary sort by source sequence helps reveal imported headers, page totals, and wrapped text mistaken for entries. Restore source order afterward and preserve an unchanged raw-import worksheet.

Duplicate decisions need context. Two same-day 45.00 card purchases may both be valid, whereas a page-boundary repeat may differ only by source sequence. Compare text, direction, balance effect, page, order, and neighboring activity, retaining both a candidate flag and final decision so detection never silently removes evidence.

Run a sort-and-recombine test on descriptions that wrap across lines. A continuation fragment can become its own spreadsheet row with a blank amount, or it can attach to the next transaction and change that merchant's identity. Sort only a working copy, then restore source order using page and row fields. Confirm that every exported row has one transaction date, one direction, one amount, and the complete description expected from the source. Structural row errors can survive perfect-looking column totals when the misplaced fragment carries no amount.

Make corrections auditable

For each correction, capture the extracted value, verified replacement, reason, evidence location, reviewer, and date. Where supported, protect formulas and raw evidence columns against accidental modification.

Include an exception sheet listing unresolved rows and completed controls. Use explicit status values so a blank category cannot be confused with not applicable, awaiting review, or failed extraction.

Create a workbook release record containing version and source identifier or checksum, plus row count, control totals, open-field count, reviewer, date, and allowed use. Inspect formulas, hidden sheets, filters, links, comments, and properties. A reconciled workbook can still disclose unnecessary data or behave unexpectedly for its recipient.

Inspect the workbook as a file, not only as a table. Check formula ranges, hidden rows and sheets, filters, external links, comments, document properties, date types, number formats, and cells stored as text. Open a copy in the intended receiving application and test a simple sort, sum, and CSV export. Record the exact version released and the unresolved exceptions. This catches a workbook that looks correct on screen but omits late rows from formulas, exposes hidden notes, or changes dates when another program opens it.

If the workbook originated from a scan, return to the image statement extraction workflow whenever spreadsheet values alone cannot verify an entry.

Design columns that preserve evidence

Reconcile row counts and control totals

Spreadsheet release checks

Completeness

Control totals

Traceability

Review status

Detect duplicate and shifted rows

Make corrections auditable

Run the next quality check

Bank Statement OCR Quality Checklist

How to Analyze Bank Statement Data

How to Categorize Bank Transactions

Apply this guide to a statement