Quick Explore in Excel 2013

November 18th 2013

I have a tendency to ignore new features in software until I know I need them. This rarely works out for me because until I know what the feature does, how will I know if I need it?

And thus it was with Quick Explore in Excel 2013. In over a year of using Excel 2013 and building pivot tables daily, I still had found no reason to use Quick Explore. Even the word "quick" could not entice me to take a few seconds to find out what it does.

Then, last month, I accidentally clicked and activated Quick Explore. A few weeks later and I use it all the time. Quick Explore in Excel 2013 is an essential must-know for all pivot table users everywhere.

What does Quick Explore do?

Let's say you have a pivot table that shows the number of tornadoes that touched down each month. January 2008 stands out as particularly anomalous and you want to know why.

84 tornadoes in January 2008? I wonder why?

In Excel 2010, you can manually filter to that specific month of year and then start adding additional attributes to see which states or which dates in January caused the number to be so unusual. When you're done, you can click undo to get back to your original pivot table.

This works great but it is cumbersome. You were already looking at a pivot table with January 2008 on it. But then, instead of being able to right-click to filter to January 2008, you have to open up a filter dialog box that shows you every month & year that ever was and find January 2008 to filter to.

Pick a month to filter to. Oh, you weren't looking at 1950? Too bad. Just be glad you're filtering on months and not on account numbers as this would be even slower.

In Excel 2013, everything changes.

You want to find out more about January 2008? Select the month in your pivot table and you'll see the little Quick Explore box to the bottom right of the cell. (Or you can right click and select Quick Explore from the menu).

The Quick Explore box. If you use pivot tables in Excel 2013, you see this all the time.

The Quick Explore menu opens and here I can drill to another attribute that I want to slice January 2008 tornadoes by. In this case, I'm going to slice by state and see which state is causing our January tornado numbers to be so high.

The Quick Explore dialog box. On the left hand side I can browse all the attributes in my data model. Once I've decided on one, I can then select it on the right hand side to slice my January 2008 data by that attribute.

Quick Explore does two steps in one:

1) It adds January 2008 (the cell I initially Quick Explored) as a filter to my pivot
2) It slices that number by the new attribute I selected

And that's it. In just a couple of quick clicks I can see that Missouri had an outbreak of January tornadoes that explains why my January number is so high.

A quick explore of January 2008 tornadoes by state.

At this point I can use Quick Explore again to drill into Missouri and find out what dates or what places in Missouri had tornadoes. I can also manipulate the pivot table in the regular way - dragging on additional attributes. Finally I can press CTRL+Z to undo my Quick Explore and return to my original pivot table.

Quick Explore doesn't give you anything you couldn't do before. Instead, it gives you a short-cut to make exploring the data in pivot tables faster. If you use pivot tables regularly, Quick Explore is definitely worth knowing about.

Note: Quick Explore only works when pivoting against a cube or Power Pivot data model. Here's the Microsoft documentation on Quick Explore.

Go back...

Visitor Comments

Post Your Own Comment

Note that comments may not appear immediately on this site.

Commenting is not available in this channel entry.