What is GETPIVOTDATA and how did it get in your worksheet? The Excel team is hoping that you would see GETPIVOTDATA, then go find out what it is and learn to love it and use it all the time. But that is never what happens. Instead, people are annoyed by it.
As an aside, I spent eight years hating GETPIVOTDATA, but now I understand it and occasionally even use it. See “Can I Save Formatting in a Template" for an example of when you would want to use it.
The big question is how to enter a formula without getting the GETPIVOTDATA. One quick and easy way is to type the formula without using the mouse or the arrow keys. Just type =C5/B5-1. This creates a formula that will copy.
The other method is to permanently turn off the feature to generate GETPIVOTDATA. To do this, chose File, Options, Formulas. There is a checkbox for Use GetPivotData Functions For PivotTable References. Turn this off.
Additional Details: the other common problem with formulas outside of pivot tables is that they don’t deal well with the changing size of pivot tables. In Q1, six reason codes are found, so the % of Total formula points to G$11.
Figure 912 This works when there are six products.
This doesn’t work anymore in Q2, when only three reason codes are found.
Figure 913 The Grand Total moves from row 11 to row 8. Your formula is still dividing by G11.
The solution is to use an Excel trick to return the last value from column G. See “Return the Last Matching Value" on page 225 for details on how this VLOOKUP works. Also, use a custom number format where the third zone is blank in order to hide any 0 values that appear below the table.