Spreadsheet cleanup
How to Clean Bank Statement Data in Excel
Reviewed 12 September 2026
Short answer
Short answer
Start by preserving an untouched copy. Make one row per transaction, remove repeated page headings and blank lines, join wrapped descriptions to the correct row, convert dates and amounts into real Excel values, separate debit and credit consistently, and add a Source File column. Check the cleaned table against the opening and closing balances before using it.
A tidy-looking table can still be financially wrong. Cleanup is complete only when the rows remain traceable to the statement and the numbers reconcile.
Clean structure before formatting
Do not begin with colours, column widths or currency symbols. First make sure each row represents one transaction and each column represents one field. Formatting a broken table only makes it easier to trust.
Descriptions deserve special care. A second line may belong to the transaction above, while a date-looking reference may not start a new transaction at all. Keep the original PDF open while resolving ambiguous rows.
Step by step
- 01
Duplicate the raw sheet
Keep one untouched import so every edit can be audited.
- 02
Remove non-transactions
Delete page headings, footers, balance summaries and empty rows from the working copy.
- 03
Repair row boundaries
Join continuation descriptions and split any rows that contain two transactions.
- 04
Set real data types
Convert dates to dates and amounts to numbers, then use formatting for display.
- 05
Reconcile the result
Use statement balances and totals to prove that no amount was lost or reversed.
Checks before you rely on the spreadsheet
- One transaction per row.
- One header row only.
- No numeric value is stored as text.
- Cleaned movement agrees with the statement.
Questions people ask
Why are bank statement amounts stored as text in Excel?
Currency symbols, spaces, commas or unusual minus signs can stop Excel recognising a numeric value. Clean the characters first, then explicitly set the column type.
Should I delete balance rows?
Remove non-transaction balance summaries from the transaction table, but retain the opening and closing figures elsewhere so you can reconcile the cleaned data.
Can a converter remove the cleanup step?
A bank-specific converter can handle known statement structures before producing CSV, but you should still check the result against the source statement.