How to Copy a Pivot Table in Excel?

At times, we need to copy Pivot Tables in Excel for different reasons. Sometimes, you might want an exact copy, including all the Pivot Table settings.

Other times, you might just want the numbers and formatting, leaving out those extra Pivot Table settings.

In this article, I’ll show you how to make both types of copies: one with all the Pivot Table settings and one without them.

Copy Pivot Table Using Pivot Table Options

When you want to analyze the same data in different ways, you can copy and paste the Pivot table so that you have two Pivot Tables (using the same source data), and you can slice and dice them differently.

To do this, you have to copy the Pivot Table as another Pivot Table with all Pivot Table options.

Below, you have a Pivot Table, which analyzes region-wise and country-wise sales of a company.

Now, you need to take a copy of this Pivot Table to cell D3 to analyze the sales data from a different perspective. For that, you have to follow the below steps.

1. Select any of the cells in the Pivot Table.

2. Go to the PivotTable Analyze tab.

3. Go to the “Actions” group and expand the “Select” options.

4. Select “Entire PivotTable”.

As soon as you click the above option, Excel selects the Entire Pivot Table.

5. Press “Ctrl + C” to copy the selected Pivot Table. You can also Right-click on the Pivot Table and select the “Copy” to copy the Pivot Table.

When you copy the Pivot Table, you’ll see green color dotted lines go around the Pivot Table.

6. Select the cell where you want to paste the new Pivot Table. In this case, it is cell D3.

7. Press “Ctrl + V” to paste the copied Pivot Table. Another way to Paste is to Right-click on the selected cell and select the “Paste” option.

Now, you have copied the Pivot Table as another Pivot Table.

When you select any cell of the new Pivot Table, you’ll see that Excel activates the Pivot Table-related tabs.

It is important to select the original Pivot Table using the “PivotTable Analyze” tab. The reason is if the Pivot Table has some fields as Filters, you can’t select the entire Pivot Table using the “Ctrl + A”. Then, you’ll not be able to copy the selected Pivot Table with Pivot Table options.

Also read: How to Lock a Pivot Table in Excel

Copy Pivot Table with Just Numbers and Formatting

There are times that we just want to copy only the numbers and formatting of a Pivot Table.

Excel paste option for this is to use the “Values & Source Formatting” Paste option. But, unfortunately, this command is not working properly for Pivot Tables. To overcome this drawback, we can use different methods.

For example, if you try to copy the below table using the “Values & Source Formatting” Paste option, you’ll get just the values and number formatting. Excel will not paste all source formatting.

I’ll show you some methods that you can use to copy values and source formatting of Pivot Tables.

Method 1: Copy Values and Source Formatting of a Pivot Table using Paste Options

To copy and paste values and source formatting of a Pivot Table, you can combine the following two paste options.

• Value & Source Formatting Paste option.
• Formatting Paste option.

Below, I have a Pivot Table, which analyzes region-wise and country-wise sales of a company.

Now, you need to take a copy of the values and source formatting of this Pivot Table to cell D3. For that, you have to follow the below steps.

1. Select the entire Pivot Table. You can do a manual selection or Press “Ctrl + A”.

2. Press “Ctrl + C” to copy the selected Pivot Table. You can also Right-click on the Pivot Table and select the “Copy” to copy the Pivot Table.

When you copy the Pivot Table, you’ll see green colored dotted lines go around the Pivot Table.

3. Select the cell where you want to paste the values and formatting. In this case, it is cell D3.

4. Go to the Home tab and expand the “Paste” options.

5. Click the “Value & Source Formatting” option.

6. Again, go to the Home tab and expand the “Paste” options.

7. Click the “Formatting” option.

Now, you have copied the Pivot Table with values and source formatting.

You can see that the pasted data table’s column widths do not match the original Pivot Table’s column widths. To copy the column widths, follow the below steps.

8. Again, go to the Home tab and expand the “Paste” options.

-2. Click the “Paste Special…”.

10. Select the “Column widths” option from the “Paste Special” dialog box.

11. Click the “OK” button.

Now, you have copied the numbers and all source formatting of the Pivot Table.

Also read: Standard Deviation in Pivot Tables in Excel

Method 2: Copy Values and Source Formatting of a Pivot Table using the Clipboard

You can also use the Clipboard to copy and paste values and source formatting of a Pivot Table.

Below, I have a Pivot Table that analyzes region-wise and country-wise sales of a company.

Now, you need to take a copy of the values and source formatting of this Pivot Table to cell D3. For that, you have to follow the below steps.

1. Select the entire Pivot Table. You can do a manual selection or Press “Ctrl + A”.

2. Press “Ctrl + C” to copy the selected Pivot Table. You can also Right-click on the Pivot Table and select the “Copy” to copy the Pivot Table.

When you copy the Pivot Table, you’ll see green colored dotted lines go around the Pivot Table.

3. Select the cell where you want to paste the values and formatting. In this case, it is cell D3.

4. Go to the Home tab and click the small arrow in the “Clipboard” group.

5. Click the “Paste All” button on the Clipboard.

Now, you have copied the Pivot Table with values and source formatting.

You can see that the pasted data table’s column widths do not match the original Pivot Table’s column widths. To copy the column widths, follow the below steps.

6. Go to the Home tab and expand the “Paste” options.

7. Click the “Paste Special…”.

8. Select the “Column widths” option from the “Paste Special” dialog box.

9. Click the “OK” button.

Now, you have copied the numbers and all source formatting of the Pivot Table.

Also read: How to Move Pivot Table in Excel?

Method 3: Copy Values and Source Formatting of a Pivot Table Part by Part

When you don’t copy the Entire Pivot Table, Excel will paste the copied cells with value and source formatting.

Below, I have a Pivot Table, which analyzes region-wise and country-wise sales of a company.

Now, you need to take a copy of the values and source formatting of this Pivot Table to cell D3. For that, you have to follow the below steps.

1. Select the headings of the Pivot Table.

2. Press “Ctrl + C” to copy the selected headings.

3. Select the cell where you want to paste the values and formatting. In this case, it is cell D3.

4. Press “Ctrl + V” to paste the headings.

5. Select the Pivot Table other than the headings of the Pivot Table.

6. Press “Ctrl + C” to copy the selected cells.

7. Select one row below the cell where you want to paste the values and formatting. In this case, it is cell D4.

8. Press “Ctrl + V” to paste the selected cells.

You can see that the pasted data table’s column widths do not match the original Pivot Table’s column widths. To copy the column widths, follow the below steps.

9. Go to the Home tab and expand the “Paste” options.

10. Click the “Paste Special…”.

11. Select the “Column widths” option from the “Paste Special” dialog box.

12. Click the “OK” button.

Now, you have copied the numbers and all source formatting of the Pivot Table.

If your Pivot Table has fields in filters, you can copy and paste the Pivot Table, excluding the filters. This way, you’ll copy the values and source formatting.

Also read: How to Change Data Source in Pivot Table

VBA Code to Copy Pivot Table in Excel

If you need to copy Pivot Table quite often, or if you’re working with multiple Pivot Tables, you can use some simple VBA codes to copy and paste Pivot Tables in Excel.

Let’s look at some of these codes.

Copy Pivot Table to Another Sheet

Sub CopyPivotTable()

    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim pt As PivotTable

    ' Set references to the source and destination worksheets
    Set wsSource = ThisWorkbook.Worksheets("SourceSheet")
    Set wsDestination = ThisWorkbook.Worksheets("DestinationSheet")

    ' Set a reference to the first PivotTable on the source sheet
    ' Assuming there is at least one PivotTable
    Set pt = wsSource.PivotTables(1)

    ' Copy the PivotTable range
    pt.TableRange2.Copy

    ' Paste the PivotTable to the destination sheet
    ' You can specify a range if needed, like wsDestination.Range("A1")
    wsDestination.Paste wsDestination.Range("A1")

    ' Optional: Deselect the range after pasting
    Application.CutCopyMode = False

End Sub

Let me explain how this code works:

• The code starts by declaring variables for the source worksheet (wsSource), the destination worksheet (wsDestination), and the PivotTable object (pt).
• It sets wsSource and wsDestination to the respective worksheets in the workbook.
• The first PivotTable on the source sheet is set to the pt variable. This code assumes that there is at least one PivotTable on the source sheet.
• The TableRange2 property of the PivotTable is used to copy the entire PivotTable, including data and formatting.
• The copied PivotTable is then pasted into the destination worksheet. The code pastes it into the top-left cell (A1) of the destination sheet, but you can modify this to paste into a different cell if required.
• Finally, the Application.CutCopyMode = False line is used to deselect the copied range, removing the ‘marching ants’ border that appears after a range is copied.

This code will effectively copy the first PivotTable found on your source sheet to the specified location on your destination sheet.

Remember, you can customize the sheet names and the paste location as per your specific requirements.

In case you want to copy and paste the Pivot Table in the same sheet, you can modify the code to do this as well.

Also read: How to Clear Pivot Table Cache?

Copy Pivot Table to Another Sheet (Values Only)

To copy a PivotTable to another sheet with values and formatting only, without the underlying PivotTable functionality, we can modify the VBA code slightly. The idea is to copy the PivotTable and then use the PasteSpecial method to paste values and formats separately. Here’s how you can do it:

Sub CopyPivotTableValuesAndFormats()

    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim pt As PivotTable

    ' Set references to the source and destination worksheets
    Set wsSource = ThisWorkbook.Worksheets("SourceSheet")
    Set wsDestination = ThisWorkbook.Worksheets("DestinationSheet")

    ' Set a reference to the first PivotTable on the source sheet
    ' Assuming there is at least one PivotTable
    Set pt = wsSource.PivotTables(1)

    ' Copy the PivotTable range
    pt.TableRange2.Copy

    ' Paste values and then formats in the destination sheet
    With wsDestination.Range("A1")
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With

    ' Optional: Deselect the range after pasting
    Application.CutCopyMode = False

End Sub

• The initial steps are the same: setting references to the source and destination worksheets and the PivotTable.
• The TableRange2 of the PivotTable is copied, which includes the data and the formatting of the PivotTable.
• In the destination worksheet, the PasteSpecial method is used twice:
• First, it pastes the values with xlPasteValues. This ensures that the values of the PivotTable are pasted without any PivotTable functionality.
• Second, it pastes the formats with xlPasteFormats. This preserves the formatting of the PivotTable.
• The With wsDestination.Range(“A1”) statement is a shorthand to avoid repeating the paste destination.
• The Application.CutCopyMode = False line clears the clipboard and removes the selection, making the sheet clean.

This modified code will copy only the values and formatting of the PivotTable to the destination sheet, removing any links to the original data or PivotTable functionality. This is useful when you want a static representation of your PivotTable’s current state.

Now you’re familiar with easy methods to copy a Pivot Table accurately, including Pivot Table options, or just the values and source formatting.

Other articles you may also like:

• Change Count to Sum in Excel Pivot Table
• How to Add Calculated Field to Pivot Table?
• How to Sort a Pivot Table in Excel?
• How to Connect Slicer to Multiple Pivot Tables
• How to Refresh Pivot Table in Excel? (Shortcut + VBA)