Clean Up Pivot Table Show Details Sheets

When you double-click on the value cell in a pivot table, a new sheet is automatically inserted in the workbook. The new sheet contains a table, listing all the source data records that are summarized in the number that you double-clicked.

showdetails03

Double-clicking the cell is a shortcut to the pivot table Show Details command. You can also right-click on a value cell, and click Show Details.

showdetails02

Show Details, also called DrillDown, is a great feature for digging into the details, but you can end up with lots of extra sheets in your workbook.

However, you don’t usually want to save the sheets, so you manually delete them before you close the file.

Automatically Name the Sheets

With event code on the pivot table’s worksheet, and in the workbook module, you can add a prefix, such as “XShow”, when the Show Detail sheets are created.

showdetails06

That prefix should make the sheets easier to find and delete, before you close the workbook.

Automatically Delete the Sheets

To make the cleanup task even more efficient, you can use programming to show a message when you’re closing the workbook, if there are Show Details sheets in the file.

showdetails04

If you click Yes, all the sheets with the “XShow_” prefix are deleted, and the workbook stays organized. Then, save the tidied up version of the workbook, when prompted.

See the Drilldown Sheet Code

For detailed instructions on adding the drilldown sheet naming and deleting code, visit the Excel Pivot Table Drilldown page on the Contextures website.

Download the Sample Drilldown File

To see how the event code names the sheets, and deletes them when closing, you can download the sample file from my Contextures website, at the link shown above.

________________

2 thoughts on “Clean Up Pivot Table Show Details Sheets”

  1. Is there any way to change the default appearance of the Show Details Sheets? I would prefer the sheet have no color shading at all.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.