How To Use Quick Explore With PivotTables In Excel

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”:

91615-1

I used the Manage option in PowerPivot to create a Data Model with relationships between the various tables:

91615-2

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:

91615-3

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:

91615-4

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:

991615-5

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:

91615-6

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!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

4 Responses to How To Use Quick Explore With PivotTables In Excel

  1. Jan Zitniak September 1, 2016 at 2:56 pm #

    Hello.

    Thank you for your very useful tutorial. I tried your example in my Excel 2016 where I see tabs Analyze and Design under PivoTables group. When I click into any cell under Sum of Total Sales I don’t see any Quick Explore icon. Can you give me a little help in this please?

    Regards
    Jan

    • Michael Rempel September 1, 2016 at 4:14 pm #

      Do you have a version of 2016 that has Power Pivot? Unless you do, this option is not available. Also, it will only work with OLAP cubes or with tables connected as I show in my video. It does not work with a standardly structured pivot table. If you want you can e-mail your file to me and I will take a look at it, but I currently use 2013.

      • Jan Zitniak September 4, 2016 at 4:47 am #

        Hi Michael.

        Thank you for your quick response and willingness to help me. Finally I found the right solution. On my computer I had installation of Office 2016 University which doesn’t support Power Pivot. After installation the Office 2016 Professional Quick Explore appeared there.

        Thanks.
        Jan

  2. Jan Zitniak September 4, 2016 at 4:50 am #

    Btw: I would like to download your My 70+ Favorite Excel Keyboard Shortcuts but I got from a new FeeBurner window following error:

    The feed does not have subscriptions by email enabled

    Regards
    Jan

Leave a Reply