Tree Maps and Sunburst Charts in Office 365

-: So, in the past, if you wanted to visualize hierarchical data, Excel was not a very good way to do that. But that's all changed in 2016 with the introduction of tree maps and sunburst charts. So these types of charts are a really great way to visualize data that has natural groups or sub-grouping. So, some examples might be: If you wanna look at relative revenue by book title, organized by genre, and sub genre; or look at the number of employees by department, by office; or population by city, state and region. All of these example data sets have natural groupings and sub-groupings. A couple pro tips here. The first, use tree maps when you're only visualizing one or two hierarchical levels. So, for instance, topic and subtopic. Or when relative sizes are really important to your story. Sunburst charts, on the other hand, are a great way to show the depth of different hierarchical levels within your data. Second, in this one's really, really important, make sure your raw source data is already grouped and sorted before trying to create a tree map and a sunburst chart. And I'll show you an example of why that's so important. So, let's take a look. All right, so here we are in the tree maps and sunburst chart tab of our database exercises workbook. And here I've got two sample data sets. I'm gonna use one to create a tree map and the other to create a sunburst chart. So, starting with the top data set, this is ticket sales by title, by rating, by genre. So, it's a good example of data that has natural groupings and sub-groupings that a tree map would be perfect for. So, I'll just select A1 through D14. And in the insert menu, there's a new section in Excel 2016 that specifically includes tree maps and sunbursts. In this case, I'll choose tree map. And there you go. So, it looked at the source data that I had selected, and it said, Okay. I see that genre is grouped and sorted, and that there are titles within genres, and a ticket sale value. And it's created this tree map for me. And I can just go ahead and delete that title. Now, what this tree map is showing me is at a high level the colored sections are giving me a sense of which genres in aggregate drive the most ticket sales. So, in this case, the gray boxes make up the biggest proportion of the entire area of my chart. So, I know that horror movies drove the largest overall chunk of ticket sales. Followed by comedy, then drama, then sci-fi. So, within each of those colors that could hone in, and in fact, Excel 2016 has some cool interactivity built into some of these newer charts. So, you can actually click through and drill into each section. So, let's say I wanna drill into comedies. Now, within comedy, the relative size of the boxes within this entire section represent the proportion of ticket sales among comedies. So, I know that Trapeze Cat was the number one comedy in this particular timeframe. And followed by Too Many Balloons, GI Jose, and Adventure Squirrels. So, you can do the same thing just drilling into any of these categories. Now, you remember the pro tip where I said you have to make sure your data's grouped and sorted. I'll show you an example of why that's the case. Let's say What's Wrong With Grandma was no longer a horror movie. Let's say it was a comedy. If I changed that label to comedy, now the tree map gets extremely confused and it adds a second category for comedy. So, it's showing comedy as blues and this one separate field, just to account for that one line that's not grouped with the other comedies. So, as you can see, that really kind of reeks havoc on the tree maps. So, I'll just undo that step. And another example, if I just selected the data and columns B through D, (computer mouse click) and tried to insert a tree map, the same thing happens. It's showing me duplicate values by ratings. It's just really not doing what I expected it to do. So, there's a little bit of upfront work that you need to do to make sure that you're hierarchical charts are formatted in the way that you expect them to be. Now, let's scroll down and take a look at our second data set, which is a classic org chart. We've got offices in Boston, New York, and Chicago. Within offices, we have marketing sales or IT departments. Under each department, we've got a supervisor followed by a manager, and an employee. So, we're drilling down five levels deep here which is deeper than a tree map would be able to show us in any sort of meaningful way. So, this means it's a great candidate for a sunburst chart. And the last thing you'll notice is this count field. This is just because Excel needs some sort of a numerical value to know it to chart. So, I can go ahead and select A17 through F27 (computer mouse clicks) and insert a sunburst chart (computer mouse clicks) and go ahead and delete the title here eventually. (computer mouse clicks) There we go. And, I'm just gonna scroll down and make this nice and large because it's really a very, very cool looking chart. And what's even cooler is that it has that same interactive effect that the tree map did, where you can start on the inner ring of the sunburst, which is your highest level grouping, in this case the "Office" and say, "Okay, show me all of the data points associated with the New York office, or the Chicago office, or the Boston office". And in this case, because we set the count values all to one, it means that you're not giving more credit or more value to any particular employee or manager. It just basically distributes things equally depending on how many segments there are in each ring. So, you can drill even deeper and say, "Okay, within New York, just show me people working in the sales department. Within sales, only show me people who Allen is supervising. And then on Allen's team, show me Tina's group". So, there you go. You can kind of drill in and quickly get a sense visually, just based on the distribution or even the shape of this sunburst, kind of some of the characteristics of our data. So, tree maps, sunbursts, two brand new charts in Excel that are great ways to visualize hierarchies in your data.