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.
Leave a Comment