Draw sunburst chart with multiple columns

I have an Excel document that looks somewhat like this:

I'd like to put Type and Amount into a pie chart. Furthermore I want to split each pie section into its other 3 columns, Days without, Days with little and Days with lots.

Sunburst seemed like a good choice to me. However for it to work I need to restructure it into some hierarchical table.

This gives me the result I'd expect:

I have a large dataset however and converting it all into some hierarchical data seems way too inefficient to me. Is there an easier method to get this done?

1 Answer

Reformatting data for a sunburst chart is a bit awkward, but here's a way to cut down some of the tedious steps.

First, use Data>Get & Transform Data>From Table/Range.

This will open a query against your source data:

Select the three right-most columns and use Transform>Unpivot Columns, so you get this:

Use Add Column>Custom Column, configured like this:

So you get this:

Use Home>Close & Load To, like this:

Configure the pivot table like this:

If your row headers have repeated values in each row of the 'Type' column, use Design>Report Layout>Do not repeat item labels so the pivot table looks like above.

Now select the entire pivot table and copy it. Paste Special as Values in some other sheet. Use the pasted result as the source of your sunburst chart.