Discover how to drill-down into a Pivot table in Excel. Let’s take a closer look and learn how to use Quick Explore function easily!
Is it enough to display some corporate specifics (KPIs)? The answer is not that simple as we might think. If we make the report to a CEO, then the answer is yes. They are capable of making the right decision based on a few key performance indicators. If we are analysts, then our task is to pay attention to every little detail.
Click on the PivotTable button and select Create Manual PivotTable from the popup menu. A Create PivotTable window should appear. Select the range of data for the pivot table and click on the OK button. In this example, we've chosen cells A1 to D13 in Sheet1. Pivot charts created in Microsoft Excel can be more useful than ordinary charts, because they are more easily manipulated to show different information and summarizations. Learning to create a pivot chart can be confusing and there are a few decisions you need to make before you begin.
Is there a tool that can help us to see behind the summed up data? The answer is yes. In today’s article we’ll introduce you to the drill-down feature that’s capable of seeing under the hood.
Drill-Down to show the Details
Pivot tables are our good friends, especially when we talk about data analysis. Let’s see how the structure of a table builds up. Every single value can contain one or more records.
Explanation: Let’s see an example of this. If we talk about a passport ID, then one ID can belong to one person, this is called a 1:1 type connection. If we talk about a birth-date, than the 1:N type connection is relevant. We can easily see that more people can bear on the same day.
After this little sidetrack, let’s go back to the initial data set. In the figure below, we can see a sales table. At first glance there are not many things can be known about the value in the cell C6 (East / Office Supplies).
Use the drill-down feature to find out all the details!
How to extract information from a Pivot table?
We should use the drill-down method to create a dashboard in Excel. In the example below, we have summed up the data by regions and categories. We would like to display all the connecting records of the cell C6.
First, highlight one of the cells of the Pivot table containing data. After this right-click the highlighted cell. Finally, choose the “Show Details” option from the appearing list.
How To Create A Pivot Chart In Excel 2016 Mac
Pro Tip: Instead of the Show Details command, there’s a lot faster solution to achieve the drill-down. Highlight the cell value that we’d like to detail. Click on it twice, and it is done!
A new worksheet will be automatically inserted, with a list of ‘Office Supplies’ records.
Use the Pivot table slicers carefully!
Be very careful when using slicers!
If we connect slicers, namely filters to the Pivot table, we can be up for some surprises. The using of the drill-down function together with slicers can lead to false results in the versions of Excel before 2016!
But they’re worth to use in Excel’s newer versions, especially when we’d like to filter even more the data of the table before drill-down.
To insert a new slicer, click on the Insert tab and choose the Slicer icon.
In the case below on the picture, the slicer contains all categories. If we would like to resolve the Grand Total in the E5 cell than all elements will be on the list.
Would you like to display a single category before drill-down? Click on the category name.
If we double click the B5 cell, then exclusively the records of the highlighted category will be displayed.
Keep eyes on the source data
There’s not much more frustrating case when we work with data that is not connected to the original source or to the Pivot table. What happens when the data of the Pivot table changed?
Let’s see the nightmare of all analysists: data is not refreshing! And this can occur rather easy when we use an external data source.
We cannot underline enough times that with the use of the drill-down feature, we only get a static list. This is no longer connected with the original Pivot table!
Drill-down PowerPivot Data Model
In this section, we’ll show you how to build a small data model using tables and PowerPivot. Just a few clicks, and you’ll get an overview of the Quick Explore feature too.
Build the data model
Quick Explore is a perfect solution in Excel if you want to drill-down into the details. It’s good to know: Excel 2013 and above is required for this functionality.
We have sales-related data tables on the Tables worksheet.
Let’s create relationships between tables first.
Select the range and add the selected table from the Worksheet to the Data Model.
Use the Manage option in PowerPivot. It is a rock-solid add-in for data analysis in Excel. We’ll create a Data Model with relationships between the sales tables:
Now create a Pivot Table from the source tables. On the PowerPivot window, click the PivotTable icon. The structure will be summarized and grouped into a new Worksheet.
Drill-down Using the Quick Explore Function
On the new worksheet, click on a cell containing data! Now the Quick Explore icon appears.
Click on the icon. The Explore box will appear. The pop-up window shows all the tables from the Pivot Table. You can choose from the available options to drill down into the data.
Click on the Quick Explore icon. The Explore pop-up window appears. This box shows all the sales tables from our Pivot Table.
Select one field from the available options to drill-down into the details. In this example, we want to extract the related product names of cell C8. The value is $25630 for the customers of BlueTechnology in the Midwest region. Choose this option from the Explore window.
Excel will rebuild the Pivot table and transform it. Check the top-left corner and the filters. The pivot table is now restructured and provides details about the selected cell.
Should you reset the layout?
Use the Ctrl + Z keyboard shortcut to back your original table structure.
Conclusion
It is useful for everyone to oversee more than just the surface. It is possible in cases that we have to drill-down to the bottom of the data tables and examine the details. In this process, our help is the drill-down method. In the case of large data tables use this function courageously because there’s a lot to discover with a little research.
See you all next week!
Pivotpal Free Download
Additional resources:
Creating the pivot table on my Mac is such a pain, so I am now creating the tables on my personal PC. After I get the pivot table created and formatted it works/looks great on the PC, and any PC users I send it to. The problem I am having is when I send it to Mac users. All formatting seems to lost/messed up, and I can't seem to fix this on the Mac. I need the pivot tables to look professional and consistent for everyone I send them to, PC or Mac.
I am wondering if I need to write a macro to make the formatting stay the same on the Mac, and if I do, I am wondering how to do that? If anyone could help me out with this I would really appreciate it! Thanks!