How To Create Sheets For Each Item In PivotTable Field

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.

Download the example file: Click the link below to download the example file used for this post:

Watch the Video

Watch the video on YouTube

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.

1. Select a cell in the PivotTable to reveal the PivotTable Analyze ribbon.
2. Click PivotTable Analyze > Options > Show Report Filter Pages…
   
3. The Show Report Filter Pages dialog box appears. Select the field to create the reports for.
4. 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 Sub

Office 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