Contextures

Excel Pivot Table DrillDown

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.

Show Records With 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. 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.go to top

East 2014 total value

How to 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

TIP: Instead of using the Show Details command, you can double-click on a Values cell.

show details command

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.

drilldown list on new sheet

DrillDown Warnings

There are a couple of things to keep in mind when you use the DrillDown (Show Details) feature.

Not Connected to Source Data

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.go to top

Problem With Slicers

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.

drilldown problem with slicers

Watch this video to see the problem, and how to add all the Slicer fields to the pivot table, to fix the problem.

Macro to Name the Show Details Sheets

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.

DrillDown sheet with special name

Create a Public Variable

First, you will add a code module to the workbook, and create a public variable there.

  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.
Public SheetType As String 

Create the Double-Click Macro

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: 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.

  1. Copy the following code
  2. Right-click the pivot table's worksheet tab, and then click View Code. That opens the Visual Basic Explorer (VBE).
  3. Paste the copied code onto the worksheet module, below the Option Explicit line (if there is one), at the top of the code module:
  4. Leave the VBE open, for the next step.
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

How the Double-Click Macro Works

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)..

If the cell that you double-clicked 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. go to top

Add the Workbook Event Code

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.

  1. Copy the following code.
  2. In the VBE, you should see a list of workbooks at the left , in the Project Explorer.
  3. Find your workbook in that list, and click the + sign at the left of its name, if necessary, to see the Microsoft Excel Objects.
  4. Double-click the ThisWorkbook object for your workbook, to open its code module.
  5. ThisWorkbook module

  6. Add the copied code to the workbook module, below the Option Explicit line (if there is one) at the top of the code module:
  7. Leave the VBE open, if you also want to add the Delete sheets code
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

How the New Sheet Macro Works

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_.

This makes the sheets easy to identify, and they can be deleted manually or automatically when the workbook closes. go to top

Delete Drilldown Sheets Automatically

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.

Follow these steps to add the code that deletes them.

  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 New Sheet code that you added earlier.
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

How the Delete Sheets Macro Works

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.

delete show detail sheets message

Then, click Save, when prompted, to save and close the workbook.go to top

save the workbook

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

The zipped file is in xlsm format, and contains macros. Enable macros when you open the file, if you want to test the macros.go to top

More Pivot Table Resources

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Summary Functions

Clear Old Items in Pivot Table

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: August 29, 2016 1:18 PM
Contextures RSS Feed