Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use of the rarely known Pivot Tables button called Show Report Filter Pages.
Show Report Filter Pages allows us to create separate reports in new sheets based on the selection in the Pivot Tables Filters. This is one of the features in Pivot Tables that I love so much. Let’s see how to use it
In the caption below, we have Sum of Sales by Region in Tabular Pivot Tables Report Layout
Next, we want to display the report filter pages using State as the filter criteria. Therefore, we are going to drag and drop the State field name into the Filters axis
In the caption above, we can see the filter displayed in row 1. So, we only want to show report filter pages for the States of Alabama, Arizona and California. Therefore, we are going to filter to show Sum of Sales by Region for the three States.
Next, in the Pivot Table group of the Pivot Table Tools Analyze tab, click on the drop-down beside Options.
Select Show Report Filter Pages
In the Show Report Filter Pages dialogue box, just click on OK button.
Note, immediately we clicked on the OK button, all the report filtered pages for the three selected States will be displayed as a new sheet. See the caption for the report filter page for California below
We can also see the report page sheets for Alabama and Arizona including the substantive Pivot Tables sheet where we created the pages from.