When you double-click a pivot table value cell, Excel adds a new sheet to your workbook, with the details for that summary value. To make it easy to find those sheets, and delete them later, use the latest version of my macros to rename Pivot Table DrillDown sheets.
Show Details Command
Another way to create a DrillDown sheet is to right-click a pivot table value cell, and click the Show Detail commands.
Turn On or Off
There is a setting in PivotTable options, on the Data tab, where you can turn off this feature, if you don’t want people to use it.
In this screen shot, I’ve removed the check mark, so a message will appear if anyone double-clicks a value cell.
Show Details or Drilldown
If you record a macro while you change that setting, you’ll see that Excel calls the feature Drilldown in the background.
That’s why I call these the DrillDown macros. And I capitalize the second D, just so it matches my initials!
DrillDown Macros – Old
Long ago, I posted a set of DrillDown macros, to help manage the details sheets. Instead of leaving Excel’s default names for the sheets, the macro adds a prefix, “XShow_” at the start of the name.
That makes it easy to find the sheets, and there is another macro that deletes all the sheets that have a name with that prefix.
Updated Macros
Last week, Rich E. asked a few questions about the old DrillDown macros, and that inspired me to take another look at them.
- For the macro that names each sheet, Rich asked if the prefix could be different, and if the tab colour could be set.
- For the Delete sheets macro, Rich asked about saving some or all of the sheets, when closing the workbook.
Those were great questions, and I created a new download file, with custom settings for the DrillDown macros.
DrillDown Macro Settings
Here are the custom setting cells on the sample file’s Admin sheet
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: 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.
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 the new 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.
Get the Sample File
To see the macros to rename Pivot Table DrillDown sheets, go to the DrillDown page on my Contextures website.
In the Download section, click the link for the Custom settings workbook (the second sample file.)
The zipped workbook is in xlsm format, and contains the macros. Be sure to enable macros, if you want to test them.
_________________
I just created a drilldown sheet by double-clicking a pivot table, the way one does. I looked at it and for some reason clicked Ctrl-z (Undo) a couple times. I then went to delete the drilldown sheet, as one does, at least one who doesn’t use your no-doubt excellent code, and noticed that there was no sheet to delete.
I confirmed this behavior – Undo gets rid of the drilldown sheet. My question is, has that always been the case?
Thanks, Doug, and I hadn’t noticed that before!
There’s no Undo option in Excel 2003, after creating the drilldown sheet.
There is an Undo in Excel 2010, and I don’t have Excel 2007 installed, to test that version.
Thanks Debra. I guess it’s just never came up before. I’ve also recently noticed that you can undo refreshing pivot tables and, I think, data-connected tables. Pretty amazing!
So I guess you could change your code to just Undo until the sheet is gone. 😉