How-to · Spreadsheet problems
How to clean bank statement data in Excel
Fix dates, amounts, duplicates, and messy descriptions so your bank statement spreadsheet is ready for reconciliation, reporting, and imports.
Why clean bank statement data in Excel?
Even a good PDF conversion can leave small issues: mixed date formats, text that looks like numbers, or description noise from the bank layout. Cleaning turns a usable export into data you can trust.
A few minutes of cleanup prevents hours of broken filters, failed imports, and reconciliation mismatches later.
- Reliable filters and pivots
- Fewer import errors into accounting tools
- Faster reconciliation
- Consistent dates and amounts
- Cleaner merchant reporting
- Confidence before tax or audit use
Step-by-step: clean bank statement data in Excel
Start from structured data
Use an Excel or CSV export from a bank-statement converter so you already have clear columns—not pasted PDF text.
Normalize dates
Convert the date column to a single format (e.g. ISO or your locale). Sort chronologically and check for blanks.
Fix amounts
Ensure debit and credit are numbers. Strip currency symbols, fix decimal separators, and leave empty cells where a side does not apply.
Clean descriptions
Trim spaces, remove repeated bank codes if needed, and optionally standardize merchant names for easier filtering.
Remove noise and duplicates
Delete header/footer leftover rows. Flag and review duplicate date+amount+description combinations.
Validate balances
Confirm opening and closing balances against the PDF. Spot-check a sample of running balances if the export includes them.
Quick cleaning checklist
Dates
One format, no blanks in transaction rows, chronological order makes sense.
Amounts
Numeric types, correct signs or separate debit/credit columns, no currency text mixed in.
Descriptions
Trimmed, readable, free of random line breaks from the PDF.
Duplicates & gaps
No repeated transactions; row count and totals match the statement.
Related spreadsheet how-tos
Put data into Excel first
Get structured rows into a workbook before you clean. Put statement data into Excel →
Convert PDF to Excel
Full conversion from bank statement PDF to workbook. Convert PDF to Excel →
FAQ: clean bank statement data in Excel
How do I clean bank statement data in Excel?
Start with a structured export (date, description, debit, credit, balance). Then normalize dates, split or unify amount columns, trim descriptions, remove duplicate rows, and verify running balances against the original PDF.
What makes bank statement data “dirty” in Excel?
Common issues: mixed date formats, amounts stored as text, extra spaces in merchant names, duplicate transactions, missing balances, and rows that still look like PDF headers or footers.
Should I clean before or after converting the PDF?
Convert first with a bank-statement tool so columns are already structured. Cleaning in Excel is then mostly about format consistency and validation—not reconstructing the table from scratch.
How do I find duplicate transactions in Excel?
Use conditional formatting or a helper column that concatenates date + amount + description, then flag repeated keys. Dedicated duplicate detectors can also help before you open the file.
How do I fix amounts that Excel treats as text?
Remove currency symbols and spaces, replace commas/periods to match your locale, then use VALUE() or Text to Columns so debit and credit columns are true numbers.
When is cleaning not enough?
If the PDF was poorly extracted (missing rows, scrambled columns), re-convert with a bank-aware tool instead of trying to repair a broken sheet by hand.
Start with cleaner statement data
Convert the PDF first, then polish in Excel—or re-export if the sheet is too messy.