How to Copy a Pivot Table in Excel (And Keep It Working the Way You Want)

Copying a pivot table sounds simple — select, copy, paste. But anyone who's tried it knows the result isn't always what they expected. Sometimes the data updates when you don't want it to. Sometimes the formatting disappears. Sometimes you end up with two pivot tables sharing the same cache when you needed them independent.

Understanding what's actually happening under the hood makes the difference between a clean copy and a frustrating troubleshoot.

What a Pivot Table Actually Contains

Before copying, it helps to know what you're working with. A pivot table has three layers:

• The pivot cache — the snapshot of source data stored in memory
• The layout and field configuration — rows, columns, values, filters
• The formatting — cell styles, number formats, conditional formatting

When you copy a pivot table, these three layers don't always travel together, and how they behave depends entirely on how you copy.

Method 1: Copy and Paste Within the Same Workbook

This is the most common approach, and it has a catch worth knowing.

1. Click any cell inside the pivot table
2. Press Ctrl+A (or use the ribbon to select the entire pivot table)
3. Press Ctrl+C to copy
4. Click your destination cell and press Ctrl+V

What you get: A new pivot table that shares the same pivot cache as the original. Changes to one table's source data will reflect in both after a refresh. This is actually efficient — shared caches reduce file size — but it means the two tables are not fully independent.

If you want both tables to stay in sync with the same data source, this is ideal. If you need them to diverge, you'll need a different approach.

Method 2: Copy to a New Workbook

Moving a pivot table to a separate workbook gives it its own cache automatically.

1. Right-click the worksheet tab at the bottom
2. Select Move or Copy
3. In the dialog, choose (new book) from the "To book" dropdown
4. Check Create a copy if you want to keep the original
5. Click OK

The copied sheet — pivot table and all — lands in a fresh workbook with its own independent cache. This is the cleanest method when you need a standalone copy for sharing or archiving. 📋

Method 3: Paste as Values (Static Copy, No Pivot Functionality)

Sometimes you don't need a working pivot table at all. You just want the visual output — the numbers as they currently appear — without any pivot mechanics.

1. Copy the pivot table normally (Ctrl+C)
2. At the destination, go to Home → Paste → Paste Special
3. Choose Values (or Values and Number Formatting to preserve formats)

What you get: A plain table of numbers. It won't refresh, can't be reorganized, and has no connection to a data source. This is useful for sending reports to people who don't need (or shouldn't have access to) the underlying data.

Method 4: Duplicate with an Independent Cache

If you need two fully functional, independent pivot tables from the same source — where refreshing one doesn't affect the other — you need to break the shared cache.

The most reliable way in Excel:

1. Copy the pivot table and paste it normally (Method 1)
2. Click inside the new pivot table
3. Go to PivotTable Analyze → Change Data Source
4. Re-enter the same source range and confirm

This forces Excel to create a new, separate cache for the second pivot table. It's a small extra step, but it gives each table a fully independent relationship with the data.

Formatting Behavior: What Survives the Copy 🎨

This trips people up constantly. Here's what generally carries over — and what doesn't:

The Move or Copy Sheet method preserves the most, which is why it's often the best choice for a clean duplicate.

Google Sheets: A Slightly Different Story

If you're working in Google Sheets rather than Excel, pivot tables behave similarly but with some differences:

• Copying a pivot table within Google Sheets creates a linked copy that shares the same source range
• There's no concept of a "pivot cache" the same way Excel uses one
• Pasting as values works the same way — you lose interactivity but keep the numbers
• Moving a pivot table between Google Sheets files (tabs vs. separate spreadsheets) can break the data connection if the source data doesn't come with it

The core logic is the same: decide first whether you need a live, interactive copy or a static snapshot.

The Variable That Changes Everything

The right method depends on questions only you can answer:

• Do you need the copy to refresh independently from the original?
• Are you sharing the file with someone who shouldn't see raw data?
• Is this a one-time export or an ongoing report you'll maintain?
• Are you working in Excel desktop, Excel Online, or Google Sheets — because each handles cache and formatting slightly differently?
• Does your pivot table use Power Query, a data model, or a named table as its source? These affect how the cache behaves and whether re-linking is straightforward.

The mechanics of copying are consistent. What varies is which method actually serves your workflow — and that depends on the structure of your file and what you plan to do with the copy next.