How To Copy A Pivot Table

The pivot table is an essential part of Excel. It is used by most people that use Excel as it is so widely useful. However, there is one problem that a lot of users stumble upon when they are using this tool: how to copy the pivot table. 

How To Copy A Pivot Table

There are lots of different ways to copy your pivot table and these are described in this article! 

How Do You Create A Pivot Table?

Before we can go on to figure out how to create our pivot table, we first need to consider how we are going to make it in the first place. 

In order to insert a pivot table onto your excel sheet, you should follow these steps: 

  • Go to the ‘Inset’ tab. Here, you will see a ‘Tables’ option in the drop-down. From here, there is an option to choose ‘Pivot Table’. 

In order to create a pivot table, you will need to make sure that you have inserted all the data in order to make the pivot table. 

Once you have all the data, you will need to select the data and then choose the Pivot Table option. When you do this, you will see a pop-up appear.

You will then be able to choose from several different options. Here, you will have the chance to select whether you want your pivot table in a new or existing sheet. 

What Does A Pivot Table Contain?

A standard Pivot Table has four different aspects to it. These are: 

  • Columns: This is where the data is input in order to measure and compare the data. 
  • Rows: This is where any data that you want to analyze is placed. 
  • Values: This is the field that contains the values that a table needs in order to make comparisons. 
  • Filter: This is an optional field that is used to sort out the data on a table. 

Occasionally, upon the creation of a pivot table, you might not be able to see all of these fields listed.

When this happens, you might need to right-click on the table to make sure that you have the ‘Show Field List’ selection on. This will allow you to view the list. 

How To Copy A Pivot Table 

It is very easy to copy a Pivot table. In order to do this, you will need to select all of the data that is involved in the table and copy and paste it.

You can do this using the copy short-cut: CTRL + C and the paste short-cut: CTRL + P. 

If you copy and paste the table in this way, then you will have an exact copy of the original table.

How To Copy A Pivot Table

You will also have copied over the options in exactly the same way that it was in the original table. This is because when you copy and paste the table in this way, it will also copy the Pivot Cache. 

If you copy a table and want to create a similar table, you can use the options below which will be found by going to the ‘paste options’ option.

Here, you can copy the range of information and then right-click on the cell. You will have the option to:

  • Paste with formulas
  • Paste with No Borders
  • Paste with Formulas and Number Formatting 

There are also other options available.

Transpose

You also can copy your table using the ‘transpose’ function. You should use the transpose option if you don’t want to create a new table.

This should be used if, instead, you want to copy your data. This option only copies the numbers, not the data itself. 

Pasting Table As Values 

This is another way to copy your table. For this option, you will be copying your table in the way that you originally did. The way that you paste the table will be different. 

You can either paste this as values, or paste the data but keep the values and number formatting. 

If you paste values, you will get the information in a certain way. If you then click on the ‘paste special’ button, you will be presented with a pop-up window. 

On this pop-up window, you can paste your table in a number of different ways. This can’t just be copied and pasted, instead, we will have to use the ‘clipboard’ in order to store the table, before it is pasted. 

It is easy to find the clipboard icon in Excel, it is located at the bottom of the ‘Home’ tab. In order to use it, you will need to select your range, copy the range and then click on the clipboard arrow. 

When you do this with a pivot table, you will then have to paste the data on the desired cell and click on this item. It should be the first one on the list.

Advantages Of A Pivot Table 

There are many great aspects to pivot tables on excel, and they are very simple and easy to create. The advantages include: 

They Allow You To Analyze The Data 

Pivot tables provide insights into analytics and they will be a great stepping stone towards understanding how data is presented and analyzed. They are very simple and easy to create. 

Create Instant Data 

Using a pivot table, you can create the data instantly. You don’t have to rely on your own formulas, instead, you can immediately create a table in a very short space of time. 

Easier To Segment 

The data that is gathered into a database or a spreadsheet can be very overwhelming, using a pivot table makes it much more manageable.

Final Thoughts 

So, it is very easy to copy and paste a pivot table on excel. There are many different ways in which you can do this, and this article has given you detailed explanations of each one. 

Understanding how to use a pivot table in excel is a very important and useful skill, so ensure that you have great knowledge of this to get the most from the Microsoft program!

Jackoneil
Latest posts by Jackoneil (see all)