How to Clean Up Bank Statement Data in Excel
Even a "clean" bank export usually needs a few fixes before it's actually usable — amounts stored as text, dates in the wrong format, one combined debit/credit column instead of two, or duplicate rows from re-exporting the same period. Here's how to fix the most common problems, and how to categorize transactions once the data is solid.
1. Fix numbers that are secretly text
The most common problem: amounts look like numbers but are left-aligned and won't SUM()
correctly, because they were imported as text (often because of a currency symbol, thousands
comma, or trailing space carried over from the export). To fix a whole column at once:
- Select the column, then go to Data → Text to Columns.
- Choose Delimited, click Next twice without changing anything, then on the final screen set the column format to General and click Finish.
- Excel re-parses every cell as a number. If a few still resist, there's usually a stray character (a currency symbol or non-breaking space) — use
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",",""))in a helper column to strip it, then paste the result back as values.
2. Fix dates that import in the wrong order
A date like 03/04/2026 means March 4th in a US-formatted file and April 3rd in a
UK-formatted one — Excel guesses based on your system locale, and it guesses wrong constantly
with bank exports. If a date column shows a mix of left-aligned (text) and right-aligned (real
date) cells, that's the tell. Fix it the same way as numbers — select the column, run
Data → Text to Columns, and on the final step choose Date and
pick the format your bank actually uses (DMY or MDY) rather than
leaving it on General.
3. Split a combined debit/credit column
Some exports give you one Amount column with negative numbers for spending and
positive for deposits; others expect separate Debit and Credit columns
(QuickBooks' 4-column CSV format works this way). To split one signed Amount column into two:
- Debit column:
=IF(B2<0,-B2,"")— shows the positive value of negative amounts, blank otherwise. - Credit column:
=IF(B2>0,B2,"")— shows positive amounts as-is, blank otherwise.
Copy both formulas down the full range, then paste-special as values so the columns aren't tied to the original Amount column anymore.
4. Remove duplicate rows
Duplicates creep in whenever you re-export an overlapping date range or paste two months
together. Select your full data range, go to Data → Remove Duplicates, and
check only the columns that should be unique together — usually Date,
Description and Amount. Leave out any running-balance column, since
that will differ even for a true duplicate if rows were reordered.
5. Reconcile against the statement balance
Before trusting the data, confirm it actually ties out. In a helper column next to your running
balance, calculate what the balance should be: start with the opening balance and add
=PreviousBalance + Credit - Debit down each row. Where your calculated balance
diverges from the bank's own running balance column, that row (or an earlier one) has a data
error — a missed transaction, a wrong sign, or a duplicate. This is exactly the check
StatementSheet runs automatically and flags with a reconciliation badge when you convert a PDF,
so you don't have to build it by hand every time.
6. Categorize transactions with a lookup table, not manual tagging
Rather than typing a category into every row, build a small two-column table elsewhere in the workbook — a keyword in one column, a category in the next (e.g. "UBER" → "Transport", "WHOLEFDS" → "Groceries"). Then use a formula that checks whether the description contains any keyword from that table:
=IFERROR(INDEX(Categories[Category], MATCH(TRUE, ISNUMBER(SEARCH(Categories[Keyword], C2)), 0)), "Uncategorized")
entered as an array formula (or with XLOOKUP plus SEARCH in newer
Excel builds that support dynamic arrays) will match a keyword anywhere inside the description
and pull the category automatically. Add new keyword rows to the table as you spot
"Uncategorized" transactions, and every past and future statement benefits from the same rules.
7. Turn it into a usable summary
Once categories are filled in, a PivotTable (Insert → PivotTable) with Category in Rows and Amount in Values gives you a spending summary in a few clicks — far faster than manually summing filtered ranges. Group the Date field by month if you want a spending trend over several statements pasted into one sheet.
Skip the manual cleanup for the initial import
All six of these problems — text-formatted numbers, ambiguous dates, combined debit/credit columns, duplicate rows, and an unreconciled balance — are exactly what StatementSheet fixes automatically when it converts a PDF statement: typed columns, a single consistent date format, separate Debit/Credit/Amount columns, and a running-balance reconciliation badge before you ever open Excel. Categorization is still worth doing yourself with a keyword table, since only you know how you want your own spending grouped.