How to Make a Treemap Chart in Excel From Category Totals

A treemap chart in Excel shows how a total splits into parts, using rectangles whose size matches each value. It handles many categories, and two levels such as Category and Product, far better than a pie chart. This guide shows how to lay out the data, insert the treemap, total raw data by category with SUMIF first, and fix the problems people hit most. Treemaps need Excel 2016 or later, including Microsoft 365, on Windows or Mac.

What a treemap chart shows#

A treemap fills a box with rectangles, one for each item, sized by its value. Items in the same group share a colour and sit together, so you see both the groups and the items inside them at once.

Use a treemap when the question is "what makes up the total?" and there are too many parts for a pie chart. It is less useful for exact comparisons: people judge lengths better than areas. To compare a few values precisely, a bar chart is clearer. The chart basics guide explains how to match the chart to the question.

Step 1: Arrange the data for a treemap#

A treemap needs label columns on the left, from the highest level to the lowest, and one column of numbers on the right. This sales table is ready to chart:

Check three things before you insert the chart:

• One header row with a short name for each column.
• Real numbers in the value column. Numbers stored as text are left out of the chart.
• Positive values only. A treemap can't draw a zero or negative rectangle, so it skips those rows.

The rows don't need to be sorted or grouped. Excel groups them by the Category column and arranges the rectangles by size.

Step 2: Insert the treemap chart#

With the data in place, the chart takes three clicks:

1. Select A1:C8, headers included.
2. Go to Insert > Insert Hierarchy Chart (the icon with coloured rectangles) and choose Treemap. On a Mac, the same icon is on the Insert tab.
3. Drag the chart to where you want it and resize it.

You can also reach it through Insert > Recommended Charts > All Charts > Treemap. If you only select one label column and the values, you get a one-level treemap with no groups.

On this data, Hardware (86,000), Software (87,000) and Services (42,000) each get a colour, and Laptops is the single largest rectangle.

Step 3: Make the labels and colours readable#

The default treemap often needs clearer labels. Right-click a rectangle and choose Format Data Series, then look under Series Options:

• Label Options sets how the group names appear: Banner puts each category name in a strip across the top of its group, Overlapping prints it over the rectangles, and None hides it.
• To show values, click the chart, choose the + button (Chart Elements) or Chart Design > Add Chart Element > Data Labels, then in Format Data Labels tick Value as well as Category Name.
• Change the colour set from Chart Design > Change Colors.

Give the chart a title that states the point, such as "Software and Hardware make up 80% of sales", not just "Sales". (87,000 + 86,000 is 173,000 of 215,000, about 80%.)

Make a treemap from category totals with SUMIF#

When your data has one row per sale, total each category first and chart the totals. SUMIF adds the values in rows that match one condition:

=SUMIF(A2:A8,"Software",C2:C8)

The result is 87,000 (42,000 + 18,000 + 27,000). For a full summary, list each category once in E2:E4 and in F2 type:

=SUMIF($A$2:$A$8,E2,$C$2:$C$8)

Fill it down. The $ signs keep the data ranges fixed while E2 changes to E3 and E4. You get Software 87,000, Hardware 86,000 and Services 42,000, and a treemap of E1:F4 updates whenever the raw data changes. The guide to summing by category covers this pattern in more depth, and the SUMIF function guide has the syntax.

In Excel 2021 or Microsoft 365, =UNIQUE(A2:A8) lists each category once for you, so new categories appear by themselves.

Can you make a treemap from a PivotTable?#

Not directly: treemaps, like some other newer chart types, aren't available as PivotCharts. You have two good options:

1. Use a SUMIF summary, as above. It stays live when the data changes.
2. Copy the PivotTable and paste it as values (Windows: Ctrl+Alt+V; Mac: ⌘+Control+V, then choose Values), and build the treemap from the pasted range. You'll need to repeat this when the data changes.

A PivotTable is still the fastest way to see the totals. The guide to PivotCharts lists the chart types that do work straight from a PivotTable, such as column, bar and pie.

Treemap, pie or bar chart?#

Pick the chart by the number of parts and the question you're answering:

A pie chart with ten slices is hard to read; a treemap with ten rectangles is not. The guide to pie charts and when not to use them goes into the trade-offs.

Common treemap problems#

Most treemap problems come from the data, not the chart:

1. Treemap is missing from the Insert menu. You're on Excel 2013 or earlier. Use a bar chart instead.
2. A category is missing. Its value is zero, negative, blank or text. Treemaps only draw positive numbers.
3. The chart shows one colour. You selected only one label column. Include the higher-level column, such as Category, on the left.
4. Lots of tiny unreadable rectangles. Group small items into an "Other" row with SUMIF before charting.
5. Totals don't match the data. The SUMIF ranges are different sizes, or the category names have extra spaces. Clean names with TRIM.

Practise it#

A treemap is only as good as the totals behind it. Practise SUMIF in a live spreadsheet to total any column by category, then practise PivotTables to summarise data fast before you chart it. Each lesson checks your work instantly, and the exercise on this page builds the first category total.