How To Drill Down in Pivot Tables Using Quick Explore

Given a rich data set, making pivot tables and charts is amongst the first tools we use to start analyzing that data. But what normally gives insight is how one drills down into the data: to find out hidden patterns or correlations or check for anomalies. Observing a striking phenomenon and then finding out the data points leading to that phenomenon can require a lot of data manipulation. And so much effort can almost always lead to a lot of stress!

Well, there is a stress-free way of drilling your data using Quick Explore (Excel 2013). And Oz is here to explain how to use it.

So, let’s get our data sets out!

 1 – The Data Model

Once you have your data in place, create a Data Model from it. You can find the tutorial the tutorial here:  link to the post covering introduction to data models

Now that all your data tables are properly linked and you have a data model in place, let’s proceed.

2 – Initiating the Drill

In your Data Model, you notice a peculiar figure and, now, you want to figure out which data points are leading to this figure. Well, just click on it for starters.

You would notice that a special symbol (containing a magnifying glass and a lightning bolt) appears on the corner. Click on it.

quickexplore1

3 – Lets Drill

A menu would now appear showing the options of variables you can drill down to.

Select the variable of your choice, and Excel will apply the relevant filters to your table. This way you would be able to see the data points used in calculating that figure. And also figure out what is so unique about them.

Note that regular Pivot tables do not offer this functionality.quickexplore2

4 – Application

The use of this tool will obviously be in checking out the data points behind various anomalies or striking figures that you may notice. And the good part is that you do not have to fall back on your raw data or manipulate your pivot table. Quick Explore presents to you the relevant data points in a summarized, meaningful fashion.

Get The Download

[thrive_link color=’teal’ link=’https://excel.tv/wp-content/uploads/Data-Models-Bull-Riding.xlsx’ target=’_self’ size=’small’ align=’aligncenter’]Get The Excel Download Here[/thrive_link]

What’s next?

Start experimenting! Fire up your data and let Quick Explore rip everything open to you. Also, share this cool feature with your friends and help them combat some miseries of exploring data sets!


Tags


You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free Power User Quick Guide

    Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

    With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.

    >