ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week

Excel Tip of the Week #381 - Treemap and sunburst charts

Author: David Lyford Smith

Published: 15 Feb 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week. This week, we have a General User post in which we’re taking a look at two more unusual chart types that were added to Excel for the 2016 version: Treemap and Sunburst charts. These are both types of hierarchy charts designed to show layered categories of items.

Laying out our data

Both of these chart types are designed to show amounts or constituents of items in a layered hierarchy. For example, we could look at sales by country and product. In order for either chart type to work, we need to sort the data so that all the items with the same label are sorted together. Here’s an example:

screenshot1

We have a top-level category (country), an item-level category (product), and a value for each. Again, we need to sort this chart to keep the chart working properly.

Note that Treemap charts can only really handle two layers – but Sunburst charts can have more. We’ll explore that later.

Making and customising our charts

From the above data, this is what we get from Insert => Hierarchy Chart => Treemap:

excel screenshot2

A treemap shows a rectangle for each subcategory’s value, with the area approximately proportional to the value. The rectangles are grouped together into the categories in the original chart. We can tidy this up a bit by removing unnecessary labelling and adding values to the labels with a right-click.

Another neat feature of Treemap charts is that they automatically redraw if we resize the window. Here’s our finished product:

excel screenshot 3

You can see this chart in the accompanying file.

We can make a sunburst chart in the exact same way. This is a ring-based version of the same basic concept:

Excel screenshot 4

Going further

One simple extension we can do with the Sunburst chart is to add another level of detail.  Here’s the same data but with two years’ data included:

excel screenshot

Of course, you can also reorder the columns and data to change how the chart works.

You can also use Insert => Slicer for both kinds of chart – this will give you a quick and easy way to filter which data is included in the chart.  You do need to format the source data as an Excel Table for this to work.

Excel screenshot
Check out all these charts in the accompanying file.
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250