To see the detail behind a number in a pivot table, you can double-click a data cell in the pivot table. This creates a new worksheet, with the related records from the source data. This technique can be helpful when troubleshooting, but you can end up with many extra sheets in your workbook, and you’ll need to delete all the excess sheets.
To avoid all these extra sheets, Héctor Miguel Orozco Díaz has written a macro that applies a filter to your source data, if it is a list in the same workbook as the pivot table.
NOTE: If you are trying to build a pivot table, using only the visible rows in the source data, go to this article — Pivot Table from Visible Rows in List. (Excel for Office 365)
How The Code Works
The macro applies a filter that is based on the pivot items connected to the cell that you double-clicked. For example, if you double-click the cell circled in screenshot below:
The source data is filtered for the following items, that match the items in the cell that was double-clicked.
- Class_A
- Month_3
- Store_1
- Code_A
Download The Sample File
To see Hector’s code and see how it works, download the zipped sample file from my Contextures website. There are two versions of the file — one has long code, and the other has a shorter version.
Related Articles
Pivot Table from Visible Rows in List
Find and Fix Pivot Table Source Data
_________________________