Quick Tip : Turning off GETPIVOTDATA function

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

1. Use calculated fields in your Pivot Table
2. 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!

1. Go to Excel Options, use the following shortcuts
   • ALT F I for excel 2007
   • ALT F T for excel 2010 and above
2. 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

1. Grouping Dates in Pivot Tables
2. DATA Models in Pivot Tables [Excel 2013 Feature]