Creating a Hierarchy in a Pivot Table
April 26, 2018 - by Bill Jelen
Recently, a friend of mine wondered about the Drill-Down and Drill-Up buttons in the Pivot Table Tools tab of the Ribbon. Why are these perpetually greyed out? They take up a lot of space in the Ribbon. How is anyone supposed to use them?
After some research, there is a way to use them, but you have to use the Data Model and use the Power Pivot diagram view to create a hierarchy. If you don't have the Power Pivot tab in your Ribbon, you will have to find a co-worker who has the button in order to create the hierarchy. (Or, if you just want to try the feature, download the Excel file that I created: Hierarchy.xlsx)
First step - convert your pivot source data set to a table using either Home - Format as Table or Ctrl + T. Make sure that the option for My Table Has Headers is selected.
Use Insert - Pivot Table. In the Create PivotTable dialog, choose the box for Add This Data to the Data Model.
Here is the PivotTable Fields before you create the hierarchy.
Click the Manage icon on the Power Pivot tab in the Ribbon. (Many instances of Excel 2013 and 2016 do not have this tab. It does not appear on the Mac.)
In the Power Pivot for Excel window, click on the Diagram View icon. It is near the right side of the Home tab.
Use the resize handle in the lower right corner of Table1 to enlarge Table1 so you can see all of your fields. Click on the first item in your hierarchy (Continent in my example). Shift-Click on the last item in the hierarchy (City in my example). You could also click on one item, and Ctrl-Click on others if the hierarchy fields are not adjacent. Once you have the fields selected, right-click any of the fields and choose Create Hierarchy.
Hierarchy1 is created and is waiting for you to type a new name. I will name my hierarchy Geography. If you click away from Power Pivot, Hierarchy1 is no longer in Rename mode. Right-click Hierachy1 and choose Rename.
Close Power Pivot and return to Excel. The Pivot Table Fields now shows the Geography hierarchy and More Fields. Your Sales field is hidden under More Fields. I somewhat understand why they hide Continent, Country, Region, Territory, City under More Fields. But I don't understand why they hide Sales under More Fields.
To build the pivot table, check the box for the Geography hierarchy. Open More Fields by clicking the triangle next to it. Choose Sales.
There is a lot to notice in the image above. When you initially create the pivot table, the active cell is on A3 and the Drill Down icon is greyed out. However, if you move the cell pointer to North America in A4, you will see that Drill Down is enabled.
With the cell pointer on North America, click Drill Down and Continent is replaced by Country.
With the cell pointer on Canada, click Drill Down and you will see Eastern Canada and Western Canada. Note at this point, both the Drill Down and Drill Up buttons are enabled.
I clicked Drill Up to return to Country. Select United States. Drill Down three times and I end up at the cities in the Carolinas region. At this point, the Drill Down button is greyed out.
Note that from the Continent level, you can click Expand Field to show Continents and Countries. Then, from the first Country, choose Expand Field to reveal Regions. From the first Region, use Expand Field to show Territories. From the first Territory, click Expand Field to reveal City.
All of the screenshots above are showing the pivot table in my default view of Show in Tabular Form. If your pivot tables are created in Compact Form, you will see the view below. (To learn how to have all of your future pivot tables start in Tabular form, see this video).
What is the advantage of the Hierarchy? I tried creating a regular pivot table without a hierarchy. I still have the ability to Expand and Collapse fields. But if I want to show only the regions in Canada, I would have to add a slicer or Report Filter.
Watch Video
Video Transcript
Learn Excel from MrExcel Podcast, Episode 2196: Drill Up and Drill Down in Pivot Tables.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. There's a mystery in pivot tables. If I insert a pivot table here, you see that we have Drill Up and Drill Down fields, but they never light up. What's up with this? Why do we have these? How do we make them work? Alright, this is a great, great question and unfortunately, I feel bad about this. I'm trying to do all of my life in Excel not ever using the Power Pivot tab. I don't want you to have to pay the extra $2 a month for the Pro Plus version of Office 365, but this is one-- this is one-- where we have to spend the extra $2 a month or find someone who has the extra $2 a month to set this up.
I'll take this data format as a table. It doesn't matter what format I choose, the format is not important; just getting us a table is the important part. Power Pivot, we're going to add this table to our Data Model, and then click Manage. Alright, so here's our table in the Data Model. We have to go to Diagram View, now we'll make this a little bit wider so we can see all the fields. I'm going to choose Continent; I'm going to Shift+click on City. Now that makes up my Drill Down, Drill Up, the hierarchy. And then we'll right-click and say Create Heirarchy. And they give us a name-- I'm going to type "Geography" for my Hierarchy, like that. Great, now, with that one change, we insert a pivot table-- and this will be a Data Model Pivot Table-- and you see that we can add Geography as its own hierarchy.
Now, the one thing I don't particularly like about this, is everything else moves to More Fields. Alright? So we choose Geography and it flies to the left-hand side. And while that's great, I also need to choose Revenue, and they took the fields that weren't part of the Hierarchy and moved them to More Fields. So it's like, I get it, they're trying to hide the fields I'm not supposed to choose, but in the process of doing that they also hid More Fields-- the Revenue or Sales down here. Alright. So, a little frustrating we have to go to more fields to get the fields that aren't part of the Geography, but that's that's the way it goes.
Alright. So, now, now that we have that let's take a look at what works here. I'm sitting on Continent, I go to the Analyze tab and nothing lights up, it didn't work. Shoot! No, it did work, you just have to come to North America and then I can Drill Down and it replaces Continent with Country. And then from Canada I can Drill Down and get Eastern Canada and Western Canada. From Eastern Canada drill down, I get Ontario and Quebec. Ontario, I get those cities, I can drill up Drill Up, Drill Up, and choose United States; Drill Down, Drill Down, Drill Down. Alright, so that's how it works.
Give it a try, you have to have the Power Pivot tab or find someone with a Power Pivot tab. If you just want to try it, look in the YouTube description there'll be a link to the web page and there's a place there on the web page where you can download this file, and you should be able to use the Hierarchy even if you don't have the Power Pivot tab. If you're in Excel 2016 or Office 365, it should work.
Now, you know, see, I guess the thing that I'm not sure I'm a fan of is the fact that they're getting rid of the other information, as opposed to using the Expand icon, which would then expand into the next group, and the next group, and the next group. We've always had the Expand icon, but even then it's working a little bit differently. Here, if I would, I can actually sit there in North America and expand one level at a time without having to choose each additional one from the data model. It looks like we have to move the cell pointer over, one bit at a time.
Alright, now, this tip was really just, kind of, discovered. The Excel MVPs had a conversation with the Excel team about these buttons, so not covered in this book. But a lot of other great tips covered in MrExcel LIVe, the 54 Greatest Tips of All Time.
Wrap-up for today: Why is Drill Up and Drill Down constantly grayed out? Well, you have to create a Hierarchy. In order to create a Hierarchy, you have to go into Power Pivot; into the Diagram View; select the fields for the heirarchy; and then right-click; and Create Hierarchy.
I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
Title Photo: Steinchen / Pixabay
