Money tips

Which category is pushing spending up? A three-month baseline

Compare each category with its own three-month average to see what is pushing spending up, with a SUMIFS recipe for Excel or Google Sheets, in pounds.

Editorial illustration for Which category is pushing spending up? A three-month baseline

Compare each category's latest month with its own average of the previous three months. The category with the biggest positive gap is pushing your spending up, and a SUMIFS formula can produce every figure for you from a plain list of transactions.

Why a three-month baseline rather than last month?

Last month on its own can be a quiet one or a heavy one, so a comparison against it inherits its quirks. An average of three months smooths out a single odd month and gives each category a baseline that belongs to it.

How do you set up the sheet?

Put your transactions on one sheet, with the date in column A, the category in column B and the amount in column C, running from row 2 to row 500. On a second sheet, list your categories down column F starting at F2, and put the first day of the month you want to measure in G1, for example 1 November.

This formula in G2 totals one category for the month in G1 (it works in Excel and Google Sheets):

  • =SUMIFS($C$2:$C$500, $B$2:$B$500, $F2, $A$2:$A$500, ">="&$G$1, $A$2:$A$500, "<"&EDATE($G$1,1))

The first range is what gets added up, and every pair after it is a condition: the category matches, the date is on or after the first of the month, and the date is before the first of the next month. Copy it down the category list. To get earlier months, put each month's first day in another header cell and copy the formula across, adjusting the reference to that header.

The baseline in the next column is then a plain average of the three earlier month columns, for example =AVERAGE(H2:J2), and the gap is the latest month minus that average.

What does a finished table look like?

CategoryAugSepOctBaselineNovGap
Groceries£540£560£520£540£605+£65
Home energy£110£120£130£120£185+£65
Eating out£130£95£165£130£220+£90
Transport£180£210£150£180£175-£5
Subscriptions£48£48£48£48£48£0

Check the baselines: groceries (540 plus 560 plus 520) divided by three is 540, home energy is 120, eating out is 130 and transport is 180. The gaps sum to £215 (65 plus 65 plus 90 minus 5).

Which category is the real driver?

Eating out has the biggest gap at £90, which is 42% of the £215 increase when you divide 90 by 215. Groceries and home energy are level at £65 each. Home energy is the one most likely to be seasonal, since a winter month often costs more, so the first questions would be about eating out and whether groceries and eating out are trading places.

If the gap column makes you want a quick picture, sort the categories by gap, largest first, and look only at the top two rows. A table with fifteen categories is noise; two rows with a reason attached are a plan. Add a note column next to the gap and write the reason in a few words as soon as you know it, so the sheet carries last month's explanation into next month.

When does the sheet stop being enough?

Once you add more cards, a new account or twelve months of lines, categorising by hand becomes the slow part. SpendixAI is built around India and INR, so we would not point a UK reader to it, but the same idea applies in any tool: keep categories consistent and let the tool do the monthly sums.

Quick checklist

  • Dates are real dates, not text.
  • Category names are spelt identically in the list and on every row.
  • Ranges stop where your data stops, with a little room to grow.
  • You compare against three months, not one.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports