Delete Excel Drilldown Sheets Automatically

With the pivot table Show Details feature in Excel, a new sheet is inserted when you double-click on the value cell in a pivot table.

It’s a great feature for drilling into the details, but you can end up with lots of extra sheets in your workbook.

Usually, you don’t 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 – XShow_ – when these detail sheets are created.

That prefix should make the sheets easier to find and delete.

Automatically Delete the Sheets

To make the cleanup task even easier, you can use event code to prompt you to delete those sheet when you’re closing the workbook.

If you click Yes, all the sheets with the XShow_ prefix are deleted. Then, click Save, to save the tidied up version of the workbook.

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 Pivot Table Drilldown sample file.
________________