Business

Chart monthly spending by category in Excel 365 with spill formulas

Use UNIQUE, SUMIFS and SORTBY in Excel 365 or 2021 to build a self-growing spending-by-category summary and chart, with a Canadian dollar example.

Editorial illustration for Chart monthly spending by category in Excel 365 with spill formulas

In Excel 365 or Excel 2021 you can build a category summary that grows by itself: UNIQUE lists the categories, SUMIFS totals them, and SORTBY orders them. A chart built on the result updates as new categories appear. Older versions of Excel do not have these functions, so check yours first.

Make the data a table

Select your transactions and press Ctrl+T to turn them into an Excel Table. Name it Expenses in the Table Design tab. Use the headings Date, Category and Amount. Tables expand when you paste more rows underneath, which is what makes this setup low maintenance.

The three formulas

  • In F2: =UNIQUE(Expenses[Category]) lists each category once and spills down.
  • In G2: =SUMIFS(Expenses[Amount],Expenses[Category],F2#) totals each one. The # after F2 means the whole spilled list.
  • In I2: =SORTBY(F2#,G2#,-1) returns the categories ordered by total, largest first, and in J2: =SORT(G2#,,-1) returns the totals in the same order.
  • These need Excel 365 or Excel 2021. Excel 2019 and earlier will show an error.

A worked month in Canadian dollars

Say a month's spending was as follows. All numbers are invented.

CategoryAmount (CAD)
Rent1,600
Groceries540
Dining190
Transport140
Fun120
Other120
Phone70
Total2,780

Check: 1,600 + 540 + 190 + 140 + 120 + 120 + 70 = 2,780.

Filter to a single month

To restrict the total to one month, put the first day of the month in L1 and extend the formula in G2 to =SUMIFS(Expenses[Amount],Expenses[Category],F2#,Expenses[Date],">="&L1,Expenses[Date],"<"&EDATE(L1,1)). A category with no spending that month will show zero rather than disappear, which is helpful when you compare months.

Chart the spilled result

  1. Select the sorted categories in I2 and the totals in J2, including the cells that spill below.
  2. Choose Insert, then a clustered bar chart.
  3. Check the order. Excel draws bar charts bottom-up, so use Format Axis and choose categories in reverse order if the biggest bar is at the bottom.
  4. Add the month and total to the title.

What can go wrong

  • A #SPILL! error means something is sitting in the cells where the list wants to flow. Clear those cells.
  • Two spellings of one category will appear as two rows. Standardise them in the table.
  • Blank categories appear as a row with no name. Fill them in.

Why a Table beats a plain range

A plain range ends where you last looked at it. A Table grows when you add rows, and every formula that refers to Expenses[Amount] grows with it. That means the month-end routine is simply paste, check the total and look at the chart. If you do add a new category, UNIQUE picks it up on its own, SUMIFS totals it, and SORTBY places it correctly, with no edits to any formula.

  • Keep the Table on its own sheet, with nothing to the right of it.
  • Do not merge cells inside the Table.
  • Give the Table a short, memorable name.

If a chart refuses to pick up new rows, the usual cause is that it was built on a fixed range rather than the spilled one. Delete the series and add it again by clicking the first cell of the spill, so Excel records the whole spilled range. Test it by adding a made-up category to a spare row at the bottom of the Table, and watch the chart grow. Then delete the test row. This tiny experiment takes a minute and tells you whether the set-up will survive a real month.

Keeping it tidy

Paste each new month directly under the last row of the table and let it expand. Keep one sheet for raw data and another for the summary, so you never overwrite formulas by accident.

SPENDIXAI JOURNAL

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

Explore your reports