Dr. Winston's Excel Tip: How to Summarize Data with Treemap and Sunburst Charts in Excel

Treemap charts, also known as mosaic charts, and Sunburst charts were added to Excel to help you visualize hierarchical data. Hierarchical data is a way to organize data with multiple one-to-many relationships. The structure is based on the rule that one parent can have many children, but children can have only one parent.

For example, if I look at the sales of a product based on quarter of year, month of year, and week of month, then the quarter is the parent with three children (the months in the quarter), and each month has either four or five children, corresponding to the weeks in the month.

As another example, consider a small bookstore. The parents, in this case, might be types of books (like children’s books, art and photography books, and so on). The children of the art and photography category might be crafts and coffee-table books. For children’s books, the children might be books in categories such as Age 3-5 and Age 6-8. Age 3-5 books might have subcategories of first-readers, ABCs, and Tolstoy for Tots.

The file Beckerseptember19.xlsx (see Figure 1) contains hierarchical data that we can use to create a Treemap or Sunburst Chart.

This file contains a bookstore’s revenues from different types of books. Column A contains the most aggregated category, Genre. Column B contains Sub-Genres for each genre. For Children’s books, this includes Baby Books, Age 3-5, Age 6-8, Pre-Teen and Teen. In Column C, some Sub-Genres are broken down by topic. Finally, each line of Column has the revenue for each type of book.

Treemap Chart

Here’s how to create a Treemap chart that summarizes sales of each product.

1. Select the range A1:D29 in the Treemap worksheet and choose the Insert Hierarchy chart icon shown in Figure 2.

2. Select the Treemap chart option.

3. You now obtain a Treemap chart. Right-click on the chart and select Format Data Series. Then, choose Banner labels.

4. Select Chart Design, Chart Element, Data Labels, More Options and then check Values, so our chart shows the sales values. The resulting Treemap chart is shown in Figure 3.

The size of each product’s rectangle is proportional to the product’s sales. The chart makes clear that Children’s books are the bestselling category.

Sunburst Chart

A Sunburst chart is an alternative way to summarize hierarchical data. A Sunburst chart represents sales with a ring or circle.

Here’s how to create a sunburst chart.

1. Select the cell range A1:D29 in the worksheet Sunburst.
2. Select the Insert Hierarchy chart icon and choose Sunburst chart.
3. Insert data labels using the same procedure as the Treemap chart. The resulting Sunburst chart is shown in Figure 4.

Again, the size of the rings is proportional to the total sales.

The strength of the Sunburst chart is that it shows how a ring breaks down into its component pieces. A Sunburst chart is weak at showing the relative size of product sales. On the other hand, the Treemap chart excels at showing the relative size of product sales but is often not good at showing how a larger rectangle breaks down into its component products. If your company sells many product categories, you will love these charts!

Ready to learn other Excel tips? Here are 3 tricks to help you work across Microsoft Excel worksheets.