Clean a bank statement export in Excel
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.
Excel Automation: Bank Reconciliation Made EASY with Power Query (No VLOOKUP Needed)
Data Future Academy
Open on YouTube (opens in a new tab)
Stop Doing Manual Reconciliations in Excel: Use Power Query
Excel University
Open on YouTube (opens in a new tab)
Import Data from TEXT Files in Excel using Power Query
TrumpExcel
Open on YouTube (opens in a new tab)Bank statement exports are useful, but they often need cleanup before they can be reconciled or summarized.
Import instead of opening directly
Opening a CSV by double-clicking can change dates, account numbers, and codes. Use Excel import tools or Power Query when the file has sensitive formats.
Preserve important fields
Watch for:
- Leading zeros in account or reference numbers
- Dates interpreted in the wrong format
- Negative amounts imported as text
- Currency symbols mixed into amount fields
- Extra spaces in transaction descriptions
Split useful information
If the description column contains merchant names, references, or payment channels, split the parts into separate helper columns. Keep the raw description too, because it may be needed for audit support.
Add reconciliation fields
Add columns such as category, matched invoice, reviewed by, and notes. A cleaned statement should support follow-up, not only arithmetic.
Go deeper with this skill
Bring outside data into Excel without losing structure, types, or auditability. For this article, the goal is to practice: Import bank CSV files, preserve important fields, clean descriptions, and prepare transactions for reconciliation.
Practice workbook setup
Use a CSV, bank export, or system report copied into a raw-data sheet.
Practice workflow
- Keep the original export untouched.
- Identify delimiter, date format, currency format, and ID columns before cleaning.
- Convert imported data into a table or Power Query output.
- Document any manual corrections separately.
Quality checks
- Dates, amounts, and IDs imported in the expected format.
- Row counts match the source or have documented exclusions.
- The refresh or re-import process can be repeated.
Common mistakes
- Letting Excel auto-convert IDs, codes, or dates incorrectly.
- Deleting source rows without keeping a raw copy.
- Pasting over formulas during the next import.
Next actions
- Create a repeatable import checklist.
- Move recurring imports into Power Query when possible.
Formula focus: test TRIM(), LEFT(), RIGHT() with normal rows, blank inputs, and at least one edge case.
