Quick Explore is a great tool from Excel that allows you to easily drill down into the details of your PivotTable by creating a modified PivotTable just for those details. However, it will only work in certain versions. So let’s take a look and see how to use Quick Explore in Excel.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner. As noted below, unless you have a version of Excel that supports PowerPivot, you will not be able to access and utilize this option. The versions that support this option are:
- Office Professional Plus
- Excel 2013 stand-alone
- Office 365 ProPlus
Here I have four tables called “Sales Team”, “Products”, Customers”, and “Transactions”:
I used the Manage option in PowerPivot to create a Data Model with relationships between the various tables:
Power Pivot in Microsoft Excel 2013 is an add-in you can use to perform powerful data analysis in Excel. It is only available in more robust versions such as Office Professional Plus or a stand alone version of Excel, so you may or may not have access to it. This tutorial assumes you have a basic knowledge of and have access to PowerPivot.
Once I’ve created my PivotTable, when I click on a cell containing data, the Quick Explore icon appears:
When I click on that icon, an Explore box appears that presents all the tables from my PivotTable along with all the available options that I can choose from to drill down into the data. So, if I want to know the Product Names that comprise the $28,204 for the Betahouse customer in the Midwest territory, I select it then click on the Drill To box:
Excel will then reconstruct my PivotTable with the appropriate filters and categories located in the proper quadrants of my Field List to provide the detail for the Product Names of the $28,204:
To return back to my original PivotTable structure, all I need to do it use the keyboard shortcut Ctrl + Z (Undo) and I’m back to my previous set up:
This is a great way to create PivotTables for the detail drill-down you may need for you data. You can continue to drill down as far as your details can take you, and Undo back to your original structure.
Remember, you can only use this option is you have the ability to create Data Models with PowerPivot, and only with certain versions of Excel
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!