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
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.
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.
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.
Select the cell range A1:D29 in the worksheet
Select the Insert Hierarchy chart icon and choose
labels using the same procedure as the Treemap chart. The resulting Sunburst
chart is shown in Figure 4.
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!
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.