When you’re analyzing data in an Excel pivot table, you might want to see the detail behind one of the numbers. To extract the data, you can double-click a data cell and a new worksheet is created, with the related records.
This is a nice feature, but you’ll end up with extra sheets in your workbook, and will need to clean things up occasionally.
Filter the Source Data
If the pivot table source data is in the same workbook, you can use the following macro, written by Héctor Miguel Orozco Díaz. It filters the source data, based on the pivot items connected to the double-clicked cell.
For example, if you double-click the cell circled in screenshot below:
the source data is filtered for Class_A, Month_3, Store_1, Code_A cost.
This lets you focus on the detail records, without creating new worksheets.