August 15

How To Drill Down in Pivot Tables Using Quick Explore

play

1  comments

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

All Excel LOOKUPs Explained

How to: Power Query File From Folder

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Use this Bottom Section to Promote Your Offer

    Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim 

    >