A Pivot Table is very flexible, but it has several limitations. Say you can’t add new rows or columns to a pivot table, though you can add rows or columns in the data source. You can’t change any of the calculated values, or you can’t enter formulas within the pivot table. If you want to manipulate a pivot table, you can make a copy of it so it’s no longer linked to its data source. I’ll show you how to copy a Pivot Table in the following section of this article.
How to Copy a Pivot Table in Excel: 2 Simple Ways
In the dataset, you will see the Sales and Profit information of a shop on some random days from the month June to August. We will make a Pivot Table using this data and show you how to copy it.
Before copying, we need to create a Pivot Table using the data. To make a Pivot Table,
- Select the range of the data (B4:D15) and then go to Insert >> Pivot Table.
- After that, the Pivot Table window will show up. Select the option where you want your Pivot Table to be created and click OK. In this case, I selected a New Worksheet so that the Pivot Table will appear in a new worksheet.
- Next, drag the PivotTable Fields to the PivotTable Area.
1. Using Copy-Paste Feature to Copy a Pivot Table (Same or Another Sheet)
In the latest version of Microsoft Excel, we can create a complete duplicate of a Pivot Table simply using the Copy-Paste feature. Let’s have a look at the description below.
Following the above-mentioned method, you will see an overview of Sales and Profit data in the Pivot Table.
Let’s copy the table.
- First, select the PivotTable data and press CTRL+C to copy it.
- Later, paste the Pivot Table into another sheet. This step is very important because if you paste the Pivot Table into the same sheet, the original Pivot Table won’t work as Excel does not allow one Pivot Table to overlap another Pivot Table. Both Pivot Tables work perfectly if you paste in a different sheet.
- You can see that there are various Paste Options, you should choose any of them according to your purpose.
- Let’s just paste it normally (press CTRL+V).
- We can observe if both these Pivot Tables work correctly. Click on the Plus (+) icon beside the month of June in the original Pivot Table. You will see the detailed Sales and Profit
- Do the same operation on the copied Pivot Table. You will see the same data on the Pivot Table.
Thus we can easily copy a Pivot Table using the Copy & Paste feature of Excel.
2. Applying Clipboard to Copy a Pivot Table
If you use the older version of Excel, you may not be able to copy a Pivot Table by using the Copy & Paste feature. We need to use Clipboard for this purpose. Let’s go through the description below for a better understanding.
- First, select the Pivot Table and press CTRL+C.
- After that, click on the marked icon in the Clipboard You will find this ribbon in the Home Tab.
- Thereafter, select a cell where you want to paste the Pivot Table.
- Next, click on the marked item in the Clipboard
- Finally, you will see the Pivot Table data pasted in the Excel worksheet.
Thus you can copy a Pivot Table by using the Clipboard.
Download Practice Workbook
In the end, we can conclude that you will learn basic ideas on how to copy a Pivot Table after reading this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.
How to Copy a Pivot Table in Excel: Knowledge Hub
- How to Copy and Paste Pivot Table Values with Formatting in Excel
- Copy Pivot Table Data to Another Worksheet Without Pivot in Excel