Double-click a pivot table value, to create a new sheet with the records used in that total. That is Excel's DrillDown (Show Details) feature. Use macros to name the sheets, and ask if you want to delete them when closing the workbook.
When you summarize your data by creating an Excel Pivot Table, each number in the Values area represents one or more records in the pivot table source data. In the screen shot below, the selected cell is the total count of new customers for the East region in 2014.
Does a number look too high or too low? A quick way to see those records is to use the Show Details (DrillDown) feature -- double-click on that cell. A new worksheet is automatically inserted, with a list of those "East 2014" records.
In the sample pivot table shown above, new customer counts are shown by year and by state. To see the customer details for any number in the pivot table, use the Show Details feature.
To see the underlying records for a number in the pivot table:
TIP: Instead of using the Show Details command, you can double-click on a Values cell.
The related customer records are extracted to a new worksheet in the workbook. These are the 15 new customers from the East region in 2014.
NOTE: The table is based on the Default Table Style in your workbook, and the cells are in Normal cell style. There are instructions on my Pivot Table blog for adjusting those settings.
There are a couple of things to keep in mind when you use the DrillDown (Show Details) feature.
The extracted records are copies of the original records in the pivot table source data. They are not linked to the original records or to the pivot table. If you want to change the data, go to the original source data, and make the change there.
If you have Slicers connected to the pivot table, the DrillDown list might not show the set of records that you expect. This problem occurs in Excel 2010 and Excel 2013, and is fixed in Excel 2016.
In those versions of Excel, you should include all the Slicer fields in the pivot table too, if you plan to use the DrillDown feature. Otherwise, the Slicer filter will be ignored in the DrillDown results.
For example, in the screen shot below, the Severity field is a Slicer, and also in the pivot table. Priority is a Slicer, but is not in the pivot table. If you double-click on cell G6, it should show 2 records on the DrillDown sheet. However, it incorrectly shows 8 records - all of the Priority values are included, not just the "20" priorities that are filtered in the Slicer.
Watch this video to see the problem, and how to add all the Slicer fields to the pivot table, to fix the problem.
When you use the Show Details command, it adds a new worksheet, and your workbook can quickly fill up with the data details sheets. When you're closing the workbook, you probably want to clear out all the extra sheets, so they don't clutter up the workbook.
With the following macro, you can automatically name the sheets created by the Show Details feature, so they are easy to identify. The code adds "XShow_" to the sheet name.
That makes it easy for you identify and delete those sheets. Or, use the code in the Delete Sheets section, to automatically delete the sheets before closing the workbook.
First, you will add a code module to the workbook, and create a public variable there.
Public SheetType As String
Next, you will add code that runs when you double-click a cell on the pivot table sheet. Follow these steps to add that code.
NOTE: If your workbook has multiple pivot table sheets:
Warning: A code module can contain only one copy of each event, such as the Worksheet_BeforeDoubleClick event. If your worksheet already contains a Worksheet_BeforeDoubleClick event, you could combine this code with your existing code.
Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, _ Cancel As Boolean) Dim pt As PivotTable If Me.PivotTables.Count = 0 Then Exit Sub For Each pt In Me.PivotTables If Not Intersect(Target, _ pt.DataBodyRange) Is Nothing Then SheetType = "Show" Exit For End If Next pt Set pt = Nothing End Sub
When you double-click a cell on the pivot table sheet, the BeforeDoubleClick event runs automatically. It is called an Event Procedure, because it is triggered by a specific action (event)..
The Double-Click code, shown above, identifies the Show Details worksheets. Next, you'll add a macro (event procedure) to the Workbook's module.
It will run when a new sheet is added to the workbook.
Private Sub Workbook_NewSheet _ (ByVal Sh As Object) On Error GoTo err_Handler Select Case SheetType Case "Show" 'Show Details Sh.Name = _ Left("XShow_" & Sh.Name, 31) Case Else 'do nothing End Select SheetType = "" err_Handler: Exit Sub End Sub
If the worksheet double-click creates a new sheet, the workbook's NewSheet event code runs.
If the SheetType variable is set to the value of Show, the sheet is given a name that starts with XShow_.
If you use the code shown above, to name the drilldown worksheets, you can also use code to automatically delete those sheets, when closing the workbook.
NOTE: This code is in the Basic download file. There is also a Custom download file, with more options for naming and deleting the drilldown sheets.
Follow these steps to add the code that deletes them.
Private Sub Workbook_BeforeClose _ (Cancel As Boolean) Dim ws As Worksheet Dim Resp As Long Dim ShowCount As Long ShowCount = 0 For Each ws In ThisWorkbook.Worksheets If UCase(Left(ws.Name, 5)) = "XSHOW" Then ShowCount = ShowCount + 1 End If Next ws If ShowCount > 0 Then Resp = MsgBox("Delete Show Detail sheets?", _ vbYesNo, "Delete Sheets?") If Resp = vbYes Then Application.DisplayAlerts = False For Each ws In ThisWorkbook.Worksheets If UCase(Left(ws.Name, 5)) = "XSHOW" Then ws.Delete End If Next ws End If End If Set ws = Nothing End Sub
When the workbook is closing, the macro runs automatically.
First the macro looks for any Drill Down sheets -- their names start with "XSHOW".
If UCase(Left(ws.Name, 5)) = "XSHOW"
If there are any Drill Down (Show Detail) sheets, you will see a message box, asking if you want to delete them.
Click Yes, if you want to delete them.
The instructions on this page show all the steps to create the Basic code for the pivot table Drilldown macros. If you'd rather not start from scratch, download one of the sample workbooks -- Basic or Custom.
Here are the steps to start copying the code from the sample workbooks into your workbook:
WARNING: These steps assume that there is no existing code on the ThisWorkbook module in your workbook.
WARNING: These steps assume that there is no existing code on the pivot table sheet modules in your workbook.
If you are using the Custom file, follow this step to get the Settings worksheet. This is NOT required in the Basic file.
Note: This step will also add named ranges to your workbook, which you can see in the Name Manager.
After you copy all the code, and the Admin_Drill sheet (Custom file), close the Visual Basic Editor.
Go to one of the pivot table sheets in your workbook, and double-click a Value cell, to create a drilldown sheet. If all the code was installed correctly, the drilldown sheet should have the special prefix.
If you used the Custom file code, you can go to the Admin_Drill sheet, and adjust the settings.
IMPORTANT: See the Add Macros to Workbook section above, for the steps to copy the code from the sample files into your workbook.
Last updated: November 2, 2022 10:37 AM