Clean a bank statement export in Excel

Converting locale-mismatched bank dates and trailing-minus debit strings into real numeric values ready for reconciliation.
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 and almost never usable as they arrive. The damage usually happens in the first five seconds — before you have typed anything, just from double-clicking the file.
This walks through importing without losing data, normalising the columns banks disagree about, and adding the fields that make a statement reconcilable rather than merely readable.
Never open the CSV by double-clicking
Opening a CSV directly hands every column to Excel’s parser with no say from you. Three things get destroyed, silently and irreversibly:
- Leading zeros vanish. Account number
00451230becomes451230. The file on disk still has it; the thing on your screen does not, and if you save, the file no longer does either. - Dates get the wrong locale. A
DD/MM/YYYYexport read asMM/DD/YYYYconverts03/04/2026to 4 March instead of 3 April. Every date where both parts are 12 or under is wrong, and every date where one part exceeds 12 stays right — so the column looks fine at a glance. - Long reference numbers become scientific notation. A 16-digit reference
shows as
1.23457E+15and the trailing digits are gone for good.
Use Data → From Text/CSV instead. That opens the Power Query import preview, where you get to set the file origin, the delimiter, and — on the Transform Data screen — the data type of every column before anything loads.
Set the account number, sort code, and reference columns to Text first. Then set the date column with Using Locale, choosing the locale the bank exported in rather than the one your machine runs. Those two steps prevent every failure above.
The extra benefit is that the import is now a query. Next month’s statement refreshes through the same steps — see the Power Query refresh checklist for what to check when it does.
Normalise the amount column first
Banks disagree fundamentally about how to express money moving, and you cannot reconcile anything until you have picked one convention.
The three layouts you will meet:
- Separate Debit and Credit columns, each positive, one blank per row.
- A single signed Amount column, negative for money out.
- An Amount column plus a D/C indicator in a separate field.
Collapse all three to one signed column. Money out is negative:
=IFERROR(N(Credit),0)-IFERROR(N(Debit),0)
Then check your work with a column that must come out at zero:
=Opening+SUM(Amount)-Closing
If that is not zero, something is missing — usually rows outside the range, or amounts that imported as text. Watch for currency symbols and thousand separators baked into the amount field; those force the whole column to text and it sums as zero without complaining. Strip them in the query, not by hand, so the fix repeats next month.
Also check for a trailing minus (1,234.56-), which some systems still emit and
Excel does not recognise as a number at all.
Clean the description field
The description column is where banks put everything they could not fit elsewhere: merchant name, card number fragment, payment channel, reference, location, sometimes a date that disagrees with the date column.
Three rules:
Keep the raw description, always. Add cleaned columns beside it, never over it. When an auditor asks what the bank actually said, the answer has to be in the file.
Trim and collapse whitespace. Fixed-width exports pad with spaces, and a trailing space makes two identical merchants into two categories:
=TRIM(CLEAN([@Description]))
CLEAN removes non-printing characters, which turn up more often than you would
expect in exports from older banking systems.
Split only what you will use. If the description reliably starts with the
merchant and ends with a reference, pull those out with Power Query’s
Split Column → By Delimiter or Column From Examples, which is genuinely
good at this. Do not try to parse every variant — a Merchant column that is
right 90% of the time plus the raw text is more useful than a brittle formula
that breaks on the other 10%.
Watch for the import problems specific to statements
- Duplicate rows from overlapping date ranges. Downloading 1–31 March and then 25 March–25 April gives you a week twice. Match on the bank’s own transaction ID if it provides one; if not, treat date + amount + description as the key, and be careful — a genuine repeat payment looks identical. The duplicate finder will show you the candidates without changing the file.
- Balance rows and summary lines mixed into the transactions. These sum as transactions and double your totals.
- Multi-currency statements where the amount column mixes currencies with no indicator. Split by currency before summing anything.
- Dates that are text even after the import, which will not group by month in
a pivot.
=ISNUMBER(A2)tells you; the pre-pivot cleaning checklist covers the fix.
Add the fields reconciliation needs
A cleaned statement supports follow-up, not only arithmetic. Add these columns after the bank’s own:
| Column | Why |
|---|---|
Category |
Dropdown from a Lists sheet, never free text |
MatchedTo |
Invoice, bill, or journal reference |
MatchStatus |
Matched, Partial, Unmatched, Query |
ReviewedBy |
A name, so follow-up has an owner |
Notes |
Why an unusual item is what it is |
PeriodKey |
=TEXT([@Date],"yyyy-mm") for clean monthly grouping |
That last one is small and pays for itself immediately — it gives pivot tables
and SUMIFS a stable monthly key that does not depend on date grouping working.
Then summarise with the same pattern used everywhere else:
=SUMIFS(tblBank[Amount],tblBank[PeriodKey],$A5,tblBank[Category],B$4)
Find the unmatched items deliberately
The whole point of the exercise is the exception list. Once MatchStatus is
populated, filter to Unmatched and sort by absolute amount descending. That
view — usually a handful of rows — is the actual work.
Two checks worth keeping on a Checks sheet:
=COUNTIFS(tblBank[MatchStatus],"Unmatched")
=SUMIFS(tblBank[Amount],tblBank[MatchStatus],"Unmatched")
A count that climbs month over month means the matching rule needs work, not that the bookkeeper needs to try harder.
Keep raw and clean apart
Three sheets: Raw (untouched import), Clean (the working table), Checks.
Never edit Raw. When a number is disputed — and on bank data it eventually is —
being able to show the original export next to the cleaned version ends the
conversation in about a minute.
If this is a monthly job, the whole sequence belongs in Power Query rather than in formulas. Power Query vs VBA for data cleanup covers that choice, and getting started with Power Query is the place to begin if you have not built a query before.
FREE EXCEL UPDATES
Get practical Excel tips in your inbox
New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.
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 a bank CSV without destroying account numbers or dates, normalise debit/credit columns into one signed amount, parse the description field, and add the columns reconciliation actually needs.
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(), TEXT(), IFERROR(), SUMIFS() with normal rows, blank inputs, and at least one edge case.





