The Pivot Table tools in Excel offers Drill Up and Drill Down buttons that are always greyed out.
How can you use those?
You have to find someone who has the Power Pivot tab in their Excel (Office 365 E3 or Office 365 Pro Plus)
Power Pivot, Manage, Diagram View. Select fields for hierarchy. Right click and Create Hierarchy.
If you don't have Office 365, you can download a workbook with a hierarchy from Creating a Hierarchy in a Pivot Table
How can you use those?
You have to find someone who has the Power Pivot tab in their Excel (Office 365 E3 or Office 365 Pro Plus)
Power Pivot, Manage, Diagram View. Select fields for hierarchy. Right click and Create Hierarchy.
If you don't have Office 365, you can download a workbook with a hierarchy from Creating a Hierarchy in a Pivot Table
Transcript of the video:
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.
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.
