Pivot Table Show Details Sheets

When you double-click on a pivot table value cell, Excel creates a new sheet, with a list of all the records that make up that total value. The double-click runs Excel’s Show Details command – it’s a helpful troubleshooting feature, but can add clutter to a workbook, because of all the sheets that it creates.

To help you keep things tidy, I created a sample file with macros that label the Show Details sheets when you create them. Then, when the workbook closes, another macro will check for those sheets, and asks if you’d like to delete them. I updated the workbook this week, so take a look, if it’s something you need.

DrillDown or Show Details

In the old days, that double-click feature was called Drill to Details. This screen shot is from Excel 2003.

In newer versions of Excel, it’s called Show Details. To see the name, right-click on a pivot table value cell. In the popup menu, you can click the Show Details command to create the list of records.

And, if you use a macro to work with Show Details, you might see it called “Drilldown” in the VBA code.

What other Excel feature has so many names?

Show Detail Sheets Macro

In the macro that I created, an event procedure runs when you double-click a cell. If that cell is in a pivot table’s Values area, the new sheet gets a name that starts with “XShow_”.

There is another event procedure, that runs when you close the workbook. It looks for sheets that begin with “XShow”. If any are found, you’ll see a message that asks if you want to delete those Show Details sheets.

You can keep the sheets if you need them, or click Yes to delete the Show Detail sheets, and keep the workbook tidy.

The details and code are on my Contextures site.

Warning – Show Detail and Slicers

In Excel 2010 and Excel 2013, there was a problem with the Show Details feature, if you also had Slicers on the pivot table. Unless the Slicer fields were also in the pivot table, the Show Detail list ignored the filters that the Slicer applied. You might get a long list of records, instead of the few that you expected.

Fortunately, the problem is fixed now, in Excel 2016. I don’t know of any updates that fixed it in the older versions though. You can read more about that problem in my old blog post on Show Details With Slicers.

And, if you’re using Excel 2010 or 2013, remember to add all the Slicer fields to the pivot table, if you plan to use the Show Details feature. Or turn the feature off – the steps are shown below.

Turn Off Show Details

If you don’t want people to view the detail records, you can turn off that feature.

• Right-click a cell in the pivot table, and click PivotTable Options
• On the Data tab, remove the check mark from “Enable Show Details”
• Click OK, to close the Options window.

Download the Sample File

To download the sample workbook, go to the Excel Pivot Table DrillDown page on my Contextures site. Scroll down to the Download section, and you’ll see a link to the workbook.

The zipped file is in xlsm format, and contains macros. Enable macros if you want to test the Show Details macros.

__________________________