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.

Figure 1 Data for Treemap and Sunburst Chart
Figure 1: Data for Treemap and 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.

Figure 2 Insert Hierarchy Chart Option
Figure 2: Insert Hierarchy 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.

Figure 3 Treemap Chart
Figure 3: Treemap Chart

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.
Figure 4 Sunburst Chart
Figure 4: Sunburst Chart

Again, the size of the rings are 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’s 3 tricks to help you work across Microsoft Excel worksheets.

FacebookTwitterGoogle+LinkedInEmail
mm
Dr. Wayne Winston is a Professor Emeritus of Decision Sciences at the Kelley School of Business at Indiana University. He holds a B.S. in Mathematics from M.I.T. and a PhD in operations research from Yale. He won over 40 teaching awards at Indiana University. He has written over a dozen books including "Marketing Analytics", "Data Analysis and Decision Making", "Operations Research", "Practical Management Science", "Excel 2019 Data Analysis and Business Modeling", and "Mathletics." Dr. Winston has taught classes and consulted for many leading global organizations. He is also a two-time Jeopardy! Champion and has consulted for the NBA’s Dallas Mavericks and New York Knicks.