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.
- When i insert a new pivot table, and point to an Access database, MS Query for Mac opens. I can select tables, but i cannot see how to create joins. If i return the data to Excel, there's no 'Relationships' button on the ribbon.
- Pivot Charts Are Here! That's right Mac users, you have something to celebrate today! 🙂 Pivot Charts have finally made their way into the Mac version of Excel. This means you can now create interactive dashboards with pivot tables, charts, and slicers on the Mac.
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.
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. The data rows used for the pivot table should have exactly one row with a 1 for each section of rows. If you sum it up, you exactly get the distinct count. If you add a new row to the pivot, you need to add it to the formula in 2. To get distinct values again.
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?
Excel Pivot Table Tutorial Pdf
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.
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.
Microsoft Pivot Table 2016
See you all next week!
Additional resources:
Comments are closed.