Paste as Pivot – New way to use Pivot table data

I created this Paste as Pivot utility (macro) along with Raj Chaudhuri. It helps you in copying cells from Pivot Table and pasting them as GetPivotData functions. You must read this article about GetPivotData in order to understand the background information.

Estimated reading time 8 min

The Need

Often we create a Pivot Table and use the data OUTSIDE the Pivot Table – to assist us with some manual calculations.

Usually we either copy paste the pivot as values or refer to specific cells in Pivot table.

Copy pasting as values is a bad idea because all the Pivot Table benefits are no longer available.

Referring to specific cells using a regular cell reference is dangerous because Pivot Table structure can change dynamically and you may end up using the wrong value – without even realizing it.

The irritating GetPivotData

If you try to put a formula which refers to any cell inside a Pivot Table data area, Excel automatically generates a GETPIVOTDATA function call.

Most of us find it extremely irritating and never end up using the feature.

In fact, it is a very important feature and we must use it to ensure accuracy of data reference – even if Pivot Table structure changes.

This topic was discussed in detail in the article:
Irritating feature = Extremely useful 🙂 GetPivotData

Please read this article before proceeding further.

Otherwise you will not be able to understand the context of why this tool was created.

GetPivotData ensures accuracy. But…

After reading the article, you must have realized that GetPivotData is a very useful feature. You will want to use it for all your Pivot Table referencing in future.

Now you will encounter an operational problem.
GetPivotData generates a safe reference to ONE cell in Pivot Table data area.

What if you want to refer to many cells from the Pivot Table data area?

You will need to manually refer to each cell one by one. This is tedious and practically impossible if you have large number of cell references to generate.

Solution: Paste as Pivot macro

To solve this problem, we created this simple but very useful macro.

All that you need to do is select the desired cells from data area and run the macro pastepivot.

The tool will then add a new sheet to your workbook and paste all the data area cells as GetPivotData references.

This saves you loads of time.

Download the Macro

This is a Zip file because XLSM files are not allowed to be uploaded.

Once you unzip the file, you will get the file
Paste as GetPivotData – with documentation.xlsm

How to use this macro

Disclaimer: We have done basic testing. We have not done extensive testing or stress testing on the code. Please use this macro at your own risk.

1. Open the XLSM file.
2. Open your file containing a Pivot Table.
   Take backup of your file, just to be on the safer side.
3. Go to your Pivot Table
4. Select desired cells from the data area of your Pivot Table
5. Go to View tab – Click Macros
6. Run the macro pastepivot
7. A new sheet will be added with GetPivotData references to the selected cells
8. A tooltip also appears in each cell explaining what it is
9. Pasting works only with cells from Data area. If you select Row or Column area cells, those will appear blank.
   
10. Each cell will be a GetPivotData function
11. When you click in each cell, the description of what the content means is shown as a tooltip. This tooltip was created using Data Validation feature. Although this may sound like an irritating feature, it is actually very useful in remembering the source data for each cell. Very important for auditing as well.
12. If you do not want tooltips, select all the cells and remove the data validation
    Click inside one of the cells. Open Data tab- Data Validation
    As each cell contains DIFFERENT data validation message, you will see an error message

    

    Click Ok. Now the Data Validation dialog will appear. Make sure the validation says Any Value and click Ok. The tooltips will now be removed.

13. You must make sure that the original Pivot Table never deleted.
14. You can change the layout and structure of the pivot table as long as the fields which contribute to the data are still included.
    If you remove any field, you will see #REF error in these cells.
15. You can now copy the output and paste it into your actual calculation.
16. The GetPivotData formula does NOT change even if you copy and paste it to another location in the same workbook.
17. If you copy these cells to another workbook, it will create a linked reference.

Send use your feedback and suggestions

Did you find it useful? Do you want any enhancements? Let us know.

***