Skip main navigation

Reporting Hierarchical Data

Welcome back. In this part, we’re going to explore some charting capabilities. We got the basic structure for a dashboard. We got our client, Lucy the sales manager, happy. But we want to pay more attention to different kinds of visualisations and charting. So let’s move to Excel again. And we have a pivot similar to other pivots we were doing before. In this case, I have categories and subcategories and rows. And I have a philtre on years that allows me to switch between year to year, nothing special so far. Now I want to– I know that I can attach to each pivot table a pivot chart. And I do that by clicking the pivot chart here.
And I know that I have different options of creating visualisations. Now in my last discussion with Lucy, she mentioned a very specific kind of visualisation she was interested in. She wanted to see some structure, some hierarchical representation of the data. We have larger parts like categories and smaller parts within those categories. So I noticed a few times before that my good friend here is the Tell Me feature. Remember, this is a relatively new feature. You wouldn’t find it in other versions. And also I’m going to now show you some features which are actually very new.
So the types of charts that I’m going to use are new to Excel 2016 and even to the further updates after the original release of 2016. So hierarchical, let’s see what Excel has to say. I start typing hierarchical and immediately the feature, the Tell Me feature says, you can insert a hierarchy chart. There are two of them, a tree map and a sunburst. I click on it and I immediately get an error saying you cannot create this chart type with data inside a pivot table. Oh, that’s not a good thing to find at this point.
And actually, this is part of a bigger kind of limitation that not every chart can be used that is known to Excel can be used as a pivot chart. So not every chart can use data within a pivot. For example, there are others which, for example, scatter charts. And the moment I try to use this while I’m in on the pivot, it tells me you cannot create this chart, same error message it was getting before. And so there is– and I still want to use this kind of hierarchical structure.
So I can actually use or reuse the technique that we use in the previous part of creating data using summarising data from the detailed table not with a pivot but with expressions. And once this is done with expressions, I will be able to utilise those new chart types. By the way, those chart types are planned to be supported as pivot charts. So you should expect further down version of Excel that those charts will be supported as pivot charts. But there’s still going to be others that are not going to be. And I might need to do the same thing of creating the data with formulas instead of a pivot table.
So if you remember, I started last time copying the pivot to a regular range just to have the regular structure. But here, before I do that, I want to change a little bit the structure of the pivot. We’re going to go to an area of the ribbon that we didn’t see before under Pivot Table Tools. We used quite a few of the features of the analyse. But we didn’t see that there is also a design. And design is something that enables us to apply these styles. And also there’s something called the Report Layout. I’m going to use a record label that’s called tabular. So this is the way it’s going to be.
So instead of if I undo it you see that the categories and the subcategories are all in the same column, column D in this case, and the subcategory which is nested within is a little bit shifted in. But in the tabular way, each one of them occupies its own column. And this is the way I wanted. Now another thing that I want to change is that I want to repeat all item labels. I want to have for each row the categories to be repeated. You will understand in a moment why do I need it. Another thing that I want is, actually, I want to remove the subtotals. So I can right click on the subtotal and just uncheck it.
And also, I don’t need the grand total. So I can actually do the same for grant totals. And that’s it. So this could be the basis of the report that I’m going to use for my hierarchical chart. So again, I’m going to copy it and I’m going to paste it as values, increase a little bit the width of the columns, apply a format, in an Excel way not in the pivot way.
And I don’t– well, sorry– actually, I don’t need. The numbers will be recreated by me as The sum IF function. Remember? So now I can start doing the Sum IF function, Sum IFs function that will say sum IFs will be a little bit simpler because I only have something in rows and nothing in columns in this case. So again, I’m going to summarise the revenue. And I’m going to slice it or philtre it by– and I can actually use subcategory because it’s subcategory defined category that it comes from. So I don’t need to use both the category and the subcategory. So the subcategory and I would say that the subcategory comes from the cell.
And I actually don’t even need to adjust the reference to absolute or relative because I’m not going to drag this value or this expression anyway. So– and another one that I want to use is the year says data, year. And in this case, it’s going to come from this cell here. And here I do have to push F4 to make it absolute. This is the value. It fits the value that they have in the pivot. I’m happy. And I drag down the values. And I know that I can actually drive it using the philtre from here. And again, this pivot I don’t– it just finished its function. I don’t need it anymore.
And I can actually, again, change the options not to fit the width of the columns and take it from here. Cut and paste over here. So it will look really as a philtre to this area.
So now, once this is a regular range, let’s see what I’m going to do start writing hierarchical again. And now it tells me, insert the hierarchy chart. The moment I try it, it shows me this preview. I see that I have two, I have the tree map and I have the sunburst. We’ll explore each one. So now I am going to introduce the chart. And you see this is what I call a hierarchical chart. So it shows the first column, which is per the category as these large areas which have the titles for them bikes, accessories, and clothing. Within each area, there are subareas which belong to the subcategories.
And the size of the relative sizes of the big areas and the smaller areas inside them are proportional to the value that is in this column. And the same thing will be true for if I change the chart type and I go from the trim up to the sunburst, sunburst is actually a good way to show even more hierarchy, like more levels into it. But the principle is the same, only across this circle shape. And I know that I want to see what happens when I change years. So you see immediately that the chart responds. One last thing I want to test, remember in 2014 we only had one major category, bikes. So here it is.
Now what will happen when I click on All? We saw before what this will cause us. It will actually break everything. And remember what we did last time was that for the– we introduce an IF statement saying that IF the value for the slicer equals– and I have to make it absolute. If it equals All, then last time I entered an asterisk value. But I won’t do it. Believe me, it wouldn’t work. The reason it wouldn’t work in this case is that the year value is numeric. And the asterisk is a good wild card to catch all values for strings.
But if I want to do it for numeric values, what I could do, I can actually enter as a criteria greater than 0. As a string, like this. And this and otherwise– so this– I have to complete the IF saying if it’s not equal to All then– so just use it as it is. And now I will just close one more parenthesis. And I just did for one single row. I double click and you see that now it actually works well with All and also with single year. It works fine. I will not be able to use this trick as it is for selecting two years like 2014 and 2015. But each individual year and the total for all years.
So we saw again a use of a known pivot table approach to summarising the data. And we saw that there are some types of charts that cannot be used as pivot charts. And we saw that there are few new chart types that were introduced in Excel 2016. And they– some of them are, you can explore. There’s a bunch. They’re not just the [INAUDIBLE] hierarchical ones. But we saw that if we have hierarchy data, like here product categories, subcategory, the three map and the sunburst can be used to visualise this kind of data. See you next time.

In previous sections, we looked at pivot tables and created dashboards using our pivot tables. We even used our pivot tables to compare and summarise our data. What we have not considered is different ways of visualising and charting our data.

In this step, we will explore treemaps and sunbursts, which simplifies visualisation and representation of hierarchical data.

It is important to remember that not all chart types are compatible with pivot tables. To use charts that are not supported by pivots, you can summarise your data from your pivot with formulas.


A treemap chart provides a hierarchical view of your data and makes it easy to spot patterns, such as which items are a store’s best sellers. The tree branches are represented by rectangles and each sub-branch is shown as a smaller rectangle. The treemap chart displays categories by colour and proximity and can easily show lots of data which would be difficult with other chart types.
Treemap charts are good for comparing proportions within the hierarchy, however, treemap charts aren’t great at showing hierarchical levels between the largest categories and each data point.


The sunburst chart is ideal for displaying hierarchical data. Each level of the hierarchy is represented by one ring or circle with the innermost circle as the top of the hierarchy. A sunburst chart without any hierarchical data (one level of categories), looks similar to a doughnut chart. However, a sunburst chart with multiple levels of categories shows how the outer rings relate to the inner rings. The sunburst chart is most effective at showing how one ring is broken into its contributing pieces.
Click on these links to be taken to the resources.

Join the discussion

Of these two charts available in Excel, which do you prefer and why?

Use the discussion section below and let us know your thoughts. Try to respond to at least one other post and once you’re happy with your contribution, click the Mark as Complete button to check the step off, then you can move to the next step.

This article is from the free online

How to Use Microsoft Excel for Data Analysis

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education