To set up data for a Sunburst chart in Excel, put each level of the hierarchy in its own column, list one complete path per row, and place the numeric value in the last column. Select the whole range, including its headings, then choose Insert > Insert Hierarchy Chart > Sunburst. The result is a set of nested rings that shows how individual values contribute to their parent groups.
For example, a sales report might use Division, Team, Item, and Sales columns. Division forms the center ring, Team the next ring, and Item the outer ring. Prepare the hierarchy carefully before inserting the chart: Excel uses the order and repeated labels in the source rows to work out which segments belong together.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. Sunburst charts are also available in supported desktop versions of Excel; ribbon labels and locations can differ slightly on Mac.
- A worksheet range with one column for each level of the hierarchy and one final column containing the numeric values to chart.
- A clear definition of what each value measures, such as units sold, hours, or revenue. For money figures, use a consistent currency and period.
- Clean source data: one row per lowest-level item, consistent category names, no blank hierarchy cells, and no totals mixed in with detail rows.
Step-by-Step
- Choose the hierarchy you want to show. Write down the levels from broadest to most specific. For a product report, that might be Department, Category, Product. For a location report, it might be Country, State, City. The broadest level becomes the innermost ring, and each more specific level adds another ring outside it. Include only meaningful levels; a chart with many levels can become difficult to read.
- Set up one column for each level and one for the measure. In a blank worksheet, enter a heading for every hierarchy level, followed by a heading for the values. For example, use Division in column A, Team in B, Item in C, and Sales in D. Keep the headings in a single top row. The values column should contain actual numbers, not numbers stored as text or labels such as $120 typed into the cell as text. Format numeric values as currency or another appropriate number format after entering them.
- Enter one complete path per detail row. Each row should identify the full route from the top-level group to a leaf item. For instance, a row for a kettle should include Home, Kitchen, Kettle, and 120. Repeat Home and Kitchen on every applicable row rather than leaving those cells blank after the first entry. Repeating labels makes the relationship explicit and gives Excel a complete hierarchy for each value. Do not insert a heading row between groups.
- Check the example against your intended structure. A small source range could look like this:
| Division | Team | Item | Sales |
|---|---|---|---|
| Home | Kitchen | Kettle | 120 |
| Home | Kitchen | Toaster | 80 |
| Home | Cleaning | Vacuum | 300 |
| Outdoor | Garden | Hose | 90 |
| Outdoor | Garden | Shears | 60 |
| Outdoor | Camping | Lantern | 210 |
In this example, Home totals 500 and Outdoor totals 360. Kitchen totals 200, Garden totals 150, and Camping totals 210. Those parent totals are represented by their child rows; you do not need to add separate rows containing the parent totals. The chart uses the leaf values to size the segments and show how each item contributes to its team and division.
- Check that labels and values are consistent. Scan for misspellings or extra spaces that could split what should be one group into two, such as Kitchen and Kitchen followed by a space. Make sure every detail row has a value and every value is numeric. Avoid blank rows within the data range, merged cells, and extra notes or grand totals beneath the records. If the source is long, use Excel’s filters or sort the range by the first hierarchy column to make inconsistencies easier to spot.
- Decide how to handle repeated paths. Ideally, each combination of hierarchy labels ends in one row. If a product appears on multiple transaction rows, summarize the transactions by Division, Team, and Item first, then chart the grouped totals. Otherwise, the chart may show multiple segments with the same path, which can make it harder to interpret. Do not add both the detailed item rows and a separate subtotal row for the same items; that counts the same amount twice.
- Select the complete data range. Click a cell in the data and press Ctrl+A to select the current contiguous range, or drag to select the headings and all detail rows. On Mac, use Command+A when the active cell is inside a contiguous data range. Check that the selection includes all hierarchy columns and the numeric values, but not unrelated notes, an extra total, or an empty column. If the data is stored as an Excel table, you can select a cell in the table and use its full range as the chart source.
- Insert the Sunburst chart. On Windows, choose Insert > Insert Hierarchy Chart > Sunburst. Depending on the window width, the hierarchy chart control may be grouped or displayed as a chart button on the Insert tab. If you do not see the direct option, open Insert > Recommended Charts, select the All Charts tab, choose Sunburst, and confirm with OK. On Mac, go to the Insert tab and look for the Hierarchy chart options, then choose Sunburst; the exact grouping can vary by Excel release. If Sunburst is not offered, use a supported desktop Excel version rather than assuming a regular pie chart will create the same hierarchy.
- Read the rings and check the result. The first hierarchy column should be closest to the center. Each subsequent label column should appear farther out, with the value-based leaf segments on the outside. In the example, Home and Outdoor are inner segments, their teams are in the next ring, and the individual products are outer segments. Check that the relative sizes make sense: Home should be larger than Outdoor because 500 is greater than 360. If the chart looks structurally wrong, fix the source layout rather than trying to repair the grouping by manually rearranging chart pieces.
- Make the chart easier to understand. Select the chart and use Chart Design to change its style, colors, or chart elements. Add a descriptive chart title, such as Sales by Division, Team, and Item. If you want labels, use the chart elements control or Chart Design > Add Chart Element > Data Labels, then review whether the labels remain legible. A chart with many small outer segments may become crowded; consider filtering to a smaller period or grouping minor items into an Other category before charting.
- Keep the source and chart together. Save the workbook after checking the chart and its source range. If you expect to add rows, format the source as an Excel table first using Home > Format as Table, then insert the chart from the table data. Table-based sources are generally easier to extend than a fixed range, but verify the chart includes new rows after adding them. Excel for the web may display or edit many charts, but if the Sunburst command or chart options are missing, open the workbook in the desktop app.
Common Mistakes
Leaving parent labels blank: A visually grouped worksheet with a label only on the first row is not a reliable hierarchy source. Repeat every level on every detail row. Adding subtotals as if they were details: If the source includes a team total plus all its products, the chart can count both the total and the products, inflating the parent segment. Keep either the leaf records or a properly summarized set, not both.
Putting values in the wrong column: Excel expects the hierarchy labels first and the numeric measure last. A text note or category after the measure can be treated as another hierarchy field, while text in the measure column may not size segments correctly. Using inconsistent names: Small differences such as North-East versus Northeast create separate categories. Standardize spelling, punctuation, and spacing before building the chart.
Including blank rows or unrelated cells: Selecting too much can bring notes, a grand total, or empty fields into the chart source. Select only the header row and the detail records. Also avoid negative measures for a Sunburst chart; this chart type represents parts of a whole and is intended for nonnegative values. Investigate negative values rather than silently converting them to positive amounts.
Choosing too many levels or labels: A deeply nested chart with many tiny segments can be difficult to scan. A Sunburst is best when the hierarchy is meaningful and the number of categories is manageable. A sorted table or another visualization may communicate a very long list more clearly.
Tips
- Use short, descriptive headings. They make the source range easier to review, and help you remember which ring each column controls.
- Keep the source data in a separate, tidy block from explanations and report notes. This makes selecting the chart range less error-prone.
- Use a consistent unit and reporting period for every row. Comparing dollars with units, or mixing monthly and annual figures, makes the segment sizes misleading.
- Sort the rows by the hierarchy columns before charting. Sorting is not required for the chart to recognize the parent-child structure, but it makes the source easier to inspect and maintain.
- For a large transaction list, summarize it by the hierarchy fields and sum the measure before creating the chart. A PivotTable can help create that summary; confirm its output is a clean hierarchy-and-value layout before selecting it for a Sunburst.
- Keep the original detailed data intact. Build a separate summarized range for the chart so you can revise the grouping without losing the records that produced the totals.
Troubleshooting
The Sunburst option is unavailable. Confirm that you are using a desktop Excel version that supports hierarchy charts and that you have selected a rectangular range containing text labels and numeric values. If you are in Excel for the web or using an older release, open the workbook in Microsoft 365 desktop Excel or update Excel if your account permits it. The ribbon can collapse controls when the window is narrow, so maximize the window or check the Insert tab’s chart menus.
The chart has unexpected rings or labels. Inspect the source columns from left to right. Every label column before the final measure column is interpreted as a hierarchy level. Remove accidental columns, blank headings, and notes from the selection. If the rings are in an unexpected order, rearrange the source columns so the broadest level is first and the most specific is last before the values.
A parent appears more than once. Check for inconsistent spelling, hidden spaces, or different punctuation in its rows. Correct the source labels so identical groups use exactly the same text. If the same complete path appears on multiple rows, summarize those rows into one value for that path when they represent parts of the same item.
Some segments are missing or too small to see. Confirm their values are numeric and greater than zero, and check that the cells were included in the selected range. Very small positive values can be difficult to distinguish next to much larger ones. You can narrow the reporting period, group minor categories, or use data labels where they fit. Do not change values merely to make segments more visible.
The chart sizes do not match your expected totals. Look for duplicate records, subtotal rows, or a total row included with the detail. Sum the relevant leaf values independently and compare those sums with the chart’s parent totals. If a value is stored as text, convert it to a number and refresh or recreate the chart as needed.
Frequently Asked Questions
Can I make a Sunburst chart with only two hierarchy levels?
Yes. For example, use Department, Product, and Sales. Excel can show the department and product levels; the hierarchy does not have to contain three or more label columns. With fewer categories, the chart may be easier to read.
Do I need to enter totals for each parent category?
No. Enter the values at the lowest level you want to display. Excel uses those values to size the parent segments. Adding parent totals alongside the child records can double-count the amounts.
Should I repeat category names on every row?
Yes. Enter the full hierarchy path on each detail row, including repeated top-level and middle-level labels. Do not leave cells blank just because the label matches the row above.
Can a Sunburst chart use text instead of numbers for its values?
No. The final column should contain numeric values that Excel can use to size the segments. Check that imported numbers are not stored as text, and keep labels in the hierarchy columns rather than the measure column.
Can I include negative values?
Sunburst charts are designed to show hierarchical parts of a whole, so use nonnegative values. If the measure can be negative, such as net change, choose a chart type that represents positive and negative values clearly or prepare a clearly defined nonnegative measure instead.
Why does Excel show a separate segment for what should be one category?
The labels may not match exactly. Check for spelling differences, trailing spaces, inconsistent abbreviations, and punctuation. Standardize the source text, then update the chart range or recreate the chart if needed.
Related
- Save an Excel Workbook as a Copy Without Replacing It
- How to Save As in Excel and Create a New Copy
- How to Save Excel Files to Google Drive
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Best Excel Chart for Comparing Data: 5 Top OptionsFor most comparisons between categories, the best Excel chart is a clustered column chart: it places values side…
- Data Mapping in Excel for Org Charts: A Practical GuideTo map employee data in Excel for an org chart, make a table with one row per person…
- Excel Histogram Custom Bins: How to Set ThemIn Excel for Microsoft 365 on Windows, select your numeric data, choose Insert > Insert Statistic Chart >…
- Excel Power Pivot vs PivotTable: Differences, Uses, and ChoiceExcel PivotTables summarize and explore data; Power Pivot adds a data-modeling layer for connecting tables, defining relationships, and…
- Excel Bar Graph Conditional Formatting: Color by RulesExcel does not apply worksheet conditional formatting rules directly to the bars in a chart. To make bar…
- Circle Bullet Chart in Excel: A Step-by-Step GuideCreate a circle bullet chart in Excel by plotting an actual value and the unused portion of a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.