Drill Into PivotTable Data - Microsoft Support

Skip to main content Sign in with Microsoft Sign in or create an account. Hello, Select a different account. You have multiple accounts Choose the account you want to sign in with. Applies ToExcel for Microsoft 365 Excel for the web Excel 2024 Excel 2021 Excel 2019 Excel 2016 WindowsWeb

Drilling into large amounts of data in a PivotTable hierarchy has always been a time-consuming task with lots of expanding, collapsing, and filtering.

The Quick Explore feature lets you drill into your Online Analytical Processing (OLAP) cube or Data Model-based PivotTable hierarchy to analyze data details on different levels. Quick Explore helps you navigate to the data you want to see, and acts like a filter when you drill down. The button shows up whenever you select an item in a field.

You typically start by drilling down to the next level in the hierarchy. Here’s how:

1. In your OLAP cube or Data Model PivotTable, pick an item (like Accessories in our example) in the field (like the Category field in our example).

   You can drill down only one item at a time.

2. Click the Quick Explore button that appears in the lower-right corner of the selection.

   

3. In the Explore box, pick the item you want to explore, and click Drill Down.

   

   Now you see subcategory data for that item (Accessory products, in this case).

   

4. Keep using Quick Explore until you reach the data you want.

   Notes: 

   • You cannot drill down in flat hierarchies (such as hierarchies that show attributes of the same item but don’t provide data on a next level) or other hierarchies that don’t have data on multiple levels.

   • If you have grouped items in your PivotTable, you can drill down on a group name the same way you drill down on other items.

   • You cannot drill down on named sets (sets of items you use frequently or that combine items from different hierarchies).

Drill up to explore big picture details

After drilling down, you can drill back up to analyze summary data. While Undo on the Quick Access Toolbar gets you back to where you started, drill up lets you take any other path back up so you can get bigger picture insights.

1. In the PivotTable hierarchy you’ve drilled into, pick the item you want to drill up on.

2. Click the Quick Explore button that appears in the lower-right corner of the selection.

3. In the Explore box, pick the item you want to explore, and then click Drill Up.

   

   Now you’re seeing data from the higher level.

4. Keep using Quick Explore until you reach the data you want.

   Notes: 

   • You can drill up multiple levels of a hierarchy at once. Right-click the item you want to drill up on, click Drill Down/Drill Up, and then pick the level you want to drill up to.

   • If you have grouped items in your PivotTable, you can drill up to a group name.

   • You cannot drill up to named sets (sets of items you use frequently or that combine items from different hierarchies).

Drilling into large amounts of data in a PivotTable hierarchy has always been a time-consuming task with lots of expanding, collapsing, and filtering.

The Quick Explore feature lets you drill into your Online Analytical Processing (OLAP) cube or Data Model-based PivotTable hierarchy to analyze data details on different levels. Quick Explore helps you navigate to the data you want to see and acts like a filter when you drill down. The button shows up whenever you select an item in a field.

You typically start by drilling down to the next level in the hierarchy. Here’s how:

1. In your OLAP cube or Data Model PivotTable, pick an item (like Accessories in our example) in the field (like the Category field in our example).

   You can drill down only one item at a time.

2. Click the Quick Explore button that appears in the lower-right corner of the selection.

   

3. In the Explore box, pick the item you want to explore, and click Drill Down.

   

   Now you see subcategory data for that item (Accessory products, in this case).

   

4. Keep using Quick Explore until you reach the data you want.

   Notes: 

   • You cannot drill down in flat hierarchies (such as hierarchies that show attributes of the same item but don’t provide data on a next level) or other hierarchies that don’t have data on multiple levels.

   • If you have grouped items in your PivotTable, you can drill down on a group name the same way you drill down on other items.

   • You cannot drill down on named sets (sets of items you use frequently or that combine items from different hierarchies).

Drill up to explore big picture details

After drilling down, you can drill back up to analyze summary data. While Undo on the Quick Access Toolbar gets you back to where you started, drill up lets you take any other path back up so you can get bigger picture insights.

1. In the PivotTable hierarchy you’ve drilled into, pick the item you want to drill up on.

2. Click the Quick Explore button that appears in the lower-right corner of the selection.

3. In the Explore box, pick the item you want to explore, and then click Drill Up.

   

   Now you’re seeing data from the higher level.

4. Keep using Quick Explore until you reach the data you want.

   Notes: 

   • You can drill up multiple levels of a hierarchy at once. Right-click the item you want to drill up on, click Drill Down/Drill Up, and then pick the level you want to drill up to.

   • If you have grouped items in your PivotTable, you can drill up to a group name.

   • You cannot drill up to named sets (sets of items you use frequently or that combine items from different hierarchies).

If you have the Excel desktop application, you can use the Open in Excel button to open the workbook and drill into PivotTable data.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

See Also

Create a PivotTable to analyze external data

Create a PivotTable to analyze data in multiple tables

Use the Field List to arrange fields in a PivotTable

Group or ungroup data in a PivotTable report

Need more help?

Want more options?

DiscoverCommunity

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Yes No Thank you! Any more feedback for Microsoft? Can you help us improve? (Send feedback to Microsoft so we can help.) What affected your experience? Resolved my issue Clear instructions Easy to follow No jargon Pictures helped Other Didn't match my screen Incorrect instructions Too technical Not enough information Not enough pictures Other Any additional feedback? (Optional) Submit feedback By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×