1. Normalise whitespace with TRIM
PDFs often carry stray leading, trailing, or double spaces into the description column. Wrap the column in =TRIM(A2) in a helper column, copy-paste-values back over the originals, and pivot tables will start grouping identical merchants correctly.
2. Split combined columns with text-to-columns
Some statements pack multiple fields into a single visual column — e.g. date and reference number joined by a space. Highlight the column, Data → Text to Columns → Delimited → space (or fixed width for narrower fields), and Excel splits them into separate columns without formulas.
3. Fix mixed number formatting
Watch for amounts imported as text — they'll left-align in the cell and refuse to SUM. Multiply by 1 in a helper column (=A2*1) and paste values back to coerce them into real numbers.
For amounts in parentheses meaning negative (e.g. '(45.00)'), find-and-replace: '(' with '-' and ')' with '', then convert.
4. Categorise with SUMPRODUCT rules
Add a Category column. In it, build a simple rule table on a second sheet: keywords in one column, categories in the next. Use =INDEX(Rules!B:B, MATCH(1, --ISNUMBER(SEARCH(Rules!A:A, C2)), 0)) as an array formula (or with LOOKUP / XLOOKUP in newer Excel) to auto-tag each row.
Ten to fifteen rules typically cover 70-80% of transactions for a personal account. Tag the rest manually.
5. Summarise with a pivot table
Insert a pivot table over the whole range. Put Category in Rows, Amount in Values (Sum), and — if you have Date parsed — Month in Columns. You'll see instantly where money goes each month.
Pivot tables also make anomalies obvious: an outlier month against category history often flags a misclassified transaction or a duplicate.
6. Save a template
Save a blank workbook with your rules table and pivot skeleton as a template. Each month, paste the converted rows in, refresh the pivot, and month-end reconciliation shrinks from an evening to twenty minutes.
/ TRY IT
Convert your first bank statement in about a minute.
One conversion is free — no card, no sign-up needed to try.
Convert a statement