Excel Sunburst Chart
Sunburst chart is a nice way of presenting relational datasets together in a compact form. This chart type is very strong in summarizing data but can be a little hard to read. Nevertheless, it looks pretty. That’s why I am going to show you how to make an Excel Sunburst Chart.
Sunburst chart is basically a stacked pie chart. It is created by stacking necessary amount of pie charts on top of each other. But there is a couple of tricks to do in order to make a decent one.
As an example, I am going to visualize project teams for a fictional software project. Here are the steps for creating an Excel Sunburst Chart for visualizing project teams:
This is our project team data. It is not very useful in this state. So we need to make some arrangements to make it suitable for a sunburst chart.
Here is the arranged version of same data. I made 3 groups from this data as Project manager, groups and team members. I assigned identical values to each team member to be able to plot them as visible data series. Team names gets the total value of their members. And project manager gets overall total value.
Now that our data is ready, let’s start building our chart:
- Insert a pie chart for each data groups. To insert pie chart, select cells; Project manager B1:C1, groups B3:C9 and team members B11:C44. Than insert a pie chart each time.
- Delete any legend, title, etc. on charts.
- Set background fill color as “no fill” and border color as “no color” for project manager and group charts.
- Resize charts arranging team members as largest and project manager as smallest.
- Place 3 charts on top of each other and align using lines between slices. We are doing this to get a feeling of how things are going to look like when finished. This is how it is supposed to look like at this stage:
- Now add data labels to each pie and select appropriate option from label options to display PM, group and member names. You may need to move charts to apply these changes. It is not a problem, we will re-stack them again anyway.
- Now that we have our labels displayed correctly, we need to reposition them to fit into their respective slices. For project manager and groups, just reposition labels by dragging them.
- For group members, you shouldn’t move labels. Make sure “Best Fit” is selected for label position. Select each label and adjust its alignment value from label options until it fits into related slice. Excel will position it inside the slide when it has a suitable alignment value.
- Re-stack pie charts when you are happy with labels.
- Now adjust colors of slices as you like. Coloring from lighter tones to darker tones and coloring related slices with same color is a good style. I colored each group with a separate color. Then I colored each member with a darker tone of same color as the group they belong to.
- Add a chart title if you like.
You can download example workbook for this Excel Sunburst Chart and inspect it yourself. You can also leave a comment below if you have any question regarding this tutorial or just to say hi 🙂
Hi Melih: I like your style, and think I understood. But what I need in addition to what you’ve explained (and before I begin my own sunburst graph) is a dynamic element, whereby I can click on any segment of any layer and obtain information about whatever subject is presented, in your example, an employee, but in my case, definitions of elements of poetry, or rhetoric, for example. Can I do this on excel and if so, how?
Only way I can think of is to use VBA for what you need.
Since these charts are on top of eachother, you may not click on every slice (especially lower ones).
So you can save the chart as apicture and place transparent boxes on each slice. Than code some complex macro to display info when a box is selected or when mouse hovers over it.
I can’t think of an easy way, sorry.
Thanks Melih. I’ll do as you suggest, although the code thing is a bit scary: I’ll have to learn. let me know if you have a ‘eureka’ moment about how to do the thing easily.
Simply superb! Easy to follow. I did one within 5 minutes… Thanks a TON!
Hi Melih, great description about sunburst. Do you know a way, which makes it possible clicking a cell with a defined color and the suitable Segment dyes the same color ? I know that i have to create a Makro respectivley a VBA- Code, but i can’t name the segments in the “code-language”. At old charts the Name is “Points”.
hi, i am unable to get Legend Name near to respective area in chart and also unable to format part of chart in different colour. I am not getting it easily. Plz guide me.
Or can you share your you tube link if it is recorded how to it?
Unfortunately I didn’t record the process of building this chart.
I downloaded and confirmed that I can move the legend anywhere within the chart just by dragging it.
Did you enabled editing from the yellow band over the screen or options?
Just wanted to say Thank You for posting. The other online examples require JSON files, or Excel 2016 (which I have no desire to download). This was for demonstration purposes, to show what could be done with the chart type, and worked beautifully. Thanks again!
How to create sunburst chart in excel 2013
Hi. This is the way to create sunburst chart in excel 2013 and prior. You can also read about creating sunburst chart with excel 2016 in charts section.
how to do the color grouping in the team member chart?
Just select the pie and set a color. That’s all.
I like your style.
Its simply superb!!
Thank you so much for sharing!
Do you know if it is possible to rotate/align labels with the circumference, rather than pointing towards the center?
I think this is my problem too!
Thanks for the great idea! A lot of people were impressed at work:
Hi, Is there a possibility of displaying percentages of each segment? Or maybe a predefined message?
Hi, you can try to change column C values to percentages and choose to display them during step 6.
Do you know how to change the order of the middle segments? I’m trying to put information in there that has to be in a certain order but the order keeps showing up randomly.
Hi. You may try to rearrange your data for this.
outstanding! Thanks so much.
Hi, I just landed here and see how nice you are to share your knowledge to the world. Thank you and hoping you success now and in the future and keep in a good way to share your practice and knowledge to the world.
Thank you so much for sharing this! My data comprises of multiple words in each instance (where you have the group members). While there is enough space in the ‘slices’ to accommodate the data, it doesn’t align with the slices. They’re not positioned in a parallel way. Not sure how to fix it. Any idea? Thanks!
man you are awesome