Go Back

Learn How to Copy a Pivot Table in Excel.

We can copy and paste a PivotTable in excel with a shortcut like CRTL+ALT+V. With this shortcut, we can prevent our source data from divulging sensitive information. The steps below will walk through the process.

Figure 1- How to Copy a Pivot Table in Excel

Setting up the Data

  • We will use the Pivot Table below for the illustration. The objective is to copy and paste the Pivot Table with the data and the formatting or as we please without the source data that has a hidden data cache

Figure 2- Setting up the Data

Copying and Pasting Data without CRTL+ALT+V

 

  • We will copy the Pivot table data by clicking on the table, press CTRL+A, and then, CRTL+C

 

Figure 3: Copying the Pivot Table Data

  • We will press CTRL+N to open a new workbook
  • We will click on any cell on the new workbook and press CTRL+V to paste the data

Figure 4: Pivot Table with Source Data

  • If this table has any sensitive information, it has been divulged. This is because the Pivot Table has been copied along with the source data. For instance, if we double-click on UTAH, details about it will display as in figure 5

Figure 5: Pivot Table with Source Data

Copying and Pasting Values including Pivot Table Formatting without Source Data

  • We will press CTRL+N to open a new workbook
  • We will click on any cell of the Pivot Table, press CTRL+A to highlight the cells, press CTRL+C to copy the data
  • We will open the new workbook and press CTRL+ALT+V

Figure 6: Paste Special dialog box

  • First, we will check the Values box. This will strip away all formatting
  • We will click on OK

 

 

Figure 7: Paste only Values

  • With the tables still on the clipboard, we will press CTRL+ALT+V again

Figure 8: Paste Special Dialog box

  • We will check the format box to regain the original format of the Pivot Table and click OK

Figure 9: Pasted Pivot table without Source Data

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar