Excel Pivot Table: Generate Multiple Sheets?

Charts & pivot tables · Updated November 22, 2026

Yes, you can generate multiple sheets from a single Excel pivot table, but not with a built-in one-click command. The standard approach is to use the “Show Report Filter Pages” feature, which creates a separate worksheet for each item in a filter field, or to manually copy and filter the pivot table for each category. For more control, you can use a simple VBA macro to automate the process.

Why Would You Want Multiple Sheets?

Pivot tables summarize large datasets, but a single table can become cluttered when you need to view data for many categories—like regions, products, or sales reps. Splitting the output into separate sheets lets you:

  • Share one sheet per department or team without revealing other data.
  • Print each category on its own page for reports.
  • Apply different formatting or formulas to each category’s summary.
  • Reduce scrolling and make the data easier to navigate.

For example, a sales manager with data for 10 regions might prefer 10 sheets, each showing one region’s monthly totals, instead of one large table with a region filter.

Method 1: Show Report Filter Pages (Built-in)

This is the quickest built-in method, and it works in Excel for Microsoft 365, Excel 2021, 2019, and earlier versions on Windows and Mac. It requires that your pivot table has at least one field in the Filters area (the top box in the PivotTable Fields pane).

  1. Create your pivot table as usual (Insert > PivotTable).
  2. Drag a field—say “Region”—into the Filters area.
  3. Click anywhere inside the pivot table to activate the PivotTable Analyze tab.
  4. On the Ribbon, go to PivotTable Analyze > Options > Show Report Filter Pages.
  5. In the dialog box, select the filter field (Region) and click OK.

Excel instantly creates a new worksheet for each unique item in that field. Each new sheet contains a copy of the pivot table with the filter set to that item. The original sheet remains unchanged. Note: this works only with fields in the Filters area, not with fields in Rows or Columns.

Method 2: Manual Copy and Filter

If you need only a few sheets, or if your data doesn’t have a filter field, you can copy the pivot table manually:

  1. Select the entire pivot table (click and drag, or press Ctrl+A twice).
  2. Press Ctrl+C to copy.
  3. Go to a new worksheet (click the + at the bottom).
  4. Right-click cell A1 and choose Paste Special > Paste as PivotTable (or just paste normally; the pivot table will still function).
  5. Use the filter arrows on the new pivot table to select the desired category.

Repeat for each category. This gives you full control over which categories appear and allows you to place them on any sheet you like. The downside is time—it’s impractical for dozens of categories.

Method 3: VBA Macro for Full Automation

For repetitive tasks, a small macro can automate the process. This is especially useful if you need to refresh the sheets every time the source data changes. Here’s a simple macro that creates a new sheet for each item in a filter field:

Sub CreateSheetsFromPivotFilter()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim ws As Worksheet
    Dim item As PivotItem
    Dim newWs As Worksheet
    
    ' Set the pivot table (adjust sheet name and pivot name as needed)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set pt = ws.PivotTables(1)
    
    ' Assume the filter field is named "Region" - change as needed
    Set pf = pt.PivotFields("Region")
    
    ' Loop through each item in the filter field
    For Each item In pf.PivotItems
        ' Create a new sheet named after the item (truncate if too long)
        Set newWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        newWs.Name = Left(item.Name, 31) ' Excel sheet names max 31 chars
        
        ' Copy the pivot table to the new sheet (simple copy)
        pt.TableRange2.Copy
        newWs.Range("A1").PasteSpecial Paste:=xlPasteAll
        
        ' Set the filter on the new pivot table (the pasted one is at A1)
        newWs.PivotTables(1).PivotFields("Region").CurrentPage = item.Name
    Next item
    
    Application.CutCopyMode = False
    MsgBox "Done"
End Sub

Important: Before running any macro, save a copy of your workbook (File > Save As) because macros cannot be undone with Ctrl+Z. Also, note that the macro above pastes the pivot table as a static copy—if you want it to remain connected to the source data, you’d need to use PasteSpecial xlPasteValuesAndNumberFormats or paste as a pivot table link. For most users, the built-in method is safer and easier.

How to Choose the Right Method

  • Built-in (Show Report Filter Pages) – Best for quick, one-time splits with a filter field. No coding needed.
  • Manual copy – Good for a few sheets or when you need to customize each sheet’s layout.
  • VBA macro – Ideal for recurring tasks, many categories, or when you need to integrate with other automation.

If your pivot table has multiple filter fields, the built-in method will create sheets for each combination of items across all filter fields—this can explode into hundreds of sheets, so use it with a single filter field.

Common Problems and Fixes

  • “Show Report Filter Pages” is greyed out: This happens when the pivot table has no fields in the Filters area. Drag a field into Filters first.
  • Sheet names too long: Excel limits sheet names to 31 characters. The built-in method automatically truncates, but VBA must handle this manually (as shown above).
  • New sheets don’t update when source data changes: The built-in method creates copies that are independent—they won’t refresh with the original. To keep them in sync, you’d need to re-run the process or use a dynamic approach with formulas.
  • Pivot table cache issues: If you copy a pivot table, it shares the same cache as the original. Changing one might affect the other. Use PivotTable Options > Data > Save source data with file to manage this.

Alternatives to Multiple Sheets

Before splitting into many sheets, consider whether a single sheet with slicers or a pivot chart might be better. Slicers let viewers filter interactively without creating extra sheets. You can also use the GETPIVOTDATA function to pull specific values from a single pivot table into a summary sheet, avoiding duplication entirely.

For example, with a pivot table in Sheet1 that has Region in the filter and Sales in values, you can write in another cell:

=GETPIVOTDATA("Sum of Sales", Sheet1!$A$3, "Region", "West")

This returns the sales for the West region without creating a separate sheet. This is often more efficient than generating many sheets, especially if you only need a few key numbers.

Frequently Asked Questions

Does Show Report Filter Pages work in Excel for the web?

No. The feature is not available in Excel for the web. You’ll need to use the desktop version (Windows or Mac) to split a pivot table into multiple sheets. In Excel for the web, you can manually copy and filter, but there’s no one-click option.

Can I generate sheets based on row labels instead of filter fields?

Not directly. The built-in method only works with fields in the Filters area. If your category is in Rows, you’d need to move it to Filters first, or use VBA to loop through row items and create sheets.

Will the new sheets update if I change the source data?

No. The sheets created by Show Report Filter Pages are static copies—they don’t refresh automatically. If you change the source data, you must re-run the process or refresh the original pivot table and then re-split. For dynamic updates, use a macro that re-creates the sheets on each refresh.

How do I avoid creating too many sheets?

Limit the filter field to a small number of items. If you have a field with 100 unique values, you’ll get 100 sheets. Consider using a slicer or a single sheet with conditional formatting instead.

Can I create sheets for only selected items?

Yes, with VBA. You can loop through a predefined list of items and create sheets only for those. The built-in method always creates sheets for all items.

Is there a way to split a pivot table into separate workbooks?

Yes, but that’s a different process. You can use VBA to copy each sheet to a new workbook, or use the “Move or Copy” feature manually. The built-in method only creates sheets within the same workbook.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.