Some Excel features hide in plain sight. They are there, but nobody thinks to click the button. In this post, we are covering one of those features. We are looking at how to create separate sheets for each item in a PivotTable field with the Show Report Filter Pages feature.
Excel users love PivotTables because they are a quick and easy method for summarizing data without any formulas. Often the underlying data includes different cost centers, managers, or departments. Therefore, it’s common to duplicate the PivotTable and change the filters so that each item has a separate tab with its own view.
STOP! Don’t go any further with this manual work. PivotTables have a secret hidden feature called Show Report Filter Pages, which automatically creates those views for us.
Table of Contents
Download the example file: Join the free Insiders Program and gain access to the example file used for this post.
File name: 0142 Create sheets from PivotTable items.zip
Watch the Video
Show Report Filter Pages
The Show Report Filter Pages feature is found in PivotTable Analyze > Options > Show Report Filter Pages…
For each unique item in the Pivot field:
- A new worksheet is created containing the Pivot Table.
- The worksheet is named based on the field value
- The PivotTable is filtered to the field value.
It doesn’t matter how many items we have; it will create a report for each of them. If we have a lot, it might take quite a while to run and probably won’t give the best user experience. But, it can, if necessary.
Let’s dig a bit deeper into this feature to understand how it works.
Setup: Add the fields to the Filter section
I’m assuming you’ve already got a PivotTable created. Identify the field you wish to create individual worksheets for. Add that field to the Filters section.
As shown above, we’ve added the Region field to the Filters section.
Note: This technique only works with items in the Filters section.
Execute: Run the Short Report Filter Pages
Before doing anything else… make sure the PivotTable is exactly how you want it. The current style, layout, and existing filters are replicated for each worksheet created.
- Select a cell in the PivotTable to reveal the PivotTable Analyze ribbon.
- Click PivotTable Analyze > Options > Show Report Filter Pages…
- The Show Report Filter Pages dialog box appears. Select the field to create the reports for.
- Click OK.
BOOM! The worksheets are created with unique names, and the PivotTables are pre-filtered for each item.
Notes
There are a few things to be aware of when using this feature.
Shared Pivot Cache
The PivotTables all share the same Pivot Cache. Therefore, refreshing data for one PivotTable, updates all the other PivotTables.
Changes are not shown in all PivotTables
While data is shared between PivotTables, any other changes are not. Each PivotTable is a separate object and does not share formatting or layouts. This is why it’s essential to get it right at the start.
If you wish to change lots of PivotTables, it is easier to delete all the additional sheets and re-generate them using the same process.
Other objects are not copied?
The feature creates a new sheet and adds a PivotTable to it. Therefore, any other objects on the master worksheet are not copied.
Even though you may consider them part of the PivotTable, slicers, and PivotCharts are not copied.
Sheet Names
The sheets are created in alphabetical order.
If a worksheet with that name already exists, the new worksheet will be created with a number (e.g., Sheet(1))
What about Data Model PivotTables?
Show Report Filter Pages works for standard PivotTables only. Any PivotTables created using Power Pivot or a connected Data Model do not have this feature.
Using multiple Filter fields?
Before you ask… no, this doesn’t work with multiple Filter fields. The Show Report Filter Pages dialog box only allows a single field selection.
Nice try though 😁
Copying all worksheet objects
We noted above that Show Report Filter Pages does not copy all the sheet objects. So if we want to copy those, we must turn to a VBA Macro, or an Office Script.
VBA Code
Copy the following into a standard code module. Select the sheet with the PivotTable, then run the code.
An input box asks us to enter the name of the field to use.
The code comments provide additional detail about each section.
Sub pivotTableWsCopy()
Dim ws As Worksheet
Dim newWs As Worksheet
Dim pvt As PivotTable
Dim newPvt As PivotTable
Dim pvtField As PivotField
Dim newPvtField As PivotField
Dim pvtFieldName As String
Dim pvtItem As PivotItem
'On Error Exit Sub, not a PT page
On Error Resume Next
Set ws = ActiveSheet
Set pvt = ws.PivotTables(1)
If Err.Number <> 0 Then Exit Sub
'Capture the name of the Pivot Field
pvtFieldName = Application.InputBox(Prompt:="Apply based on which field?", Title:="PivotTable", Type:=2)
'Escape if user clicks Cancel
If pvtFieldName = "False" Then Exit Sub
'Set the Pivot Field
On Error Resume Next
Set pvtField = pvt.PivotFields(pvtFieldName)
If Err.Number <> 0 Then
MsgBox pvtFieldName & "does not exist in the PivotTable"
Exit Sub
End If
'Turn off message when deleting sheet
Application.DisplayAlerts = False
'Delete any existing PT sheets
For Each pvtItem In pvtField.PivotItems
On Error Resume Next
Sheets(pvtItem.Name).Delete
On Error GoTo 0
'Copy the worksheet
ws.Copy After:=ActiveWorkbook.Sheets(Sheets.Count)
Set newWs = ActiveWorkbook.Sheets(Sheets.Count)
newWs.Name = pvtItem.Name
'Change the PivotItem
Set newPvt = newWs.PivotTables(1)
Set newPvtField = newPvt.PivotFields(pvtFieldName)
newPvtField.ClearAllFilters
newPvtField.CurrentPage = pvtItem.Name
Next
'Turn on alerts
Application.DisplayAlerts = True
'Select the worksheet
ws.Activate
End SubOffice Script
We also have an Office Scripts solution.
Copy the following code into the script editor. Select the sheet with the PivotTable, then run the script.
The execution is similar to the VBA method, but since Office Scripts don’t have an easy way to display a form for user input, it uses the first field in the Filters section.
The comments provide additional detail about each section.
function main(workbook: ExcelScript.Workbook) {
let ws = workbook.getActiveWorksheet();
let newWs: ExcelScript.Worksheet;
let pvt: ExcelScript.PivotTable;
let newPvt: ExcelScript.PivotTable;
let pvtItemName: string;
//On Error return, not a PT page
try {
pvt = ws.getPivotTables()[0];
} catch {
return;
}
//Get the Field Name
let pvtField = pvt.getFilterHierarchies()[0].getFields()[0];
let pvtFieldName = pvtField.getName();
//Loop through all Items in Field
for (let i = 0; i < pvtField.getItems().length; i++) {
//Get the name of the Item
pvtItemName = pvtField.getItems()[i].getName();
//Delete worksheet if exists
try {
newWs = workbook.getWorksheet(pvtItemName);
newWs.delete();
} catch {};
//Create the new worksheet
newWs = ws.copy(ExcelScript.WorksheetPositionType.after, ws);
newWs.setPosition(workbook.getWorksheets().length - 1);
newWs.setName(pvtItemName);
//Change PivotTable Item
newPvt = newWs.getPivotTables()[0];
newPvt.getFilterHierarchy(pvtFieldName).
getPivotField(pvtFieldName).applyFilter({
manualFilter: {
selectedItems: [pvtItemName]
}
});
}
}Conclusion
In this post, we’ve seen how to create separate sheets for each item in a PivotTable field. All it takes is a few clicks and the Show Report Filter Pages feature does all the work for us.
However, if you wish to copy other worksheet objects, the VBA and Office Scripts may better suit your needs.
Related Posts:
- How to create a PivotTable from multiple Tables (easy way)
- Clear old items from a PivotTable filter list
- Excel VBA for PivotTables
Discover how you can automate your work with our Excel courses and tools.
The Excel Academy
Make working late a thing of the past.
The Excel Academy is Excel training for professionals who want to save time.