Business

Chart monthly spending by category across three months

Build a stacked column chart that compares spending by category over three months using SUMIFS and EDATE, with an Australian dollar worked example.

Editorial illustration for Chart monthly spending by category across three months

To see how spending by category moves from month to month, build a small grid with categories down the side and months across the top, then chart it as stacked or clustered columns. SUMIFS with EDATE fills the grid from your transaction list, so a new month is just a new column.

Why compare months at all?

One month's chart tells you what happened. Three months side by side tell you what is normal. If dining out is A$260, A$310 and A$240, then A$310 is a nudge, not a crisis. If a category rises every month, that is worth a closer look.

The grid

Keep transactions in A to C: Date, Category, Amount, rows 2 to 500. In G2 to G6 list your categories. In H1, I1 and J1 put the first day of three consecutive months as real dates, formatted as mmm yyyy. In H2 enter the formula below, then fill right and down:

  • =SUMIFS($C$2:$C$500,$B$2:$B$500,$G2,$A$2:$A$500,">="&H$1,$A$2:$A$500,"<"&EDATE(H$1,1))
  • The dollar signs matter: $G2 stays on the category column, and H$1 stays on the month row.
  • EDATE(H$1,1) gives the first day of the following month, so the condition is less than that date.
  • The same formula works in Google Sheets.

A worked three-month example

These figures are invented, in Australian dollars.

CategoryMonth 1Month 2Month 3
Housing1,9001,9001,900
Groceries620580640
Eating out260310240
Transport210195225
Other190170200
Total3,1803,1553,205

Check month 1: 1,900 + 620 + 260 + 210 + 190 = 3,180. Month 2: 1,900 + 580 + 310 + 195 + 170 = 3,155. Month 3: 1,900 + 640 + 240 + 225 + 200 = 3,205.

Stacked or clustered: which chart answers your question?

A stacked column shows the total for each month and how it divides, which is right when your question is whether the month got bigger. A clustered column puts each category's three months side by side, which is right when your question is whether dining out is growing. Housing is the same every month, so in a stacked chart it takes up most of the bar and squashes the interesting parts. Consider leaving fixed costs out of the chart and showing only the flexible categories.

Steps to chart it

  1. Select G1:J6, including the headings. Leave the Total row out.
  2. Insert a column chart and pick stacked or clustered.
  3. If the months and categories are swapped, use Switch Row/Column in Excel or the Switch rows and columns option in Sheets.
  4. Label the axis with the currency symbol.

Adding a fourth month without breaking anything

When the next month arrives, copy the last month's column to the right, change the date in row 1 to the first day of the new month and the formula will adapt, because the month reference is relative. Then extend the chart's data range to include the new column. In Excel, drag the corner of the highlighted range. In Sheets, edit the data range in the chart editor. Keep older months in place instead of deleting them, so the trend remains visible.

To spot trends faster, add a column on the right that shows the change between the first and last month for each category, using a plain subtraction such as =J2-H2. A positive number means spending rose over the period, and a negative one means it fell. Sort by that column to see the movers. With the made-up figures above, eating out falls by A$20 across the three months while groceries rise by A$20, which is a small difference but a useful prompt to look at the lines behind it.

Check before you trust the chart

  • Do the month totals match the sum of all the transactions in that month?
  • Are there rows with a category you left out of the list?
  • Did any transfer between your own accounts sneak into the data?
SPENDIXAI JOURNAL

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

Explore your reports