How to use Treemap Charts in Excel to Plot Simple Hierarchical Data

AUTHOR:  JASON MORRELL 

Jason Morrell

Reading Time:
  minutes remaining

Excel offers 2 types of Hierarchy chart: Treemap Charts and Sunburst Charts. Both work with hierarchical data, but Treemaps are restricted to 2 levels while Sunbursts can work with any number of levels.

Treemap Chart

In this post, you'll see how to set up your data, create a treemap chart and then style it to reflect the data is the clearest way.

1. How to set up your data for a treemap chart

Consider the hotel restaurant data.

  • Meal is the top category.
  • Type is the sub-category.
  • Item is the sub-sub-category

On a Treemap, only the Meal (first column, highest level) and Item (penultimate column, lowest level) will be represented.

The Volume (last column) determines the size of each block on the treemap chart.

Treemap Chart

2. How to create a Treemap chart

To create a treemap chart:

1.  Select a single cell in your data to allow Excel to select the entire range or select the headings and the specific data range you wish to use.

2.  Click the Insert tab.

3.  Select the Insert Hierarchy Chart icon in the Charts group and select Treemap.

Treemap Chart

The Treemap is arranged as follows:

  • The top-level item (breakfast or lunch) is colour-coded. The item label is displayed in the top-left of its coloured section. It can also be displayed in the legend. 
  • The lowest-level item (food) is represented in different sized blocks (based on volume) within each colour group.
  • The middle-level item(s), in this example, just 'type', are not shown on a Treemap. If you need to show some middle levels use the Sunburst chart instead.
Treemap Chart

3. How to restyle a treemap chart

It is always worth checking out the Chart Styles and Quick Layouts on the Chart Design ribbon for pre-built style and layout configurations (which you can still modify if desired).

Treemap Chart
Treemap Chart

4. How to switch the legend on or off

1.  On the Chart Design ribbon, click Add Chart Element.

2.  Hover over Legend, click the little options-arrow and choose where you would like to position the legend.

5. How to choose where and if to display the top-level items on the Treemap

1.  Double-click anywhere on the Treemap to display a sidebar containing all appropriate formatting options.

2.  At the top of the sidebar, click the options drop-down arrow and select Series xyz, where xyz is your numerical data.

3.  Click the 3rd icon (if necessary) to display the Series Options.

4.  Under Label Options, choose None if you are displaying the items in the legend, or choose Overlapping or Banner to display labels on the Treemap.

6. How to control what is displayed on the Data Labels on each element on the chart:

1.  In the sidebar, select Data Labels from the Options drop-down menu. Or Click  on the top-right edge of the chart, hover over Data Labels, click the little right-arrow that appears and choose More Data Label Options.

2.  Tick the Category Name and/or the Value boxes.

7. What next?

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Check the RELATED articles below

Read More
The Best Way to Create Drop-Down Lists in Excel That Are Easy to Maintain, Re-use and Put YOU in Full Control
Read More
5 Excel Functions for Beginners: Simplify Your Formulas and Release the Magic
Read More
Excel Formulas for Beginners: Bring Your Spreadsheet to Life
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>