In Excel for Microsoft 365 on Windows, select the PivotTable, open PivotTable Analyze > Options, and choose Show Report Filter Pages. Select a field in the dialog, and Excel creates a separate worksheet with a copy of the PivotTable for each item in that report filter field.
What Show Report Filter Pages Does
Show Report Filter Pages is a command for splitting a filtered PivotTable into multiple worksheet views. It uses a field placed in the PivotTable’s Filters area, also called a report filter or page field. For each item in the chosen field, Excel makes a worksheet containing a PivotTable filtered to that item. For example, a report filtered by Region can become separate sheets for North, South, East, and West.
The command creates worksheets; it does not merely display a different filter selection on the current sheet. The original PivotTable remains in place, and the new sheets give readers a convenient way to inspect one category at a time. Each generated report is still a PivotTable, not just a pasted image or a fixed block of values.
This is useful when you need to distribute or review consistent reports for many categories. It saves the repetitive work of changing a filter, copying the PivotTable, and naming a worksheet for every item. It is less useful when there are hundreds or thousands of items: a workbook with that many sheets can become unwieldy.
Requirements Before You Run the Command
The PivotTable must have at least one field in the Filters area. A field placed only in Rows, Columns, or Values is not a report filter for this command. If the command is unavailable, first check the field layout and confirm that you have clicked inside the PivotTable rather than in an unrelated cell.
It also helps to prepare the source data and report before creating the pages. Check that the source has clear column headings, that the filter field contains the categories you expect, and that the PivotTable has been refreshed if the source changed. Decide whether the current layout, number formats, subtotals, and grand totals are appropriate for every generated sheet; the command repeats the report rather than designing a different layout for each category.
- Make sure the intended category field is in the Filters area.
- Refresh the PivotTable if recent source-data changes have not appeared.
- Consider saving a copy first if you are unsure how many sheets the operation will create.
- Check the workbook for existing sheets with names that could be confused with the new report pages.
How to Show Report Filter Pages in Excel
- Click any cell inside the PivotTable you want to split into report pages.
- On the ribbon, select PivotTable Analyze. This contextual tab appears when the PivotTable is selected.
- In the PivotTable group, open Options and select Show Report Filter Pages. In some ribbon layouts, the Options control is a drop-down button.
- If Excel displays the Report Filter Pages dialog, choose the field whose items should each receive a worksheet, then select OK.
- Wait while Excel creates the sheets, then inspect the new tabs and verify that each PivotTable shows the intended category.
If there is only one eligible report filter field, the dialog may offer only that field or proceed with it directly. When there are several fields in the Filters area, choose the one that should define the pages. The command handles one chosen field at a time; it does not create every possible combination of all report filters.
Example: One Worksheet per Region
Suppose a sales PivotTable summarizes Revenue by Product, with Region in the Filters area. The source contains sales for North, South, East, and West. Running Show Report Filter Pages and choosing Region creates a sheet for each region item. Each page keeps the same Product and Revenue arrangement, while the report filter is set to the corresponding region.
This does not mean the PivotTable will show only one product or that Excel will build a different analysis for each region. The report layout is essentially repeated, and the selected filter value changes. A reader can therefore compare the same measures across the resulting sheets. If the source contains an unexpected category such as a blank region, inspect the output and correct the source or filter selections as needed.
A different choice changes the meaning of the pages. If Month is in Filters and Region is in Rows, selecting Month creates a page per month, with regions still listed within each report. If you want one sheet per region, Region needs to be in the Filters area and selected as the page field.
What Happens to the New Sheets
Excel creates a separate worksheet for each item it processes in the chosen filter field and sets the PivotTable’s filter to that item. The worksheet names are based on the item labels where possible. Excel worksheet names have restrictions, including a 31-character limit and characters that cannot be used in a sheet name, so review names if labels are long or contain punctuation.
The generated reports are PivotTables and can generally be explored or formatted as PivotTables. They are not guaranteed to be independent snapshots of the source. If the source data changes, refresh the relevant PivotTables to update their summaries. Refreshing a report does not create a new worksheet for a category that has appeared since the pages were made; run Show Report Filter Pages again if you need a page for a newly added item.
Creating many pages can enlarge the workbook and make navigation harder. Before running the command on a field with many distinct values, consider whether a smaller selection, a slicer, or a single interactive PivotTable would serve readers better. Save a copy first if the workbook is important, particularly when the output will add many worksheets.
Common Problems and Practical Fixes
- The command is missing or disabled: Click inside the PivotTable and confirm that a field is in the Filters area. A field in Rows or Columns does not qualify. Also confirm that the selected object really is a PivotTable.
- The wrong type of pages appear: In the dialog, choose the field that should define one sheet per item. If the desired field is not listed, move it into the Filters area in the PivotTable Fields pane first.
- There are more sheets than expected: The selected field may contain more distinct items than the visible portion of its filter list suggests. Check the source categories, including blanks and spelling variations such as “West” and “west,” and review the generated tabs.
- A new category is missing: Refresh the PivotTable and check that its source range includes the new records. Then run Show Report Filter Pages again if a separate worksheet is needed for the added category.
- Sheet names are confusing: Long, similar, or restricted labels can make tabs hard to identify. Rename the worksheets after creation, using short descriptive names, and verify that the PivotTable filter on each sheet still matches its label.
- The workbook takes too long to use: A high-cardinality field can produce a large number of sheets. Cancel or avoid generating a page for every item and use an interactive filter or a narrower category instead.
How It Differs from Filters, Slicers, and Manual Copies
A report filter changes which records a single PivotTable summarizes. Show Report Filter Pages uses that same kind of field to create multiple worksheet views in one operation. It is therefore a batch-reporting feature, not a new kind of calculation.
A slicer is an on-sheet set of buttons for filtering a PivotTable interactively. It is often better for a reader who wants to switch categories while staying on one worksheet. A report filter is more compact, while separate report pages are useful when each category needs its own tab or needs to be reviewed independently.
Manual copying gives you more control over each report’s placement and content, but it takes longer and can lead to inconsistent layouts or filter settings. Show Report Filter Pages is faster when every page should use the same PivotTable structure. For a small number of categories that need customized commentary or different charts, manual pages may be more appropriate.
Windows, Mac, and Excel for the Web
These steps describe Microsoft 365 desktop Excel for Windows. On Excel for Mac, select the PivotTable and look for the command under the PivotTable Analyze tab’s Options controls; the ribbon arrangement can differ by release. There is no need to use a keyboard shortcut for this feature, and a specific shortcut should not be assumed.
Excel for the web has a more limited set of PivotTable tools than the desktop application, and the Show Report Filter Pages command may not be available there. If you cannot find it in the web interface, open the workbook in the desktop version of Excel and use the PivotTable Analyze ribbon. The resulting workbook can then be saved and reopened in the web app, although availability of PivotTable editing features can still vary.
Frequently Asked Questions
Can I show report filter pages for more than one field at once?
The command creates pages based on one selected report filter field at a time. It does not automatically generate a worksheet for every combination of two or more filter fields. To report on another field, run the command again and review the additional output.
Does Show Report Filter Pages create static values?
No. The generated sheets contain PivotTables filtered to individual items. They can be refreshed as PivotTables; they are not simply pasted values. Keep the source and refresh behavior in mind if the workbook will be updated later.
Will new source categories automatically get new worksheets?
No. Refreshing updates PivotTable data, but it does not automatically make a new report-page sheet for every newly added category. Run Show Report Filter Pages again when you need separate pages for new items.
Can I use the command if my field is in Rows?
Not as a report-page field. Move that field into the Filters area in the PivotTable Fields pane, then select Show Report Filter Pages and choose it in the dialog.
Why did Excel create many worksheets?
The chosen field has many distinct items, and Excel created a report page for each item it processed. Check for unexpected categories, blanks, or inconsistent labels. If the count is too large, use a slicer or filter on one PivotTable instead.
Related
- Excel Monthly Employee Work Schedule: Build a Clear Plan
- Excel MIN and MAX Dates: Find the Earliest and Latest
- Excel Merge Shortcut: How to Merge Cells in Microsoft 365
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Column Chart in Excel: Definition, Uses, and ExamplesA column chart in Excel is a chart that uses vertical bars to compare values across categories or…
- Create an Org Chart From Excel Data in ExcelYes. In Microsoft Excel for Microsoft 365 on Windows, you can create an org chart with Insert >…
- Copy Sparklines from Excel to PowerPoint: Best MethodsTo copy sparklines from Excel to PowerPoint, select the cells that contain them, use Excel’s Home > Copy…
- Excel Pie Chart Legend on Slices: How to Add LabelsYes. In Excel for Microsoft 365 on Windows, select the pie chart, then choose Chart Design > Add…
- Excel Pivot Table Error: Causes and FixesIf Excel shows an error while creating a PivotTable, first check that the source data is one continuous…
- Waterfall Chart in Excel: Setup and TroubleshootingTo set up a waterfall chart in Excel for Microsoft 365 on Windows, put each step and its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.