How to Duplicate a Pivot Table | Copy That Holds Together

Duplicating a PivotTable is straightforward: select the entire table, copy it, then paste it into the target sheet or workbook.

Duplicating a Pivot Table in Excel is simpler than most people expect. Whether you need an identical copy on the same sheet, a duplicate in another workbook, or one filtered version per region, Excel provides direct methods that preserve your layout and keep the data source link intact. Knowing which approach fits your situation saves time and prevents the common headaches that catch most users.

Duplicating a PivotTable: Three Methods That Work

Excel offers three reliable ways to duplicate a PivotTable. The standard copy-and-paste method works for most quick copies, sheet duplication preserves everything including slicers and filters, and the Show Report Filter Pages feature creates one copy per filter item automatically. All three keep the original data source connected, so refreshing any copy updates them all.

The Standard Copy-and-Paste Method

Click any cell inside the PivotTable, then go to the PivotTable Analyze tab. Click Select and choose Entire PivotTable. Press Ctrl+C to copy. Navigate to the destination cell and press Ctrl+V to paste. The paste carries over all labels, values, formatting, and any active filter states from the original.

If you only need static numbers rather than an interactive table, paste as values instead. But for a working duplicate you can refresh later, the standard paste is the right call.

How to Copy a PivotTable to Another Sheet

After copying the entire PivotTable using the steps above, click the sheet tab for the destination sheet at the bottom of the Excel window. Select the cell where you want the top-left corner of the PivotTable to appear and paste. The new PivotTable shares the same underlying data source as the original, so refreshing one updates the other.

If you need the duplicate to pull from a different data range, right-click the copied PivotTable, choose PivotTable Options, and use Change Data Source to point it to new data.

How to Copy a PivotTable to Another Workbook

Copying to a separate workbook follows the same core steps but adds one critical check. Copy the entire PivotTable from the source workbook, open the destination workbook, and paste it in the desired location. The pasted table still points to the original workbook’s data source. If the source data lives in the new workbook, use Microsoft’s guidance on changing the data source to reconnect it. Otherwise, create a fresh PivotTable from the target data.

This is where most users hit a snag. The recommended habit is to verify the data source immediately after pasting by right-clicking the PivotTable and checking Change Data Source before investing time in formatting and layout adjustments.

Can You Duplicate a PivotTable by Copying the Sheet?

Yes, and this method preserves every detail including filters, slicers, and timelines. Right-click the sheet tab at the bottom of the Excel window and choose Move or Copy. Check Create a copy, select where to place the new sheet, and click OK. The copied sheet contains the same PivotTable with all its settings intact. This is the most thorough option when you want the entire sheet layout duplicated, not just the PivotTable.

The table below compares each duplication method so you can pick the right one for your situation.

How to Create Multiple PivotTables from One Filter

When you need a separate PivotTable for each item in a filter field—one sheet per sales region, for example—Excel’s Show Report Filter Pages automates the work. Click anywhere in the existing PivotTable, go to the PivotTable Analyze tab, click the dropdown next to Options (or PivotTable in older versions), and choose Show Report Filter Pages. Select the filter field you want to split by, and Excel creates one new sheet per filter item, each with its own PivotTable.

This feature works across Excel 2007 through the current version. The menu label reads Options in Excel 2007 and 2010, then shifts to Analyze starting with Excel 2013.

What Happens to the Data Source After Copying?

A copied PivotTable retains the same underlying data source as the original. If the raw data changes—new rows get added or existing values get updated—the duplicate PivotTable will not show those changes until you refresh it. Right-click any cell in the copied PivotTable and choose Refresh to pull in the latest data. If the source data moves or gets renamed, update the path using Change Data Source in the PivotTable Analyze tab.

The table below covers the common pitfalls people run into when duplicating PivotTables.

Choosing the Right Duplication Method

For a single duplicate on the same sheet or another sheet, the standard copy-and-paste method with Entire PivotTable selected is the fastest. If you need the full sheet layout including slicers and timelines, duplicate the sheet using Move or Copy. When you need one copy per filtered group, Show Report Filter Pages eliminates repetitive manual work. And for moving a PivotTable to another workbook, paste first, then immediately verify the data source connection using Change Data Source.

Each method produces a working duplicate that can be refreshed independently, but the original and the copy share the same data source. Plan for that link, and you will avoid the most common frustrations.