Spreadsheets

Treemap chart of spending categories in Excel

Build a treemap of spending categories and subcategories in Excel from a flat table, and learn when area comparison is hard and a bar chart is better.

Editorial illustration for Treemap chart of spending categories in Excel

To make a treemap of spending in Excel 2016 or later, lay out one row per subcategory with columns for Category, Subcategory and Amount. Select the three columns and choose Insert, Hierarchy Chart, Treemap. Excel draws each category as a block, divided into its subcategories, with area showing the amount.

Prepare the data

A treemap needs a flat list, not a pivot. Each row is one subcategory with its total spending for the period, and the parent category repeats on each row.

CategorySubcategoryAmount (₹)
HomeRent18,000
HomeMaintenance3,000
HomeElectricity2,400
FoodGroceries9,000
FoodDining out4,500
FoodFood delivery2,500
TransportFuel3,600
TransportCab rides1,800

These are made-up figures for one month. Home adds up to 23,400, Food to 16,000 and Transport to 5,400, giving 44,800 in all. Rent alone is about 40 per cent of the total, so it will be the biggest block.

Create the chart

  1. Sort the list by Category, then by Amount from largest to smallest.
  2. Select the three columns, headers included.
  3. Choose Insert, then the Hierarchy Chart icon, then Treemap.
  4. Click the chart, choose Format Data Series, and set Parent Labels to Banner so each category name sits over its block.
  5. Turn on data labels showing the category name and value, and remove the legend.

Each category gets its own colour, and its subcategories share that colour in lighter shades. Excel places the largest blocks towards the top left.

If the Treemap option is missing or greyed out

  • Excel 2013 and older versions do not include it.
  • Newer chart types such as treemap generally cannot be built directly from a pivot table. Copy the pivot's values to a plain range and chart that.
  • Values must be positive. Refunds or credits entered as negatives are not drawn properly, so net them off first.
  • A hierarchy chart cannot be combined with another chart type.

Is a treemap easy to read?

Less than people expect. The eye compares lengths far better than areas. Home against Transport is obvious, but telling 4,500 from 3,600 is hard, and small blocks lose their labels. A treemap shows share of the whole and the nesting of subcategories well. It does not show precise differences or change over time.

If your numbers come from a pivot, flatten them first. Put Category and Subcategory in Rows, choose Design, Report Layout, Show in Tabular Form, then Repeat All Item Labels, and turn Subtotals and Grand Totals off. Copy the result and use Paste Values into a plain range. The treemap can then read the repeated category on every row.

Treemap, pie or sunburst?

ChartGood forWeak at
TreemapMany items, two levels, share of totalExact comparison, change over time
PieFour or five parts of a wholeMany slices, subcategories
SunburstShowing the nesting as ringsReading outer rings; labels clutter quickly
Sorted bar chartComparing precise valuesShowing hierarchy

Colours deserve a second look. Excel assigns one colour per category, which is helpful, but the lighter shades can be hard to tell apart when printed in black and white. If you plan to print the chart, check the preview, and rely on the labels in each block rather than on colour alone.

Keep it useful

Chart one period, such as a month, and say so in the title. Limit it to ten or fewer subcategories, grouping the rest into Other. And look at the biggest block first: if it is rent or an EMI, you may get more insight by charting the flexible categories alone.

If your categories and sub-categories already live in SpendixAI, its custom categories can be set up with colours, so a chart built from a CSV download keeps the same grouping you use elsewhere.

SPENDIXAI JOURNAL

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

Explore your reports