For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

Clean a bank statement export in Excel

IMP.B2 DATA > FROM TEXT/CSV
Clean a bank statement export in Excel — Excel example preview

Converting locale-mismatched bank dates and trailing-minus debit strings into real numeric values ready for reconciliation.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Intermediate

Excel Automation: Bank Reconciliation Made EASY with Power Query (No VLOOKUP Needed)

Data Future Academy

Open on YouTube (opens in a new tab)
Data Future Academy

Loading preview…

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 00451230 becomes 451230. 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/YYYY export read as MM/DD/YYYY converts 03/04/2026 to 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+15 and 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.

=PRACTICE

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

  1. Keep the original export untouched.
  2. Identify delimiter, date format, currency format, and ID columns before cleaning.
  3. Convert imported data into a table or Power Query output.
  4. 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.