Filter Pivot Table Source Data in Excel

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.

Download the Sample File

Héctor’s code is shown here, and you can download the sample file to filter a pivot table’s source data. There is also a sample file with a shorter version of the code.