I am in the process of updating my Pivot Table course and decided that I take another look at the GetPivotData function.
GET PIVOT DATA is an Excel function that pulls data from your pivot table. I personally don’t use it because if you hide a field you referenced in GETPIVOTDATA, the function stops working. Sometimes, just rearranging the fields in the table can impact the function as well. However, I did want you to be aware of it.
If you try to multiply a total in a pivot table, you will immediately see that Excel automatically references the GETPIVOTDATA function. This is a problem because if you copy the formula, you will get the same answer for the entire column or row.
In the example below, I opened Pivot_Rearrange.xlsx and removed the Product Name field from the Pivot Table. Next I clicked on cell C5, as I wanted to multiply B5 by 1.10 to estimate next year’s order amount by carrier.
Instead of showing =B5 it shows =GETPIVOTDATA(“Order Amount”,$A$3,”Ship Via”, “FedEx”)*1.10
Notice that A3 is treated as an absolute cell reference. If you copy the formula down, you will get the same answer for all of the Ship Via carriers. As a work around, I know many people who copy and paste the pivot table into another workbook if they want to do any math on the pivot table. However, I found a great tip on the MrExcel.com website on how to avoid this problem.
- Go up the Options icon and click the drop-down arrow to the right of it.
- Uncheck Generate GetPivotData.
Now, if you go back to cell C5 and recreate the formula, you will see the formula bar now shows =B5*1.10. It will now copy down correctly. (Excel will not correct the GETPIVOTDATA formula so you need to delete it and then type in =B5*1.1.)
The best part is that once you uncheck it, Excel remembers so you do not have to keep unchecking the Generate GetPivotData icon every time you want to use a formula that references the pivot table.
This is an excerpt from my new Pivot Table Course that will be out the first week of June 2016.