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.
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.
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.
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.
The Treemap is arranged as follows:
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).
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?
I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.
Have a fantastic day.
About the author
Jason Morrell
Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE