Analysing a Bank Statement in Excel with Pivot Tables

Turning a converted bank statement into monthly summaries, category breakdowns and cash flow views using pivot tables.

A converted statement is a flat list. Pivot tables are how you turn it into answers, and there are a few setup decisions that make the difference between a pivot that works and one that fights you.

Set the data up first

Three things need to be right before you build a pivot:

Dates must be real dates, not text. If they are left-aligned in the cell, Excel thinks they are text and grouping by month will not work.
Amounts must be numbers, not text. Currency symbols or stray spaces make them text.
Add a Month column — `=EOMONTH(date, 0)` — rather than relying on pivot date grouping, which behaves differently across Excel versions.

A spreadsheet from this site needs the first two steps. Every cell is kept exactly as your bank printed it and stored as text — which is what keeps a long reference number whole — so dates and amounts have to be turned into dates and numbers before a pivot can group or sum them.

Three pivots worth building

1. Monthly cash flow

Rows: your Month column. Values: sum of debit, sum of credit. Add a calculated field for net. This is the single most useful view for most people — it shows whether each month was positive or negative at a glance.

2. Category breakdown

Rows: category. Values: sum of debit. Filter to a date range. This requires a category column, which means a lookup table — but it is the view that actually changes behaviour.

3. Counterparty analysis

Rows: description or merchant. Values: sum of debit, count. Sort descending by sum. This surfaces recurring payments you have forgotten about, which is frequently the point of the exercise.

Verify before you analyse

None of this is worth doing on an incomplete file. A pivot table on a statement missing a page produces confident, well-formatted, wrong answers.

Check the totals against the statement first. It takes seconds and it is the difference between analysis and fiction.

A cash flow view worth building

Beyond the three pivots above, one chart is worth the setup: monthly net alongside a running balance.

The net figure tells you whether each month was positive; the balance line tells you whether the trend is sustainable. A month that was slightly negative matters differently depending on whether the balance is climbing or falling.

Excluding transfers first

Before any of this means anything, exclude transfers between your own accounts. On a household with a current and a savings account, those movements can easily be the largest debits and credits in the file, and they represent no income or expenditure at all.

Frequently asked questions

Why can't I group my pivot by month?

The date column is text rather than real dates. Text to Columns forces Excel to re-parse it.

Why does my income look higher than my salary?

Transfers in from your own accounts are probably being counted as income. Exclude own-account transfers before totalling anything.

How do I categorise transactions?

Build a lookup table of merchant fragments and categories, then use a partial-match formula. It is an hour's work once and seconds thereafter.

Convert a statement now

Bank Statement PDF to Excel, or see every format. More in the guides.