Using Excel Pivot Table DrillDown
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.
If you have questions about the summarized data, a quick way to see
the underlying records is to use the Show Details (DrillDown) feature.
With the Show Details feature you can create a list of the related
records on a new worksheet, then examine the data, to see where any
problems occur.
Extract Pivot Table 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:
- In the Pivot Table, right-click the number for which you want
the customer details.
- In the pop-up menu, click Show Details
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 9 new customers from Illinois in 2005.
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.
Name Show Details Sheets When Created
If you frequently use the Show Details command while working with
a pivot table, your workbook can quickly fill up with the data details
sheets. At the end of your working session, you probably want to clear
out all the extra sheets, so they don't clutter up the workbook.
With the following event code, you can automatically name the sheets
created by the Show Details feature, so they are easy to identify.
The code will add a prefix of "XShow_" to each drilldown
sheet. Then, you can easily identify and delete them. Or, use the
code in the following section, to automatically delete the sheets
before closing the workbook.
Create a Public Variable
- On the keyboard, press Alt + F11, to open the Visual Basic Editor
(VBE).
- In the VBE, click the Insert menu, and click Module.
- At the top of the module, where the cursor is flashing, type the
following line of code to create a public variable. This variable
can be used by other procedures in the workbook.
Public SheetType As String
Create the Double-Click Event Code
The code should run when a cell is double-clicked on the pivot table
sheet, so you'll add code to that sheet's module.
- Right-click on the pivot table's worksheet tab, and then click
View Code.
- Add the following code to the worksheet module, below the Option
Explicit line at the top of the code module:
'==================
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.
If the double-clicked cell is in the Values area (pt.DataBodyRange)
of a pivot table, the procedure is the public varible, SheetType,
is set to a value of Show.
Add the Workbook Event Code
The worksheet event code, shown above, will identify the Show Details
worksheets. Next, you'll add event code to the Workbook's module.
It will run when a new sheet is added to the workbook.
- In the VBE, in the Project Explorer, double-click the ThisWorkbook
object for your workbook.
- Add the following code to the workbook module, below the Option
Explicit line at the top of the code module:
'==================
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 SheetType is set to the value of Show, the sheet is given a name
that starts with XShow_.
This makes the sheets easy to identify, and they can be deleted manually
or automatically when the workbook closes.
Delete Drilldown Sheets When Workbook Closes
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.
- In the VBE, in the Project Explorer, double-click the ThisWorkbook
object for your workbook.
- Add the following code to the workbook module, below the Option
Explicit line at the top of the code module:
'==================
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, you will see a message box, asking if
you want to delete the Show Details sheets. Click Yes, if you want to
delete them.
Then, click Save, when prompted, to save the workbook.
