Creating Sunburst & TreeMap Charts in Excel 2016

Familiarise yourself with two of Excels fantastic new Chart Types – the Sunburst, and Treemap (collectively known as Hierarchical charts)

It’s been many years since Excel has given us any meaningful new Chart Types (I’m not counting Sparkline Charts)

However, the initial release of Excel 2016 gave us a 2 new chart types, with 2 more released as part of a Jan 2016 Update.

I’ve already spoken about Waterfall Charts, and was impressed at the speed and ease of creating these, compared to the method you had to use before.

The other types we have are,

• Funnel Chart
• Waterfall Chart
• Sunburst Chart
• Treemap
• Histogram

Today, I’m going to talk about the Sunburst & Tree Map Chart Types.

These are interesting ones to look at, as previously, it was incredibly difficult to create these in Excel.

I’ve before created a pseudo-Sunburst Chart by nesting doughnut charts, but is far from ideal, and as for Tree-Map, I believe the last time I looked at trying to do this it involved loading Java based add-ins or similar. Again, not ideal, and never really used it in the end.

It should be noted that neither of these charts can be created using Pivot Charts – which, for reasons which will become apparent, is a little frustrating.

Sunburst Chart

The first step to creating any chart is prepping your data.

For a sunburst chart, the structure of your data is a little different than from other charts.

Your label fields will come first, and you will likely have several (otherwise you’ll just created a donut chart!).

Importantly, though, you do not repeat your labels for each row. Leave them blank. I have included a couple of examples below.

Because of this slight difference in how you prep your data did make creating my first sunburst charts a little frustrating, as it wasn’t obvious that this was the requirement.

The first example is a good one of why it’s frustrating that you can’t create a Sunburst from a Pivot Chart / Pivot Table – as you can very easily replicate the data layout required in a pivot table, but you must copy+paste values that Pivot Table before you can create a chart from it.

One your data is correctly formatted, simply highlight the data, and INSERTàChartàHierarchy ChartàSunburst

This will create a Sunburst using the default settings, so may look a little garish

But resize, and choose a different colour palette (DESIGNàChange Colors)

And you will have something a little easier on the eye

We can quickly see that for Meat & Fish, most of my spend is on Private Label goods, but for Toiletries, most of my spend in on Branded Goods.

Of course, you can create sunbursts with multiple layers, not all of which may apply to all categories.

Taken from the second dataset example I used above

Ok, not very imaginative use of a sunburst, but hopefully you get the idea!

Treemap Chart

As you created the Sunburst, you will have seen that Excel groups the Treemap chart with it, as a group of Hierarchy Charts.

What this means is that the data layout required is the same for both types. This is useful, as it allows us to quickly toggle between the two

So, Right-Click your Sunburst ChartàChange Chart Type

And select ‘Treemap’

OK, and your chart will be converted to a Treemap Chart

One useful setting for Treemap Charts that I knew existed, but took a little digging to find where to set it, is creating a banner for the Category Label.

Right-Click your chartàFormat Data Series

You should see, on the Format Data Series Task Pane, a set of Series Options

Once of these is ‘Banner’

Selecting it will convert your ‘Parent’ Category Label into a banner.

However, these does appear to be a bit of a bug here (or maybe I’ve just it figured it out yet!)

As you can see, it’s added the banners, but there is no formatting – and it doesn’t seem possible to change them independently.

However, we can work around this.

If you Right-Click your chart, you should see a floating menu pop up allowing you to set the Fill colour.

If you change this colour, then your entire chart – including the banners – will change.

But this may not be the effect you’re after!

There is another workaround though. Change the colour scheme to how it was ([CTRL]+Z)

Select the chart, and go to the DESIGN tab

From the Styles section, select one of the styles which has the Banners pre-formatted

This now gives you a chart with banners correctly formatted.

You can now change the colour scheme as you desire, and the banners should persist – though you still seem to have limited (no) control over the background formatting.

And here is my other Sunburst Chart converted

So, two new chart types that were previously near-impossible to create in earlier versions of Excel. Neither are without their niggles, which at least for the banner formatting will hopefully be fixed.

But still very welcome additions, and adds to the compelling argument to take the upgrade to 2016.