Tree Maps and Sunburst Charts in Office 365

Maven Analytics
A free video tutorial from Maven Analytics
Award-Winning Analytics & Business Intelligence Training
4.6 instructor rating • 26 courses • 608,925 students

Lecture description

Excel's Tree Map and Sunburst charts are ideal for visualizing hierarchical data that falls into natural groups and subgroups. In this demo, we'll explore movie titles and employee records to practice building and customizing our own tree maps and sunbursts.

Learn more from the full course

Microsoft Excel - Data Visualization, Excel Charts & Graphs

Master 20+ Excel charts, build advanced Excel visuals & learn data analysis with a top Excel instructor (Excel 2016+)

04:28:07 of on-demand video • Updated October 2021

  • Understand WHEN, WHY, and HOW to use 20+ chart types in Excel 2016+
  • Learn advanced Excel tools like automated dashboards, scrolling charts, dynamic formats, and more
  • Master unique tips, tools and case studies that you won't find in ANY other Excel course, guaranteed
  • Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor
  • Get LIFETIME access to project files, quizzes, homework exercises, and 1-on-1 expert support
  • Build 10+ Excel projects designed to take your data visualization skills to the next level
English [Auto] 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 twenty sixteen 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 subgrouping. So some examples might be if you want to look at relative revenue by book title organized by genre and subgenre, or look at the number of employees by department, by office or population by city, state and region. All of these examples, data sets have natural groupings and subgroups. A couple of protests here. The first use tree maps when you're only visualizing one or two hierarchical levels. So, for instance, topic and subtopic or one relative sizes are really important to your story. Sunbus charts and the other hand, are a great way to show the depth of different hierarchical levels within your data. Second, and 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. Our date of his exercise is workbook. And here we've got two sample data sets and we 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 a one through D 14. And in the insert menu there's a new section in Excel twenty sixteen that specifically includes tree maps and Sunburst. In this case, I'll choose treatment. And there you go. So it looked at the source data that I had selected and it said, OK, 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 grey 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 and sci fi. So within each of those colors that could hone in and in fact Excel Twenty Sixteen 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 want to 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 traps Cat was the number one comedy in this particular time frame and followed by too many balloons, Jai Hosie and adventure swirls. 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 is grouped and sorted. So 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 treatment 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 wreaks 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 and try 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 your 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 what to chart. So I can go ahead and select a 17 through F twenty seven and insert a sunburst chart, go ahead and delete the title here. Eventually, there we go, and I'm just going to 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 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, OK, 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, OK, within New York, just show me. People working in the sales department within sales only. Show me people who Allan is supervising. And then on Alan's team, show me Tinas Group. So there you go. You can kind of drilling and quickly get a sense visually, just based on the distribution or even the shape of this sunburst and of some of the characteristics of our data. So tree maps sunburst to brand new charts in Excel that are great ways to visualize hierarchies in your data.