Copy a Pivot Table in Excel

Most of the people who use Excel encounter issues that can be resolved by the Pivot Table and they are very quick in learning about this very useful part of Excel.

If you wish to copy your Pivot Table, there are several ways to do so. They are described later in this article. But first, we have to create it.

Creating the Pivot Table

To Insert a Pivot Table, we need to go to the Insert tab. There we will find the section Tables from which we can choose our Pivot Table.

Pivot Table

Now, the first step in using the Pivot Table is that we have the data to make the Pivot Table. In our example, we are going to create a table with the list of NBA players and their statistics for several games, along with their teams and salaries. All of the data presented in the table below is completely random.

Now, we will create our Pivot Table by selecting all our data (our range) and then selecting Insert-Pivot Table. A pop-up will appear, one similar to the picture below:

There are several options to choose from. We can choose the range (in this case we have already done that) and we can choose if we want our Pivot Table in the new sheet or existing worksheet.

In this example, we have created a sheet named Pivot Table and copied the data into that sheet.

To finally create our table, we will filter out some data.

Standard Pivot Table has four areas:

  • Columns: The field used to measure and compare data.
  • Rows: The field for data you want to analyze.
  • Values: The field containing the values a table uses for comparisons.
  • Filter (optional): A field used to sort table data.

Sometimes, when you create your Pivot Table, it can happen that you cannot see these fields. If that happens, you have to right-click on the table and make sure that you turn on the Show Field List at the bottom.

In our case, we want to see the average points and sum of assists (in values) for all the players on the Brooklyn Nets team. We want this data to be shown by the player and we want our players to be ordered in our rows.

Our table looks like this:

If you would like to add some more that (for example, you get the statistics for one more game for each player), i.e. to update your Pivot Table, you would first have to insert the data in the original table and then go back to your table, click on it and then go to Pivot Table Tools in the ribbon:

Then you select Analyze, click on Change Data Source and change the range of your table. Finally, you click Refresh to apply the changes. You can also use Refresh if you have changed your data in the original table. In this case, you do not have to change your data source.

Copy a Pivot Table

The first, and easiest one to copy a Pivot Table is that you select all of your data and just paste your table. This can be done with the well-known shortcuts: CTRL + C for copying, and CTRL + P for pasting.

This way, you will get completely the same table as your original one:

This means that you will get not just the same numbers, but the same options as in the original table and the same range of data as well.

We will copy the data and the Pivot Cache as well. A Pivot Cache is something that automatically gets generated when you create a Pivot Table. It is an object that holds a replica of the data source. When we create another table, we create another Cache, and it has a large impact on the size of our file.

You will create another table, with the same source data if you use the following options:

  • Paste with Formulas
  • Paste with Formulas and Number Formatting
  • Paste with Keep Source Formatting
  • Paste with No Borders option
  • Paste with Keep Source Column Widths

All of your options can be seen when you copy the range and then right-click on the cell that you want to copy your data into. You then have to click on Paste Special icon in Paste Options (picture below):

All of the options that we mentioned above are found in the first section. In this section, there is also one more option- Transpose. The Transpose function returns a vertical range of cells as a horizontal range or vice versa.

The results of our table with Transpose options are:

It is important to have in mind that if we choose the Transpose option, we will not create another Pivot Table, i.e. we will simply copy our data.

In the second section of pasting options we have three options for pasting our table as values:

  • Paste as Values
  • Paste but keep Values and Number Formatting
  • Paste but keep Values and Source Formatting

Just as for the Transpose option, with this option, we are only copying the numbers, not our source table and our Pivot Cache.

If we only choose to paste values, our data looks like this:

If we click on Paste Special button (picture below)

We will get a pop-up window just like on the picture below:

We can basically paste our table in numerous ways and combinations.

is noticeable that we cannot simply copy and paste our table and have our formatting and table style copied without creating another Pivot Cache. At least not this way.

Luckily, there is a solution to this problem. For this, we use the Clipboard.

The Clipboard allows you to copy up to 24 items and paste them into another Office document.

In Excel, the Clipboard icon is located at the bottom of the Home tab, as in the picture below.

To use it, we just need to select our range, copy it, and then click on the little arrow located on the bottom right side of our picture above.

A window will appear on the left side of the screen:

We can see that we have our Pivot Table as the first item on a Clipboard. To paste it, we simply need to go to the desired cell where we want to paste the data, and then click on this item.

We will have our data copied with all the formatting and styles, but without copying our Pivot Cache and taking up our valuable space.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.