GETPIVOTDATA is an amazing formula use to extracts summary data from a PivotTable report. The good thing about this function is how it makes use of criteria to ensure that the right data are extracted without being mindful of any change made to the PivotTable layout.

Below is the syntax that appears automatically whenever you turn on the Generate GetPivotData while retrieving values.

= GETPIVOTDATA(data_field, Pivot_table, [field1, item1, field2, item2],…)

The syntax arguments detail:

  • Data_field, this argument is required and it indicates the field that contains the data intends to retrieved, usually enclosed with quotation.
  • Pivot_table, indicates the PivotTable report holding the data been extracted. This argument is required.
  • Field1, item1,…, makes little description about the data being retrieved and can be between 1 and 126 pairs but optional.

GETPIVOTDATA is said to return #Ref! Error if Pivot_table argument not a range in which a PivotTable is found or the field counterpart is not describes value that can be found in a particular cell.

Create A Custom Report With GETPIVOTDATA

The easiest way to get this done is input equal sign (=) in a cell that will hold the return value and then click on the cell in PivotTable that contains the value you intend to extract.

Cell A22 =GETPIVOTDATA(“Unit Sold”,$A$3,”Product”,”Lucky Plus”,”Years”,2014)

The above formula retrieved value of “Unit Sold” where Product = “Lucky Plus” in year “2013” and then return the result back to cell A22.


The result appears as follows


Hope this post is helpful?

For more Insight on Microsoft Excel, kindly buy any of my books “Productivity Boosting Aspects of Microsoft Excel” and “PivotTable Recipe” on Kindle Amazon. You can as well subscribe to our newsletter here

Shares 0