Excel - Master Excel's Powerful Pivot Table Techniques: Drill Up & Drill Down - Episode 2196

Microsoft Excel Tutorial: Pivot Table Drill Up and Drill Down.

Welcome back to the MrExcel netcast, where we bring you the best tips and tricks for mastering Excel. In this episode, we'll be discussing the mystery of Drill Up and Drill Down in Pivot Tables. You may have noticed these options in your pivot table, but they never seem to light up. Well, today we'll uncover the secrets behind these buttons and show you how to make them work.

First, let's address the burning question - why do we have these buttons in the first place? And more importantly, how do we make them work? The answer lies in the Power Pivot tab, which unfortunately requires the Pro Plus version of Office 365. But don't worry, we'll also provide a workaround for those who don't have access to this feature.

To get started, we'll need to format our data as a table. It doesn't matter what format you choose, as long as it's in a table format. Then, we'll add this table to our Data Model in the Power Pivot tab and click on "Manage". In the Diagram View, we'll select the fields we want to use for our hierarchy - in this case, we'll choose Continent and City. Then, we'll right-click and select "Create Hierarchy" and give it a name, such as "Geography".

Now, when we insert a pivot table, we can add the "Geography" hierarchy as its own field. However, you may notice that the other fields have been moved to the "More Fields" section. While this may be frustrating, it's a small price to pay for the functionality of Drill Up and Drill Down. And speaking of functionality, let's take a look at how these buttons actually work.

When we select a field, such as Continent, and go to the Analyze tab, nothing lights up. But don't worry, it's not a glitch - you just need to select a specific region, such as North America, and then the buttons will become active. From there, you can Drill Down to see more specific data, or Drill Up to see a broader view. And the best part? You can keep drilling down or up as many levels as you want, without having to manually select each field from the data model.

So there you have it - the mystery of Drill Up and Drill Down in Pivot Tables has been solved. Remember, you'll need the Power Pivot tab to access this feature, but we've also provided a workaround for those who don't have it. And if you want to learn more great tips and tricks for Excel, be sure to check out our book, "MrExcel LIVe: The 54 Greatest Tips of All Time". Thanks for watching, and we'll see you next time for another netcast from MrExcel.

This video answers these common search terms:
how to drill down in excel pivot chart
how to drill down into pivot values in excell
create a drill down in excel
excel how to drill down
how do you drill down data in excel
how to add a drill down in excel
how to create a drill down in excel
how to create drill down excel
how to create drill down in excel
how to create drilldowns in excel
how to drill down excel
how to drill down in excel
how to drill down on excel
how to drill in excel
how to drill through in excel
how to make a table drill down in excel
how to make drill down in excel
how to put drill down in excel
how to use drill down when creating excel
what is drill down in excel

#excel
#exceltutorial
#exceltricks
#evergreen
#pivottable
#pivot_table
#excelpivot
#excelpivottablestutorial
#powerpivot

Table of Contents:
(00:00) Mystery in pivot tables
(00:19) Question: What's up with Drill Up and Drill Down fields?
(00:29) Solution: Use Power Pivot tab or Data Model
(00:44) Steps to add table to Data Model and create hierarchy
(01:39) Limitation: Other fields move to More Fields
(02:15) Demonstration of how Drill Up and Drill Down works
(02:59) Alternative option for those without Power Pivot tab
(03:17) Discussion on the Expand icon and its differences
(04:10) Recap: How to make Drill Up and Drill Down work
(04:20) Clicking Like really helps the algorithm

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.

[ratnoinamaew]

Fantastic info Bill, I had no idea this could be done, saw those arrows before just ignored them!!

[HaydenElsem]

Hey Mr Excel!
In previous versions of Excel pivot tables we were able to double-click and "expand" the pivot table (either in compact form or tabular), however, the double-click action defaults to "drilldown" in Excel 2016. Is there a way stop this and revert back to the old way?

[simfinso]

Normal table with Subtotal can achieve same effect?

[ericrose]

Hi, i was wondering if you provide help with setting up excel sheets to function in the most efficient way possible by emailing you the data and work books and explaining what is needed? There is just so much to do its hard to find a starting point really. My name is Eric, let me know if this is something you are interested in helping me with. Thank you.