Use a pivot table to see your spending without building a dashboard

If your transactions live in a sheet, a pivot table is usually enough analytics. No app switch. No new login.

A pivot is a machine for grouping rows and adding up a column. You point it at your transaction table, tell it what to group by, and it recalculates every time the underlying data changes. That last part is the whole value: unlike a chart you built by hand in January, a pivot does not go stale.

The minimum useful pivot

Rows: category
Columns: month
Values: sum of amount (expenses as positive or absolute value, pick one convention and stick to it)

Filter out transfers and income if you only want spending.

That single pivot is the report. Resist the urge to add a second value field, a percentage-of-total column, and conditional formatting on day one. Build the plain version, use it for two months, then add exactly the one thing you kept computing in your head.

Questions this answers quickly

• Which category jumped this month?
• Is dining a blip or a trend?
• Did the subscription line actually fall after the audit?

That last one is the pattern worth copying. Make a change, then check it against the pivot a month or two later rather than trusting your memory of having made it. A subscription audit that never gets verified is a to-do list, not a result.

A second pivot earns its place occasionally: rows by merchant, filtered to one category, values summed. That is the view that answers “what is actually inside the food number” without scrolling the raw ledger.

Keep the source tidy

Pivots punish messy data. Consistent category names, real dates, and no blank amounts matter more than chart colors.

Three specifics do most of the work. Keep one header row and no blank rows inside the table, because a gap can truncate the range on refresh. Store dates as real dates rather than text, so month grouping works instead of producing a column per day. And keep category spelling stable, since “Groceries” and “groceries ” with a trailing space become two rows that look identical and never add up. The habits in how to categorize transactions are what keep a pivot honest.

If your table grows past a year, define the source as a named range or a table object rather than a fixed cell range. Otherwise every new month means editing the pivot definition, and the month you forget to edit is the month the numbers quietly go wrong.

When to stop

If you’re building cross-tabs of cross-tabs, you’ve left personal finance and entered a reporting project. Go back to the one pivot that drives decisions.

The signal that you have overshot is usually time. If producing the monthly view takes longer than reading it, the reporting has become the activity. A household needs a handful of numbers, and it needs them reliably rather than elaborately.

The pivot is how you interrogate the table without leaving the workbook. That is its whole job, and it is enough.