Locked lesson.
About this lesson
Sunburst and Treemap charts are intended to show the breakdown of the source data.
Exercise files
Download this lesson’s related exercise files.
Sunburst charts and Treemaps (Excel 2016+).xlsx46.5 KB Sunburst charts and Treemaps (Excel 2016+) - Completed.xlsx
53 KB
Quick reference
Sunburst Charts and Treemaps (Excel 2016+)
Working with Sunburst charts and Treemaps
When to use
Sunburst and Treemap charts are intended to show the breakdown of the source data
Instructions
Treemaps
Source Data
- Ideal data consists of 3 columns sorted by the first and second columns in that order
- Ideal data should be aggregated as much as possible
Creating the Chart
- Select the data and go to Insert --> Charts --> Hierarchy Charts --> Treemap
- Data is shown with the largest items from top to bottom and left to right
Formatting Options
- Right click the data labels to add values instead of just descriptions
- Right click a square and choose Format Data Series to show the categories as “Banners”
Sunburst Charts
Source Data
- Ideal data consists of data sorted in ascending order from the left most column to the right most
- The earlier the column in the data, the more it will be shown to the center of the chart
- Ideal data should be aggregated as much as possible
Creating the Chart
- Select the data and go to Insert --> Charts --> Hierarchy Charts --> Sunburst
- Data is shown with the largest items clockwise around the rings
Formatting Options
- Right click the data labels to add values instead of just descriptions
Hints & tips
- Sunburst and Treemap charts are only available in Excel 2016
- 00:04 The next charts that we're going to look at are what we call hierarchy charts.
- 00:09 These charts are used to help visualize the breakdown where we have different
- 00:13 categories and subcategories to see what the proportional sales are for each.
- 00:18 You'll notice that this data here in a nice summary format lends particularly
- 00:21 well to it.
- 00:22 You'll notice we have sleeping bags and
- 00:23 we have tents that have been sold with multiple vendors associated to each.
- 00:27 We also have a vendor that happens to sell us items from different categories.
- 00:33 Now the one kicker of this, this particular set of charts was actually only
- 00:38 released to Excel 2016 subscription users in early 2017.
- 00:43 So if you don't have these charts, you're not on Excel 2016 and
- 00:46 your not on subscription.
- 00:48 Let's go and insert them both.
- 00:50 So we'll go to the insert tab, we'll go up to the hierarchy charts and
- 00:53 we'll grab the tree map or break down tree, I'll just put that over here.
- 00:58 We'll go back, inserts, and this time we'll grab the sunburst chart.
- 01:04 Now, you'll notice that the sunburst chart has a bit of a leaf structure to it.
- 01:09 So in the center, we've got tents, force of nature, we have 47,000.
- 01:13 High alpine, we've got 31,000.
- 01:15 Wouldn't it be nice to see those data labels on here?
- 01:17 We can right-click, format the data labels that already exist and add the values
- 01:23 to them as well if we want to do that, or we can take them off, whichever.
- 01:28 You'll notice though that there's very little other options that we have.
- 01:31 Data labels, trend lines, none of these things are actually here.
- 01:33 So the chart are little bit limited in that particular regard.
- 01:37 With the breakdown tree, if we just move and
- 01:39 take a look at this with a tree map, we do have some options.
- 01:43 We can right click on this, and if we go to format data series,
- 01:46 you'll notice that we have an overlapping and a banner.
- 01:49 When we go to banner, it actually puts the title across the top
- 01:53 rather than having it in the individual cells themselves.
- 01:57 I like this better because in here we've got tents.
- 02:00 The yellow is also tents, but it doesn't show up here.
- 02:02 When I put a banner at the top, it now nicely classifies it,
- 02:06 which means I don't need this access anymore.
- 02:08 So I can get rid of it.
- 02:11 Can I change the data labels here to put the values on?
- 02:13 Well, sure, format data labels and values, and there you go.
- 02:18 That's essentially about the makeup of what you can do to customize these charts.
- 02:22 There's not a whole lot going on.
- 02:24 The key thing that I want you to recognize though is what happens
- 02:28 with the data order.
- 02:31 One of the things that's important when you're working with this is that your data
- 02:33 is sorted in the correct order.
- 02:35 If I grab this table, and notice I'm just grabbing the header,
- 02:38 if I mouse over just to the edge, I'll get this little bar, I can left click and
- 02:41 drag to ressort this, so that vendor comes first.
- 02:45 And you'll notice that it changes things up.
- 02:47 I got force of nature high alpine.
- 02:48 Here's high alpine again.
- 02:50 That's not really gonna work.
- 02:51 But if I click and sort this A to Z, at this point,
- 02:55 what'll happen is that high alpine gives me tents and sleeping bags.
- 02:58 And then force of nature.
- 02:59 And then Acme gives me its two components down the bottom.
- 03:02 And high alpine here gets broken down nicely as well.
- 03:05 The key thing to remember about these things is that the first category,
- 03:09 which is gonna be the innermost ring, needs to be sorted.
- 03:12 The first category needs to be sorted in order to put
- 03:15 these in the right order as well.
- 03:17 With your breakdown tree, you always read from the left most and
- 03:20 then across to the right.
- 03:23 Now this is working with very well summarized data and it works quite nicely.
- 03:28 What I like to do now is show you how it works with data that's not so
- 03:33 well summarized.
- 03:34 So here we are with some more, well, detailed I guess, visuals here.
- 03:39 We got both the sunburst and the treemap here.
- 03:41 You'll notice that the category right now is nicely sorted.
- 03:45 We can see that because our leaf structure is working out fairly well.
- 03:49 What's interesting about the sunburst is that it actually does seem to set up okay.
- 03:54 I've used something in this to go through and say all right,
- 03:57 well, let's sort this in a proper order to get all of these guys grouped together.
- 04:02 So if we do our data sorts on here, we're gonna go to an advanced sort.
- 04:07 And the way that I wanted to set this up is with category values sorted first.
- 04:12 Then we're gonna go and we're gonna add, we'll put our vendor sorted next.
- 04:16 And then we'll say, add, and we'll put our month in sorted next.
- 04:20 When we do that, it's going to keep it in the same order rate now, which is perfect,
- 04:24 because we've got our tents, and we've got all of our force of nature group together,
- 04:28 then we've got our month ends.
- 04:29 If we were to go back and sort this differently, where month end was above,
- 04:34 you'll see that things change up.
- 04:37 Now we've got tents, and then we've got all these segregations because this
- 04:41 is the last element, but we've sorted it in order.
- 04:44 And it's always going to plot from the largest, not from the largest date, but
- 04:48 the one with the largest sales.
- 04:51 So getting this order wrong really messes things up.
- 04:56 What about doing this?
- 04:57 Let's grab vendor and move it across to the left hand side here.
- 05:01 And now we can see that everything goes, holy smokes,
- 05:03 this is really crazy, what's going on?
- 05:05 So again, it becomes really important here to go back and
- 05:09 get your sorting into the correct order.
- 05:11 So we're going to sort this time, add level.
- 05:14 We're gonna sort this one first be vendor.
- 05:17 And then we're gonna sort it by category.
- 05:19 And then we're gonna sort it by month end.
- 05:21 Now, at this point,
- 05:22 everything should come back nicely looking the way that it should.
- 05:25 Some interesting observations around this though.
- 05:28 In the sunburst chart, we have our high alpine, then we have our product lines of
- 05:31 tents and sleeping bags, and then the individual month sales.
- 05:35 In a tree map, we never see tense, it just doesn't show up because it's
- 05:40 only picking up the first value and the last one right before the sale.
- 05:43 So something to be aware of here, because if I were to come back and
- 05:47 just nuke this, this actually starts to make the data look a little bit nicer and
- 05:53 a little easier to consume.
- 05:55 Now, I'm not quite sure that I'd want to set my chart up in this fashion
- 06:00 because it's a little bit over broken down and
- 06:02 that's sort of the second part I wanted to talk about here.
- 06:04 Having this data well summarized, like we had on a previous tab,
- 06:08 works quite nicely for this.
- 06:10 But having it broken down with multiple levels for Acme Accessories,
- 06:13 Acme Accessories, Acme Accessories, mean every time we hit Acme,
- 06:16 we're going to see an individual accessory piece in here.
- 06:19 So that's not really the right set up either.
- 06:21 Summarize your data properly, make sure it's sorted, and
- 06:24 these charts will work quite nicely for you.
- 06:26 They can show you some great information as far as your sales breakdown, but
- 06:29 they have to be treated properly.
Lesson notes are only available for subscribers.