How to Clean Bank Statement Data in Excel (Step by Step)

TL;DR: Messy bank data almost always has the same handful of problems: dates stored as text, amounts with currency symbols or trailing minus signs, separate debit and credit columns, descriptions split across lines, and junk rows such as headers, subtotals and balance-forward lines. Fix them in this order: remove junk rows, fix dates, fix amounts, merge debit/credit into one signed column, tidy descriptions, then verify that opening balance plus the sum of amounts equals the closing balance. Power Query makes it repeatable.

Whether your data came from a bank's CSV export, a copy-paste from a PDF, or a converter, it rarely arrives perfectly clean. And clean data matters: a single text-formatted amount drops out of a SUM silently, a date in the wrong format sorts into the wrong month, and a description split over two rows breaks your categorization rules.

We spend a lot of time making bank data clean at SheetStatement, so here's the checklist we'd use by hand in Excel.

Before you start: keep the original

Copy the raw data to a sheet called Raw and never edit it. Do all cleaning on a copy or in Power Query. If something goes wrong, you can start again, and you can always prove what the source said.

Step 1: Remove junk rows

Bank exports and PDF copy-pastes often include rows that aren't transactions:

• Repeated column headers (one per page of a PDF).
• "Balance brought forward" or "Opening balance" lines.
• Page totals and subtotals.
• Blank rows.
• Footer text, disclaimers and page numbers.

Filter each column for blanks and for words like "Balance", "Total", "Page", "Date" (in the date column) and delete those rows. Be careful with "balance brought forward": it's useful for checking, so note its amount before deleting it.

A helper column makes this systematic:

=IF(ISNUMBER(A2), "keep", "check")

Where column A holds dates, anything that isn't a real date gets flagged for review. (This works once dates are fixed; if they're text, use the next step first, or test with a pattern like =ISNUMBER(--LEFT(A2,2)).)

Step 2: Fix dates

Common date problems:

Problem	Example	Fix
Text dates	03/04/2026 left-aligned	Data > Text to Columns > Date (choose DMY or MDY)
Wrong order	3 April read as March 4	Text to Columns with the correct order
No year	04 Mar	Add the year from the statement period
Month names	4-Mar-26	Usually converts; check the year
Mixed formats	Some DMY, some MDY	Fix at the source, or parse with formulas

Text to Columns is the quickest fix: select the column, choose Data > Text to Columns, click through to step 3, choose Date and the order the source uses (for example DMY for UK statements). Excel converts the text to real dates.

For dates without a year, which PDF statements often have, build the date with a formula:

=DATEVALUE(A2 & " " & 2026)

Watch out for statements that span December and January: the January rows need the next year.

Check the result: real dates are right-aligned by default, and =ISNUMBER(A2) returns TRUE.

Step 3: Fix amounts

Amount problems are the most common cause of totals that don't tie:

• Currency symbols: $1,234.56 or £45.00.
• Thousands separators: 1,234.56 imported as text.
• Trailing minus: 45.00-.
• Brackets for negatives: (45.00).
• DR/CR suffixes: 45.00 DR.
• Non-breaking spaces, invisible but enough to make a number text.
• European formats: 1.234,56.

A cleaning formula that handles most of these:

=LET(t, TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"$",""),",",""),CHAR(160),"")),
     neg, OR(RIGHT(t,1)="-", LEFT(t,1)="(", RIGHT(t,2)="DR"),
     n, VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(t,"-",""),"(",""),")",""),"DR","")),
     IF(neg, -n, n))

Adjust the currency symbol and remove the comma substitution if your data uses commas as decimal separators. For European formats, it's usually easier to re-import with the right locale (Data > From Text/CSV lets you choose the file origin and locale) than to fix with formulas.

Check: =ISNUMBER(C2) should be TRUE for every row, and =COUNT(C:C) should equal the number of transactions.

Step 4: Merge debit and credit columns

Many statements have separate "Money out" and "Money in" (or Debit and Credit) columns. Most tools want one signed amount. In a new column:

=N(D2) - N(C2)

where C is money out and D is money in. N() treats blanks as zero. The result is positive for money in and negative for money out, which is what most accounting software expects. Our article on bank statement CSV formats explains the conventions different tools use.

For credit cards, the sign convention is often reversed in exports (charges positive). Decide which convention you want and apply it consistently.

Step 5: Tidy descriptions

Descriptions cause two different problems: structure and noise.

Multi-line descriptions. PDF statements often wrap long descriptions onto a second line, which becomes a separate row with no date or amount. To merge them:

1. Add a helper column that's TRUE when the row has no date: =A2="".
2. For continuation rows, append the text to the row above: in a new column, =IF(A3="", E2 & " " & B3, B3). Work from the bottom up, or use Power Query's fill-down on the date and group by.
3. Delete continuation rows once merged.

This is one of the fiddliest steps by hand, and a good reason to use a converter that handles wrapped lines.

Noise. Descriptions are full of reference numbers, card digits and dates that make the same merchant look different each time. Clean them into a separate Payee column:

=PROPER(TRIM(LEFT(B2, FIND("*", SUBSTITUTE(B2, " ", "*", 3) & "*") - 1)))

That keeps the first three words, a crude but surprisingly useful start. Better still is a lookup table that maps keywords to clean payee names; our guide to Excel formulas for categorizing transactions builds one.

Always keep the original description alongside the cleaned version. The cleaned payee is for grouping and reporting; the original is what you search when matching against the statement, a receipt or a bank feed, and what an accountant or auditor will want to see.

One more description fix worth making: remove double spaces and stray characters with =TRIM(CLEAN(B2)). CLEAN strips non-printing characters that sometimes come through from PDFs and break lookups, and TRIM collapses repeated spaces. Lookup formulas that mysteriously fail on one row are very often caused by an invisible character like this.

Step 6: Remove duplicates carefully

Duplicates appear when exports overlap or a page is pasted twice. But legitimate duplicates exist too: two coffees of the same price on the same day.

Don't use Remove Duplicates blindly. Instead:

1. Add a key: =A2 & "|" & B2 & "|" & C2 (date, description, amount).
2. Count occurrences: =COUNTIF(F:F, F2).
3. Review rows with a count above 1 against the source statement.

If the statement shows the item once, delete the extra. If it shows it twice, keep both. A running balance column, if your source has one, settles it: legitimate duplicates have different running balances.

Step 7: Verify the totals

This is the step that tells you the data is complete:

Opening balance + SUM(Amount) = Closing balance

Take the opening and closing balances from the statement. If they don't agree:

• A missing row: compare the count of transactions with the statement.
• A sign error: look for a transaction equal to half the difference.
• A text amount: check =COUNT() vs =COUNTA() in the amount column.
• An included junk row: a subtotal or balance line that slipped through.

If the source has a running balance, recompute it (=previous + amount) and compare row by row; the first row where they differ is where the problem is. Our balance checker tool does this automatically.

Add the columns you'll need later

Clean data is more useful with a few derived columns. Add them now, while you're thinking about structure:

• Month: =TEXT([@Date],"yyyy-mm") for grouping in PivotTables.
• Direction: =IF([@Amount]>0,"In","Out") for quick filtering.
• Account: the account name or last four digits, essential when you combine several statements.
• Source: the file name and page, so any row can be traced back to the statement.
• Category: empty for now, ready for your rules.

The Account and Source columns are the ones people regret not adding. Once three accounts are combined into one table, a row without an account label is almost useless, and when someone asks "where did this come from?", a source column answers in seconds.

Cleaning data from different banks

Each bank has its own quirks, and once you know them, cleaning gets faster. A few patterns we see often:

• US banks usually use month-first dates and sometimes show debits and credits in a single column with a minus sign.
• UK banks usually use day-first dates and often have separate Paid out and Paid in columns, with a running balance.
• Credit card statements often list charges as positive numbers and payments as negative, the opposite of a bank account.
• Some exports include a pending transactions section at the top, which should usually be removed because pending items can change before they post.
• Multi-currency accounts may include a currency column, or a separate statement per currency.

Our bank-specific guides cover several of these in detail, for example Chase statements to Excel and Barclays statements to CSV.

A cleaning checklist to keep

Print this or keep it next to your template:

1. Raw data saved untouched.
2. Junk rows removed (headers, totals, balance lines, blanks, footers).
3. Dates are real dates in the correct order and year.
4. Amounts are numbers; COUNT equals COUNTA.
5. One signed amount column, with a consistent convention.
6. Wrapped descriptions merged; original description kept.
7. Duplicates reviewed against the statement.
8. Opening balance + sum = closing balance, per statement.
9. Account and source columns filled.
10. Data formatted as a named Table.

If every box is ticked, the data is ready for categorizing, reconciling or importing into accounting software.

Step 8: Make it a table

Select the clean data and press Ctrl+T to make it an Excel Table. Name it (for example Transactions). Tables expand automatically, formulas fill down, and PivotTables and lookups refer to columns by name. Everything that follows (categorizing, reconciling, summarizing) gets easier.

Doing it all in Power Query

If you clean the same kind of file every month, do it once in Power Query and reuse it:

1. Data > Get Data > From File > From Text/CSV (or From Workbook).
2. Set the locale for dates and numbers when importing.
3. Remove top rows and filter out junk rows.
4. Change column types: Date, Decimal Number.
5. Replace values for currency symbols; add a custom column for the signed amount.
6. Trim and clean text columns (Transform > Format > Trim / Clean).
7. Close & Load to a table.

Next month, drop the new file in the same place and click Refresh. Our Power Query guide covers importing PDFs directly.

A worked example

A bookkeeper receives three months of a client's statements, copied from PDF into Excel by the client. Problems found:

• 9 repeated header rows and 3 "Balance carried forward" rows.
• Dates as text in DD MMM format with no year.
• Amounts in separate Paid out and Paid in columns, as text with commas.
• 41 descriptions wrapped onto a second row.
• One page pasted twice (28 duplicate rows).

She removes junk rows, builds dates with the year, converts amounts, merges the two columns, merges the wrapped descriptions, and removes the duplicate page after checking it against the PDF. Opening balance plus the sum of amounts equals the closing balance for each month.

It took about 90 minutes, most of it on the wrapped descriptions and the duplicate page. The dates and amounts took ten minutes between them once she knew the formulas. Converting the original PDFs directly would have taken a few minutes, which is what she does for the next client.

When to stop cleaning and start again

If your data has several of these problems at once, cleaning by hand can take longer than going back to the source. Converting the original PDF statements with a tool designed for bank statements, such as SheetStatement's PDF to Excel converter, gives you real dates, signed numeric amounts, merged descriptions and a balance check from the start. For a comparison of approaches, see PDF to Excel methods compared.

Edge cases that trip up cleaning formulas

Even with the steps above, a few situations need extra care.

Year-end statements without years. A statement for December 15 to January 14 lists dates like "28 Dec" and "03 Jan". If you add a single year to all of them, the January rows land eleven months early. Use the statement's period: =IF(MONTH(DATEVALUE(A2&" 2026"))>=12, DATEVALUE(A2&" 2025"), DATEVALUE(A2&" 2026")) is one way, adjusted for your statement's months. Then sort and check that the dates run in order.

Negative zero and tiny remainders. After merging debit and credit columns, sums occasionally show -0.00 or a difference of 0.0000001. That's floating point arithmetic, not a missing transaction. Wrap totals in =ROUND(…,2) before comparing them with the statement.

Amounts that are really references. Some exports put a cheque number or reference in a numeric-looking column. If a "transaction" of 104,233.00 appears in a small account, check whether it's a cheque number that slipped into the amount column.

Descriptions with commas. If you save as CSV and a description contains a comma, the file needs quotes around that field. Excel handles this when saving, but hand-built CSVs often don't, and the import shifts every column after the comma.

Pending rows. Online exports sometimes include pending transactions with no posting date. Remove them; they'll appear properly on the next statement, possibly with a different amount.

A second worked example: a CSV export, not a PDF

A client exports six months from online banking as CSV, and the import into their accounting software fails. Opening it in a text editor shows the problems: a two-line header with the account name, dates as 2026-04-03T00:00:00, amounts with a trailing space, and a final "Total" row.

1. Delete the first line, keeping the real column headers.
2. Convert the dates with =DATEVALUE(LEFT(A2,10)), then format as dates.
3. Clean amounts with =VALUE(TRIM(C2)).
4. Delete the Total row.
5. Check: opening balance from the first statement, plus the sum, equals the latest balance shown online.

Fifteen minutes, and the import worked first time. The lesson: always open a CSV in a text editor before blaming the software.

FAQ

Why won't my bank statement amounts add up in Excel?

Usually because some amounts are stored as text (because of currency symbols, commas, spaces or trailing minus signs), so SUM ignores them. Convert them to numbers and compare COUNT with COUNTA.

How do I convert text dates to real dates in Excel?

Use Data > Text to Columns, choose Date and the order your source uses (such as DMY). For dates without a year, build them with DATEVALUE and the year from the statement.

How do I combine debit and credit columns into one?

Use a formula like =N(MoneyIn) - N(MoneyOut) to create a single signed amount, positive for money in and negative for money out.

How do I fix descriptions split across two rows?

Identify continuation rows (no date or amount), append their text to the row above, then delete them. Power Query or a statement converter handles this more reliably.

Should I use Remove Duplicates on bank data?

Not blindly. Identical transactions can be legitimate. Flag duplicates with a key and count, then check against the statement or running balance.

How do I know my cleaned data is complete?

Check that the opening balance plus the sum of all amounts equals the closing balance for each statement period.