Excel Pivot Table DrillDown Show Details - Contextures

• Topics
  • Charts
  • Data Entry
  • Data Validation
  • Filters
  • Formatting
  • Formulas
  • Macros
  • Pivot Tables
  • Sample Data
  • Sample Files
  • Tutorial Index

Home > Pivot > Layout > Show Details Excel Pivot Table Show Details Double-click a pivot table value, to create a new sheet with the records used in that total. That is the Excel Show Details feature. If you use this feature frequently, save time with my macros that name the new sheets, and ask if you want to delete them when you're closing the workbook.

Show Records With DrillDown

DrillDown Warnings

How to Extract Pivot Table Records

Name Show Details Sheets - Basic

--Delete Drilldown Sheets Automatically

Name Show Details Sheets - Custom

Add Macros to Workbook

Get the Sample Workbook

Pivot Table Tutorial List

Show Records with "Show Details"

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 feature -- double-click on that cell. A new worksheet is automatically inserted, with a list of those "East 2014" records, and you can drill down into the data..

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.

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.

Show Details Warnings

There are a couple of things to keep in mind when you use the 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.

Problem With Slicers

If you have Slicers connected to the pivot table, the Show Details 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 Show Details feature. Otherwise, the Slicer filter will be ignored in the Show Details 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 Show Details 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.

Macro to Name Show Details Sheets - Basic

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.

• NOTE: This code is in the Basic download file.
• There is also a Custom Naming Macro, with more options for naming and deleting the Drill to Detail sheets.

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.

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: If your workbook has multiple pivot table sheets:

• Put this code on each pivot table sheet.
• OR, download the Multi-Pivot sample file, with the code on the ThisWorkbook module

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.

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. (optional) Paste the copied code onto the worksheet module for any other pivot tables in your workbook
5. 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.

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. Add the copied code to the workbook module, below the Option Explicit line (if there is one) at the top of the code module:
6. 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.

Delete Drill to Detail Sheets Automatically

If you use the code shown above, to name the Drill to Detail 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 Drill to Detail sheets.

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.

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

Name Show Details Sheets - Custom

In this version of the Name Show Details Sheets macro, it's easy to customize the macro settings. The sample file has an Admin sheet, where you can fill in the options that you want to use.

You can also choose a location for the new sheet – make it the first or last sheet, or leave it where it is (Do not move)

The current setting is “Last”, so the new sheet is the last tab in this workbook.

Macro to Delete the DrillDown Sheets

There is a Workbook Close procedure in the sample file, and it will delete All, Some or None of the DrillDown sheets.

• ALL – Each sheet with the designated prefix is deleted, and the Next Number is reset to 1
• None – The drilldown sheets are left as is, with nothing deleted.
• Some – The macro shows a message for each sheet that has the designated prefix, and asks if you want to delete it. The Next Number is not reset.

DrillDown Sheet Info

Another feature in this DrillDown macro is the information added to the right of the detail table.

It shows the date that the sheet was created, the pivot table name and the pivot table’s sheet name. The sheet name is hyperlinked, so you can get back there quickly.

Using the Macro

When a new drilldown sheet is created, the macro changes its name:

• The new name starts with the prefix from cell C4.
• Next is the number from C12, formatted as 3-digits.
• It ends with the name of the pivot table that was double-clicked.

▶	Note: Due to Excel limitations, only the first 31 characters of that string are used as the new sheet name.

Other Options

The Admin sheet has a tab colour cell too, and the fill colour from that is used on the new sheet’s tab.

Add Macros to Workbook

The instructions on this page show all the steps to create the Basic code for the pivot table Drill to Details 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:

1. Open the sample workbook, and the workbook where you want to copy the code
2. Be sure that your workbook is saved as xlsm or xlsb format, or the macros will not be saved
3. On the keyboard, press Alt + F11, to open the Visual Basic Editor (VBE).
4. In the Project Explorer at the left, find the sample file, and your workbook
5. If necessary, click the + signs, to see the Microsoft Excel Objects and Modules for the 2 workbooks
6. From the sample file, in the Modules folder, drag the module (named Module or modVar) onto your workbook (drop it anywhere)

Next, follow these steps to get the Workbook code

WARNING: These steps assume that there is no existing code on the ThisWorkbook module in your workbook.

1. In the sample file, right-click on ThisWorkbook, and click View Code
2. Click in the code window, and press Ctrl+A to select all the code
3. Press Ctrl+C to copy all the code
4. For your workbook, , right-click on ThisWorkbook, and click View Code
5. If there is an Option Explicit line at the top of the code window, delete that
6. Click in the code window, and press Ctrl+V to paste all the code

Next, follow these steps to get the Pivot Sheet code

WARNING: These steps assume that there is no existing code on the pivot table sheet modules in your workbook.

1. In the sample file, right-click on pivot table sheet, and click View Code
2. Click in the code window, and press Ctrl+A to select all the code
3. Press Ctrl+C to copy all the code
4. For your workbook, , right-click on ThisWorkbook, and click View Code
5. If there is an Option Explicit line at the top of the code window, delete that
6. Click in the code window, and press Ctrl+V to paste all the code
7. (optional) Paste the code onto all the other pivot table sheets in your workbook

For the Custom sample file ONLY:

If you are using the Custom file, follow this step to get the Settings worksheet. This is NOT required in the Basic file.

1. Close the Visual Basic Editor and go back to Excel.
2. In the sample file, right-click on tab for the Admin_Drill sheet, and click Move or Copy
3. In the drop down list, choose the workbook where you are adding the Drill to Details code
4. In the Before Sheet list, choose a location for the Admin_Drill sheet
5. IMPORTANT: Check the box to Create a Copy
6. Click OK, to copy the sheet to your workbook

▶	Note: This step will also add named ranges to your workbook, which you can see in the Name Manager.

Test the Code

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 Drill to Details sheet. If all the code was installed correctly, the Drill to Details 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.

Show Details vs Drill Down

There are confusing names in Excel, and DrillDown is one of them!

• 1) Drill to Details: This page is about the Pivot Table Show Details feature, which was originally called Drill to Details, back when I first started using it.
• 2) EnableDrilldown: In Excel VBA code, the Show Details feature is called Drilldown. In the screen shot below, I recorded a macro while I turned off the pivot table's "Enable Show Details" option. The pivot table property is named "EnableDrilldown".
  • 
• 3) Expand/Collapse: Excel pivot tables also have an Expand and Collapse feature, which lets you show or hide pivot items in the Row and Column areas. The expand and collapse buttons in a pivot table show a plus sign or minus sign, and are sometimes called the Drill Down button, and Drill Up button.
  • 
• 4) Quick Explore: If your pivot table is based on an OLAP cube, or the workbook Data Model, there is a different feature to use - Quick Explore, with Drill Down or Drill Up commands. This Microsoft page explains those options.

Get the Sample Workbook

IMPORTANT: See the Add Macros to Workbook section above, for the steps to copy the code from the sample files into your workbook.

1. Basic: To see how the event code names the sheets, and deletes them when closing, get the Pivot Table Drill to Details 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.
2. Custom: For more options on naming and deleting the Drill to Details sheets, get the Pivot Table Custom Drill to Details 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. See details on my Contextures Blog.
3. Multi Pivot: If your workbook has multiple pivot table sheets, get the Pivot Table Drill to Details Workbook sample file. This has the basic Drill to Details code, and instead of adding the double-click event procedure on each sheet, that code is in the ThisWorkbook code module.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Pivot Table Resources

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Summary Functions

Clear Old Items in Pivot Table

Grouping Data

Clear Old Items in Pivot Table

Last updated: December 27, 2024 11:36 AM