• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel PivotTable Quick Explore

You are here: Home / Excel PivotTables / Excel PivotTable Quick Explore
Excel PivotTable Quick Explore
April 25, 2018 by Mynda Treacy

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:

Excel PivotTable Quick Explore

Quick Explore is also available in Pivot Charts:

filter and drill down 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

Subscribe YouTube

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Building PivotTables for Quick Explore

In the Create PivotTable dialog box (Insert tab > PivotTable), you can either connect to an external data source:

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:

add this data to the Data Model

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:

drill to Order Priority

And below you can see that John has become the Filter (cell B2) and the row labels contain the Order Priority:

Order Priority in the row labels

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:

drill to Product Container

Now we have two filter items; Salesperson ‘John’ and Order Priority ‘High’, with the Product Container in the row labels:

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:

multiple tables in the Data Model

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 Quick Explore Pivot Charts

Excel PivotTable Quick Explore Things to Note

  1. The hierarchy can come from the source data or grouped items, like dates etc.
  2. 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:

    hierarchy in the PivotTable

    In the image below, you can see the quarter breakdown has been removed from the PivotTable altogether:

    the quarter breakdown has been removed from the PivotTable

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:

Excel pivot chart drill down buttons

Learn about Excel Pivot Chart Drill Down Buttons here.

Excel PivotTable Quick Explore

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.

Excel PivotTable Percentage Change

Excel PivotTable Percentage Change calculation is dead easy with Show Values As. Add conditional formatting, and Slicers for interactivity.


Category: Excel PivotTables
Previous Post:timer stopwatch in excel vbaTimer or Stopwatch in Excel VBA
Next Post:Excel 3D MapsExcel 3D Maps

Reader Interactions

Comments

  1. jim

    September 4, 2021 at 4:56 am

    I’m with Jim and Zack,
    I’ve never seen this and it does NOT appear in the downloaded example spreadsheet
    I’ve had 365 for nearly 2 years now and all data is added to the Data Model

    from some of the other comments, perhaps that’s lucky!

    jim

    Reply
    • Mynda Treacy

      September 4, 2021 at 8:51 am

      Hi Jim,

      I’m thinking this is some kind of licensing limitation, however there’s no mention of this restriction in Microsoft’s documentation for Quick Explore.

      Mynda

      Reply
  2. Steven Alker

    September 4, 2021 at 12:04 am

    Simply brilliant!

    Ever since starting your courses, I have wondered what the magnifying glass icon and other things were.

    I have found that the best way to proceed is to just remember the puzzling item and carry on with the work. (A bit like quantum Physics, Copenhagen Flavour: Shut up and keep on calculating!!) now knowing that eventually, all will be made clear.

    You must think that I am becoming a bit of a groupie! I have given up looking at other Excel videos – they are either too boring, or the presenter clearly doesn’t really understand the intent of the tool he is droning on about. Technically, they are OK, but they provide no context.

    I watched one covering Solver. Yes, they knew how solver worked, but they had clearly never done any Linear Programming at all, and they didn’t know why they were using the tool or why it worked. In fact, it produces correct answers that are useless to the user for quite a wide range of problems. For that, he would need to understand the context and how to fix the unusable results.

    Reply
    • Mynda Treacy

      September 4, 2021 at 8:50 am

      Thanks for your kind words, Steven! It’s great to know you find my tutorials helpful 🙂

      Reply
  3. Jim Berglund

    September 3, 2021 at 3:36 am

    I’m confused…
    I use MS 365 and can’t seem to find Quick Explore in Excel.
    I set up a pivot table using your downloaded file and right-clicked, poked and prodded to find the magnifying glass, but to no avail.
    Am I missing something?

    Reply
    • Mynda Treacy

      September 3, 2021 at 9:40 am

      Hi Jim,

      I’m also using 365. I wonder if you haven’t loaded the data to the Data Model?

      Mynda

      Reply
  4. Joana Flores

    June 11, 2021 at 1:39 am

    Once you’ve selected Quick Explore and drilled down, are there any ways to go back out without using Ctrl+z? I need to use quick explore to drill down on a table I was given for which I was not given the source data. I then need to copy+paste the resulting tables with the detail I need onto a separate file. However the only way I can find to navigate back to the upper levels is using Ctrl+Z which basically undos the pasting of the table, making my entire task pointless! Does anyone know a work around for this issue?

    Reply
    • Mynda Treacy

      June 11, 2021 at 10:01 am

      Hi Joana,

      That’s a great question! You’d have to edit the fields in the field list to revert the PivotTable back to its original state. I don’t know of any automatic way other than Undo.

      Mynda

      Reply
  5. Zack

    November 9, 2019 at 5:29 am

    To whom it may concern,

    The quickexplorer option in excel doesn’t pop up when I click on a pivot table cell. Is there a setting to turn it on?

    Thanks

    Reply
    • Mynda Treacy

      November 9, 2019 at 8:53 am

      Hi Zack,

      Did you add your data to the data model/Power Pivot? Quick Explore is only available for PivotTables that use data from the data model.

      Mynda

      Reply
  6. Steve

    October 22, 2018 at 2:37 am

    Is there a way to turn off the Quick Explore pop up. For years, I’ve been annoyed by it in Power Pivot and am dumbfounded that there’s seemingly no way to disable this irritating pop up.

    Reply
    • Mynda Treacy

      October 22, 2018 at 12:52 pm

      Hi Steve,

      No, there doesn’t appear to be any way to turn Quick Explore off. You can suggest it on Excel UserVoice.

      Mynda

      Reply
      • Renny Schweiger

        April 11, 2020 at 5:37 am

        I just submitted a suggestion on Excel UserVoice. We gotta have a way of turning this thing off. It destroys the table. I completely agree with Steve. Unless you have a pivot table that is completely unadorned in a worksheet with nothing else on it and you didn’t care how it changed (had no one, ever) this thing wreaks havoc!

        Reply
        • Mynda Treacy

          April 11, 2020 at 10:21 am

          Yes, it’s annoying I agree.

          Reply
  7. Ted

    May 24, 2018 at 4:25 am

    Once again, you have me download a work book and it does not match up with your instructions.
    Like Alan Wyatt, you assume that I know as much as you do and blam, I get led into a brick wall.
    The Pivot Table in the workbook I downloaded does not match your instructions
    Your Pivot Table has Row Labels of years and quarters, not the salesperson

    A waste of my time

    Reply
    • Mynda Treacy

      May 24, 2018 at 1:15 pm

      Sorry, Ted, the PivotTable in the workbook is in the state of the final example in the tutorial above. You can change it and play around with it to try out different scenarios covered in the post above, all the data is there in the file, you just change the row labels as desired. The tutorial wasn’t intended to teach you how to use a PivotTable, that’s a whole other lesson, I simply included the workbook so you had something you could experiment with.

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.