Drill into PivotTable data

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

In Excel 2013, the new 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.

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

1. 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).

1. 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.

1. 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).

More information about PivotTables

• 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