- You can quickly enter a simple GETPIVOTDATA formula by typing = (the equal sign) in the cell you want to return the value to and then clicking the cell in the PivotTable that contains the data you want to return.
- To use the GETPIVOTDATA function, the field you want to query must be a value field in the pivot table, subtotaled at the right level. In this case, we want a subtotal of the 'sales' field, so we provide the name the field in the first argument, and supply a reference to the pivot table in the second: = GETPIVOTDATA('Sales', $B$4).
Purpose of Excel GETPIVOTDATA Function. The advantage of using the GetPivotData function is that it uses criteria to ensure that the correct data is returned, even if the pivot table layout is changed. GETPIVOTDATA function returns visible data from a pivotTable.
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?
Excel 2016 Getpivotdata
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 !
![Excel getpivotdata get grand total Excel getpivotdata get grand total](/uploads/1/2/4/3/124394291/212463715.png)
--> Region wise Sales and Profit
What if I am suppose to calculate profitability % (i.e. Profit / Sales). Two simple ways to do it
- 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
Excel Getpivotdata Drag
After you turn the GETPIVOTDATA thingy off, the cell references again flow as smooth as butter!