Filter the Source Data For a Pivot Table

Pivot Filter

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:

Pivot Filter

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

Pivot Filter 2

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

Re-create the Source Data

_________________________

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.