With PivotTable Quick Explore we can filter and drill down in PivotTables and Pivot Charts without the field list exposed. This means our users can customise their reports without needing to know how to build a PivotTable:
Quick Explore is also available in Pivot Charts:
Note: Applies to Excel 2013 onward for Power Pivot/Data Model or OLAP PivotTables.
Excel PivotTable Quick Explore requires the source data to come from the Data Model (aka Power Pivot), or an OLAP (Online Analytical Processing) cube. In other words, it doesn’t work with regular PivotTables and only works in Excel 2013 onward where Power Pivot is built into Excel, as opposed to an add-in.
Watch the Video
Download the Workbook
Enter your email address below to download the sample workbook.
Building PivotTables for Quick Explore
In the Create PivotTable dialog box (Insert tab > PivotTable), you can either connect to an external data source:
And navigate to your OLAP database, or other relational database like Access.
Or if your data is in an Excel Table you can check the box to load it to the Data Model upon creating the PivotTable:
Using Excel PivotTable Quick Explore
To use Quick Explore, first select a single cell or item in the PivotTable that you want to filter on, then click the magnifying glass, or right-click > Quick Explore. From here you can choose the table and field you want to drill down to.
In the example below notice that I’ve selected John, which will become my filter. Then I want to drill down to the Order Priority field:
And below you can see that John has become the Filter (cell B2) and the row labels contain the Order Priority:
We can continue to drill down further into the data. For example, from here we can filter Order Priority ‘High’ and drill down to the Product Container:
Now we have two filter items; Salesperson ‘John’ and Order Priority ‘High’, with the Product Container in the row labels:
If you have multiple tables in your Data Model/Power Pivot, then those will appear with an expand icon to the left of the table name. Clicking on this will reveal the fields in the table:
So, now that you’ve drilled down you’re probably wondering how you drill back up. Well, there’s no drill up as such, but you can use CTRL+Z or 'Undo' on the Quick Access Toolbar to undo the filter and drill down.
Excel Quick Explore Pivot Charts
Quick Explore works the same in a Pivot Chart, except you select the item in the axis to trigger the Magnifying Glass icon:
Excel PivotTable Quick Explore Things to Note
- The hierarchy can come from the source data or grouped items, like dates etc.
- If you already have a hierarchy in your PivotTable it will be replaced with the field you drill down on.
For example, in the PivotTable below I’ve selected 2014 to ‘Explore’ and I’m going to drill down to the Order Priority, which will replace the quarters currently in the row labels:
In the image below, you can see the quarter breakdown has been removed from the PivotTable altogether:
Excel PivotTable Quick Explore Limitations
- Your data needs to be in a hierarchy that supports drilling down. e.g. flat hierarchies or hierarchies that don’t have data on multiple levels are not supported.
- You cannot drill down on named sets.
- Unfortunately, you cannot turn off the Quick Explore pop up icon.
Alternatives to Quick Explore
If you want to retain the levels in the hierarchy as you drill down then an option is to use the Expand/Collapse buttons. These are available in Pivot Charts in Excel 2016 onward: