Free Practice WorkbookWhen you are using an Excel Pivot Table you can show the items within the Report Filter on separate sheets inside your workbook. Say that you have created an awesome Pivot Table which shows total sales and number of transactions per region. You can drop in your Customer field in the Report Filter and replicate the Pivot Table for each of your customers in a separate Sheet. All you need to do is click inside your Pivot Table and in the menu ribbon under PivotTable Tools choose the Options tab and then select the Options drop down and choose Show Report Filter Pages. Each of your customers will have their unique Pivot Table in a separate Sheet with their individual sales and transactional metrics.
Key Takeaways:
-
Automate Page-Wise Reports – The Show Report Filter Pages feature allows you to automatically create separate Pivot Table sheets for each item in a report filter field, saving time.
-
One-Click Multiple Reports – Instead of manually filtering and copying Pivot Tables, you can generate individual sheets for each filter value with just one click.
-
Works with Single Filter Field – This option is available when you have a report filter field added to your Pivot Table, making it perfect for fields like Region, Product, or Department.
-
Preserves Pivot Table Format – Each generated sheet retains the same layout, formatting, and structure of the original Pivot Table, ensuring consistency across reports.
-
Dynamic and Refreshable – The created sheets are linked to the original Pivot Table data, meaning you can refresh them when source data changes without rebuilding the pages manually.
Table of Contents
Our Pivot Table Setup
Here is our pivot table:
How to Show Report Filter Pages in a Pivot Table
STEP 1: Drop the Customer Field in the report filter.
STEP 2: Go to Options > Options Drop Down > Show Report Filter Pages
STEP 3: Press OK.
Each customer’s pivot table will show in a unique sheet!
Frequently Asked Questions
What is the “Show Report Filter Pages” feature in Excel Pivot Tables?
It’s a tool that automatically creates a separate worksheet for each item in the report filter, using the same Pivot Table structure for each sheet.
Where can I find the “Show Report Filter Pages” option?
Go to the PivotTable Analyze (or Options) tab → Options → Show Report Filter Pages, and select the filter field you want to split by.
Can I use multiple filter fields with Show Report Filter Pages?
No, it works with only one filter field at a time. However, you can repeat the process for different fields if needed.
Do the generated sheets stay linked to the source data?
Yes, all Pivot Tables created through this feature are connected to the same data source, so you can refresh them together when your data updates.
What happens if I add new items to the filter field later?
New filter items won’t automatically generate new sheets—you’ll need to run Show Report Filter Pages again to create reports for the newly added items.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
