New Excel 2016 chart types: Tree map and sunburst

- [Instructor] Starting in Excel 2016 there is some new chart types available. And it's not exactly obvious where they are or how you might use them. I've got some data in columns A, B and C. And you can tell by the way the data's structured sometimes we refer to this as a hierarchical structure. First thought might be, let's create a simple column chart here. We can do this very quickly. Simply by clicking with the inside the data. Because it is surrounded by empty cells we don't even have to highlight this data. We'll simply press Alt F one to get a quick chart.

But I think most people would agree that's not a very good looking chart after all. That doesn't really show the data. So let's approach this from a different angle. Insert tab in the ribbon. How about recommended charts. Do these look any better? Well, that might be okay. How about this one. But your eye might be drawn to this one here. This is one of the new chart types. It's called a tree map. Let's click okay. Move this around a little bit. And resize it. As you can see, each color there refers to a different department. The yellow here is manufacturing.

And within that we see different rectangles representing the various proportions based on salaries for each city. And the dark blue there represents operations, and so on. You can see the others. There could be times when this is ideal. Notice that we can't quite see all the city names in the lower right corner. Making this wider might help. So at different times you will want to experiment with this. The amount of data being represented here only goes down to row 31. So you could probably imagine in some situations your data just is too big for this to work very well.

I'm going to shrink this a bit. Put it back over here. By the way, the data below this, which is identical except that it's got the missing repeating entries here, will work in the exact same way even though these are empty. Excel will recognize the hierarchal nature of this as we go to Insert. And if we know we want to use the tree map it's found with this icon right here under Insert Hierarchy Chart. Click the drop arrow and there's Tree Map. And that looks pretty much like the first chart, I'm not going to bother resizing it. But it will look identical to the chart above if we were to manipulate it the same way.

Now, in the data to the right here in columns E, F, and G that's ultimately the same data as in columns A, B, and C. But you'll notice I've moved the columns around. So, clicking within this data Insert. Tree Map. We see this kind of a layout. Now, when you are working with data like this I think you can see the value of each of these, it's just a question of, is the grouping the way you want it? And we can see the difference in the tables we put them side by side. The chart on the left, as we saw earlier, each color represents a different city.

The chart on the right each color represents a department, you want to experiment with that for sure. Now, on the next sheet over here and notice I did call this sheet tree map, the companion new chart is called sunburst. And let's start with the same kind of data and compare the two. We've got data in columns A, B, and C. This time we've got status here instead of city. Insert. Let's try tree map first. Based on the data we're seeing here, no surprises. We've got more data here, so it's hard to see all those small entries.

So how's this going to look with the other kind of chart. So I'll click within the data there. Go back to Insert. It's called Sunburst. And first thought here might be not as good. And we could make that bigger and bigger and bigger but look how many panels are not showing any text because it's just too small. Now, if we click on one of those could we go to the home tab and instead of font nine maybe make that be font six. And readability could be an issue here.

I can barely read that, but it does show us more of the entry. So that's a possible work around at times. But that's pretty crowded there. And maybe not the best choice. So I'm going to delete that one. Now, here too, we could make a comparison between the two chart types if we were to create a chart for this data. And like in our previous example I've moved the columns around. So we could do something there. But, here's another thought that we haven't encountered. In the data starting column J and also the data starting over in column O, now we've got four columns of data.

What happens here with the tree map? And will that sunburst be any better? So with the active cell within this data, here too, the data is surrounded by empty cells so we don't have to highlight it. Insert. There's that icon. Tree Map. And you can see how that's looking. But what has it done here? The different colors represent the cities. The yellow here is New York, but there's full-time, and there's full-time again and then another full-time. And so these are being repeated. And it's pulling the data out of column L.

Column K is essentially ignored. So this isn't working so well. Let's contrast this with a sunburst. The active cells within the data. Insert. Sunburst. Now we've got the same problem as before with sunburst but there's an additional angle here worth exploring. There are three rings here. The inner ring is for the cities. That's the data over in column J. The next ring, the ring that's in the middle is for departments. And the outer ring is for status.

So, the hierarchal nature of the data here is probably presented better even though we can't see all the entries. Now, I think you recognize here that this probably will work better if we've got less data. You don't always have less data, of course. But we probably have some exploring to do with the data here. Now that's based on the information in columns J, K, L, and M. Now, for the sake of argument in an example here what if we were to take this data and say, in effect, we no longer have a Chicago and a Houston office.

Or maybe we didn't have one to begin with. So I'm going to take all the data for Chicago and Houston. That only leaves us with New York and L.A. But, I'm going to take this data right click and delete. Shift the cells up. Watch the hierarchal chart. So here, we've got an inner ring it refers to the cities. In this case, there are only two of them left. The next ring over refers to department. But they seem to be repeating. Here's manufacturing, manufacturing, manufacturing. Let's go sort the data. So clicking over in column K.

Go to the data tab here. I'm going to click the AZ button. And that makes it a bit better. Or does it? We see the names repeating here. We've got to go back and sort the cities again because now the inner ring has the cities repeating. So, let's go back to column J. And we'll do an AZ sort there. Well that works a little bit better. So you definitely need to experiment with this and sort the data. We are still not seeing data in all the wedges here. We can only imagine what that one is although we can slide over and point to it. So once again, another possible option.

Click one of the labels. And then on the home tab, change this to a smaller number. For example, six. So lots of situations for experimenting with these charts. I think you saw from the very first example though there's a place for these and they work better than standard column charts for sure. And they also fill that need that we sometimes have to share the breakout in a pie-like chart but with multiple rings. So some new additions to the charting capability in Excel. Remember, once again, they are called tree map and sunburst.