Spreadsheets

Stacked column chart of categories by month: build and read it

Build a stacked column chart of expenses by month in Excel or Sheets, learn when it shows category mix, and how it compares with 100 percent stacked.

Editorial illustration for Stacked column chart of categories by month: build and read it

A stacked column chart puts one column on each month and divides that column into coloured blocks, one per category, so the column height is total spending and the blocks show where it went. Use it when you want to see the mix of categories change over months. Use a different chart when you need to compare one category's movement precisely.

How do I build it from my expense log?

First make a small table with months down the side and categories across the top. Say the log has Date in A, Category in B and Amount in C, the month's first day in A12 and the category name in B11. Each cell of the table is one SUMIFS:

=SUMIFS($C$2:$C$500,$B$2:$B$500,B$11,$A$2:$A$500,">="&$A12,$A$2:$A$500,"<"&EDATE($A12,1))

Fill it right and down. EDATE($A12,1) is the first day of the next month, so every day of the chosen month is caught. Then select the whole table including headings and insert the chart:

  • Excel: Insert, then Charts, then Column, then Stacked Column.
  • Google Sheets: Insert, then Chart, then Chart type, then Stacked column chart.
  • If months and categories come out swapped, use Switch rows and columns in Excel, or tick Switch rows and columns in the Sheets chart editor.

When does a stacked column help?

  • When the question is how the mix of spending changed, such as whether eating out grew as groceries shrank.
  • When total height matters as much as the blocks, for example in a month with a festival or a school-fee quarter.
  • When there are no more than five or six categories. Fold the small ones into Other first.

Stacked columns are weaker for exact comparisons. Only the block at the bottom starts on the same baseline in every month, so a category in the middle floats, and your eye has to judge its length against a moving start. A practical fix is to put the steadiest category (rent or EMI) at the bottom and the one you care about most just above it, or to draw that single category as its own line chart.

What does a 100 percent stacked chart change?

It rescales every column to the same height, so the blocks show share of the month, not rupees. That is good for mix, and misleading for money. Take two months with made-up numbers:

CategoryApril (₹)April shareMay (₹)May share
Rent18,00060.0%18,00051.4%
Food7,00023.3%9,00025.7%
Transport3,00010.0%3,0008.6%
Other2,0006.7%5,00014.3%
Total30,000100%35,000100%

In the 100 percent chart, the rent block shrinks from 60.0 percent to 51.4 percent, yet rent was ₹18,000 both months. The share fell only because other spending rose from ₹12,000 to ₹17,000. Anyone reading the block alone could think rent came down. In the ordinary stacked chart the rent block is the same height in both months and the extra height in May is plainly in Other and Food.

Which of the two should I use?

Question you are askingBetter chart
How much did I spend in total each month?Stacked column
Did any category grow in rupees?Stacked column, or a line chart for that category
Is the pattern of where money goes shifting?100 percent stacked
Month sizes differ a lot (a travel month versus a quiet one)Show both and label them clearly

How do I stop the chart from becoming unreadable?

How do I read it once it exists?

  1. Look at the total height first and find the tallest and shortest months.
  2. Ask what you remember about those months (a wedding, a repair, a trip) before reading any block.
  3. Check the bottom block to see whether the fixed cost really is fixed.
  4. Pick one block that changed and look at its numbers in the table, not its length on the screen.

If you prefer not to build the table by hand, SpendixAI has a library of ready-made reports and a Report builder where you filter and group your spending, and you can download the result as a CSV and chart it here.

SPENDIXAI JOURNAL

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

Explore your reports