Search Contextures Sites

Contextures
Excel news
by email

 

 


Amazon.com 

 

 

 

 

 

 

Learn how to create Excel dashboards.

Excel Pivot Table DrillDown

Using Excel Pivot Table DrillDown  
Extract Pivot Table Records  
Name Show Details Sheets When Created
Delete Drilldown Sheets When Workbook Closes

Download the Pivot Table Drilldown Workbook


Pivot Table Tutorial List  

 

 

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.

pivot table

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:

  1. In the Pivot Table, right-click the number for which you want the customer details.
  2. In the pop-up menu, click Show Details

show details command

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.

pivot table top 10

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

  1. On the keyboard, press Alt + F11, to open the Visual Basic Editor (VBE).
  2. In the VBE, click the Insert menu, and click Module.
  3. 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.
  4. 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.

  1. Right-click on the pivot table's worksheet tab, and then click View Code.
  2. 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.

  1. In the VBE, in the Project Explorer, double-click the ThisWorkbook object for your workbook.
  2. 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.

  1. In the VBE, in the Project Explorer, double-click the ThisWorkbook object for your workbook.
  2. 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.

Note: The ThisWorkbook module can contain only one copy of each event, such as the Workbook_NewSheet event. If your workbook already contains a NewSheet or BeforeClose event, you can add the above code to the existing event.

Download the Sample Workbook

To see how the event code names the sheets, and deletes them when closing, you can download the Pivot Table Drilldown sample file.

Xtreme Pivot Table Course

Pivot table skills are essential, if you want to be an Excel master. To raise your skills to the expert level, I recommend the Xtreme Pivot Table course, from John Michaloudis, at My Excel Online.

This course has more than 200 videos -- beginner, intermediate and advanced level -- along with practice workbooks, finance business cases, and 12 months of personal support. Each short lesson is clear, and easy to follow. Work through the lessons at your own pace, and track your progress. The course is an excellent value, at a surprisingly low price.

See the course details, and watch sample videos here: Xtreme Pivot Table Course. When you buy the course, use the coupon code CONTEXTURES for a 10% discount

More Pivot Table Resources

Tutorials:

 

 

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: July 6, 2014