Quick Excel Tip

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 !

Turning off GETPIVOTDATA1

 

--> 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

Turning off GETPIVOTDATA2

 

--> 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

Turning off GETPIVOTDATA3

 

Now enjoy uninterrupted cell references

Turning off GETPIVOTDATA4

 

 

 

 

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]

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI