Spreadsheets

Stacked column chart of monthly spend by category in Excel

Build a stacked column chart of monthly spend by category in Excel from a SUMIFS grid, order the series sensibly and know when 100% stacked reads better.

Editorial illustration for Stacked column chart of monthly spend by category in Excel

A stacked column chart shows each month as one bar made of coloured category blocks, so you see the month's total and its mix together. In Excel, build a month-by-category grid with SUMIFS, select it, then choose Insert, Column or Bar Chart and Stacked Column.

How do I build the grid the chart reads from?

Put your entries on a sheet named Data: date in A, category in C, amount in D. In column E add a month key with =EOMONTH(A2,-1)+1, which returns the first day of the month. On a Chart sheet, write categories down A2:A7 and month starts across B1:G1 (01-Apr-2026, 01-May-2026 and so on, formatted mmm-yy). In B2 enter:

  • =SUMIFS(Data!$D$2:$D$1000,Data!$C$2:$C$1000,$A2,Data!$E$2:$E$1000,B$1)

Fill right to G and down to row 7. Add an eighth row called Other with the total minus the six above, so the stack always reaches the real total. Six categories is about the limit before colours blur together.

How do I insert and fix the stacked column chart?

  1. Select A1:G8, including the header row and the first column.
  2. Choose Insert, then Column or Bar Chart, then Stacked Column.
  3. If months appear as the series and categories along the bottom, click Switch Row/Column on the Chart Design tab.
  4. Add a title and remove gridlines you do not need.

Excel guesses which direction is the series by comparing row and column counts. With six categories and six months it can guess either way, so check the legend: it should list categories, not months.

Which series goes at the bottom of each stack?

The first series in the data order sits at the base. Put the steady, large category, usually Rent, first, so it forms a flat floor and the changing categories ride on top where their ups and downs are visible. To reorder, open Chart Design, then Select Data, choose a series and use the arrows. Place the legend on the right and check that its top-to-bottom order matches the stack, which Excel normally arranges.

Stacked or clustered: what is the difference?

Chart typeShows wellHides
Stacked columnMonthly total and its mixExact size of upper segments
Clustered columnCategory against category in a monthThe overall monthly total
100% stacked columnShare of each category per monthAbsolute rupee amounts

A hypothetical illustration: April spend of ₹30,000 includes ₹18,000 rent, so rent is 60 percent. In October, a festival month with ₹10,000 of extra shopping, the total is ₹40,000 and rent is the same ₹18,000, now 45 percent. A plain stacked chart shows October is taller. The 100% version shows rent shrinking as a share. Both are true; they answer different questions.

When should I switch to 100% stacked?

Use it when you are asking whether your pattern is changing, for example whether food has crept up as a share of what you spend, and the total matters less. Right-click the chart, choose Change Chart Type, pick 100% Stacked Column and add data labels, which then show shares if you format them as percentages from the source.

One more tip for the grid: add a Total row under the categories with =SUM(B2:B8) and compare it to the sum of that month's entries on the Data sheet. If the two differ, a transaction has a category that is not in your list, and it is missing from the chart. Fix the category spelling rather than the chart.

Two small finishing touches help. Format the vertical axis numbers as thousands with the custom code #,##0, and keep the axis starting at zero, because a stacked column that starts at ₹15,000 makes every month look wildly different. Then add the month total above each column by inserting a Total series, changing it to a line with no line colour and turning on data labels.

A shorter route

If building this grid every month feels like a chore, the reports in SpendixAI group spending by category from imported statements, and a CSV download is there if you still want Excel for the chart.

SPENDIXAI JOURNAL

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

Explore your reports