Recently I was getting a model vetted from my girl friend! She saw a set of pivot tables and asked: How do you turn off the irritating GETPIVOTDATA function?
I traded off the quick tip with some awesome stuff ! 😆 And for you my friend.. it is on the house 😎
Lets say we have a Pivot Table set up !
--> Region wise Sales and Profit
What if I am suppose to calculate profitability % (i.e. Profit / Sales). Two simple ways to do it
- Use calculated fields in your Pivot Table
- Use a simple formula in the adjacent cell (i.e. Profit / Sales)
But when you do that, Pivot Table features the cells by a GETPIVOTDATA function. Which is irritating, since you cannot drag the formula down and replicate it to other cells
--> The GETPIVOTDATA cant be copied down
Turning off the GETPIVOTDATA function!
- Go to Excel Options, use the following shortcuts
- ALT F I for excel 2007
- ALT F T for excel 2010 and above
- In the formulas tab un-check the GETPIVOTDATA function
Now enjoy uninterrupted cell references
After you turn the GETPIVOTDATA thingy off, the cell references again flow as smooth as butter!
More on Pivot Tables