Sunburst Charts and Treemaps (Excel 2016+)

Quick reference

• 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.