Converted bank statement data is rarely analysis-ready. Descriptions are long and inconsistent, and depending on how the data was produced, numbers and dates may be stored as text. These techniques turn a raw transaction list into something you can summarise with confidence.
1. Make sure numbers are numbers
If amounts are left-aligned or SUM returns 0, Excel is treating them as text. Select the column, then use Data → Text to Columns → Finish, which re-evaluates each cell. For amounts containing thousands separators or currency symbols, use a formula such as =VALUE(SUBSTITUTE(SUBSTITUTE(C2,",",""),"$","")) in a helper column.
Files exported from FinParse already store debits, credits and balances as numbers, so you can skip this step.
2. Fix dates
Dates stored as text will not sort or group properly. =DATEVALUE(A2) converts many text dates, but it follows your computer’s regional settings, so 03/04/2024 may be read as either March or April. Unambiguous formats such as 2024-04-03 avoid the problem – which is why FinParse exports ISO dates.
3. Create a single signed amount column
Separate Debit and Credit columns are easy to read, but a single signed amount is easier to summarise. Add a column with =D2-C2 (credit minus debit) so money in is positive and money out is negative.
4. Tidy descriptions
=TRIM(B2)removes extra spaces.=PROPER(B2)changes “TESCO STORES” to “Tesco Stores”.- Use Find and Replace to strip recurring prefixes such as “POS ” or “UPI/” so similar transactions group together.
5. Add categories
Create a small lookup table with keywords in one column and categories in the other, for example “NETFLIX → Subscriptions”. Then use a formula to find the first keyword contained in each description. In Excel 365:
=XLOOKUP(TRUE, ISNUMBER(SEARCH(Keywords[Keyword], B2)), Keywords[Category], "Uncategorised")
Review the “Uncategorised” rows and extend the lookup table until most transactions are covered.
6. Summarise by month and category
Convert the range to a table (Ctrl+T), then insert a PivotTable. Put the date in Rows and group it by Months and Years, put Category in Columns, and the signed amount in Values. You now have a monthly spending summary that updates when you add new statements.
7. Check for duplicates and gaps
When combining several statements, overlapping periods can create duplicates. Highlight them with conditional formatting on a helper column that joins date, description and amount. Compare the first and last date of each statement to make sure no days are missing.
Need the data first? Convert your bank statement to Excel in your browser, then come back to these steps.