Brighten up hierarchical data with a Sunburst chart in Excel 2016


by Kara Hiltz

Application: Microsoft Excel 2016

Operating System: Microsoft Windows


Presenting hierarchical data can prove difficult when you want to see how various categories relate to one another. One of Excel 2016’s new chart types, the Sunburst chart, offers an effective way to visually represent hierarchical categories nested within one another.

To make the most of a Sunburst chart, we’ll:

  • Set up data so it’s ready to transform into a Sunburst chart.
  • Create a Sunburst chart in Excel 2016.
  • Highlight one portion of our hierarchical data using the Sunburst chart.

Nested categorical data can get complicated, which makes displaying it as a chart a messy venture. For instance, you may have several years of data organized into quarters, months, and weeks. Or you might have sales figures for individuals, who make up various departments at various store locations. Excel 2016’s Sunburst chart is a powerful option for organizing and displaying hierarchical data, as shown in Figure A.

In this article, we’ll show you how to take advantage of this new chart type. We’ll demonstrate using an example in which a store that sells books, movies/music, and includes a coffee shop wants to compare sales in each of these areas across four quarters and in two city locations.


A: The Sunburst chart may look like a supercharged, multilayered pie chart.

Get your data ready

In our example, we’ll start with our top layer of data, the city in which the store is located in our leftmost column—followed by the quarter, the department, and the sales data, as shown in Figure B.

Download: To follow along with our example, just download and extract the file quarterlysales.xls from the URL given at the beginning of the article.

You may notice in our data table that there are many empty cells. Instead of repeating categories in the column to the left, we’ve only included cells that have new data. While this kind of data setup wouldn’t fly with a PivotTable, for a Sunburst chart, it will work just fine.


B: Note that our data includes some empty cells—and that’s okay.

Create the Sunburst chart

Once you’ve organized your data with the top layer of your hierarchical data to the left and the subsequent layers in each column moving right, you’re ready to create a Sunburst chart. It will only take you a few clicks to get there.

To produce a Sunburst chart:

  1. Select any cell within your data table.
  2. Go to the Insert tab and, in the Charts area, click on the Insert Hierarchy Chart button Insert Hierarchy Chart.
  3. Choose the Sunburst option from the dropdown list, and you’ll see the preview of the chart display in your worksheet, as shown in Figure C.

Excel’s default chart can use some fine-tuning. You might want to create a better chart title and resize the labels so that they all fit within the data point sections of the chart. We double-clicked on the Chart Title text to replace it with Quarterly Sales by Department for Austin and Dallas Locations. Resizing the chart is as simple as clicking and dragging the corner resizing handles that appear when you select the chart.


Join NowClose
Return to the ExcelSkillsSociety's homepage