GETPIVOTDATA is an inbuilt worksheet referencing function in excel which is used to get the data from a pivot table to any other cell or in any other worksheet, this function has two required arguments which are data field which field we want and pivot table as the reference the rest arguments are optional as which fields we wish to include in our data.
Excel GetPivotData Function (Table of Contents)
What is GETPIVOTDATA Function?
- A GETPIVOTDATA is a function that retrieves the data stored in a PivotTable.
- The function continues to retrieve the correct data if the report layout changes or if we refresh the data, provided the summary data is visible in the report.
- The function helps to extract, group or add data from a pivot table.
Syntax of GETPIVOTDATA Formula
There are at least 2 arguments, which are must, which will result in the grand summary (Grand Sum, Grand Count, Grand Average, Grand Max, Grand Min, etc. whichever we have selected for the PivotTable). Apart from these, we can specify 1 to 126 pairs of field names and item names that describe the data we want to retrieve. Field names and names for items other than dates and numbers are enclosed in quotation marks.
Let us understand each argument for GETPIVOTDATA formula individually.
- Data_Field: This is a required argument. We need to specify the field name in quotation marks that contain the data, which we want to retrieve.
- Pivot_table: This is a required argument out of two mandatory arguments of GETPIVOTDATA This argument indicates from which PivotTable, we want to retrieve the data. We need to give a reference to any single cell or multiple cells in a PivotTable. However, by convention, we give reference to the upper left cell. If we specify a range for pivot_table, which contains two or more PivotTables, data will be extracted from whichever report was created most recently in the range.
- Field1, Item1, Field2, Item2… Field126, Item126: These are optional arguments. These pairs help us to describe which data we want to retrieve.
There is one more way to enter the GETPIVOTDATA function.
We can enter this formula by typing = (the equal sign) in the cell where we want to return the value to and then click the cell in the Pivot Table that contains the data that we want to return. For this to work, we need to make sure that ‘Generate GetPivotData’ is active. This option can be found in the ‘Pivot Table’ group in the ‘Analyze’ tab.
How to Use the GETPIVOTDATA Function in Excel?
Let us understand the Getpivotdata function using some examples.
GETPIVOTDATA Function – Example #1
Suppose, we have the following data for sales of an organization.
Steps to use the GETPIVOTDATA function in excel are:
- We need to insert a Pivot table based on this data. To insert a pivot table, click on ‘Pivot Table’ in ‘Tables’ group under ‘Insert’ tab.
- Select the above data for ‘Table/Range’ option and ‘Existing Worksheet’ for ‘Choose where you want the PivotTable report to be placed’ and specify a single cell ‘F1’ for ‘Location’ and click on ‘OK’.
- Choose ‘Location’, ‘Product’, and ‘Type’ for ROWS label and ‘Sales’ for VALUES.
- PivotTable is ready now.
- Now to use GETPIVOTDATA function, type = (The Equal Sign) in J5 cell and write GETPIVOTDATA and start specifying the argument after opening bracket.
- Arguments Specification
- For data_field, we will specify “Sales” as we want the sum of sales.
- For pivot_table, we need to give reference to the F1 cell, as this is the upper left cell of the PivotTable.
- For field1 and item1, we will give reference to I2 and J2 cell respectively.
- For field2 and item2, we will give reference to I3 and J3 cell respectively.
- For field3 and item3, we will give reference to I4 and J4 cell respectively.
After pressing the Enter button, we get the result as follows:
Now we can change the value of Location, Product and Type and Sales will reflect automatically. We can also create a dropdown for specifying the values for Location, Product and Type. The steps would be:
- Select Cell J2, Click on ‘Data Validation‘ available under ‘Data Tools’ group in ‘Data’
- Choose ‘List’ for ‘Allow’ field and Specify “Delhi, Bombay, Bangalore” for ‘Source’ Click on OK.
We can choose from the drop down now to select the location.
We will use the same steps for creating drop downs for ‘Product’ and ‘Type’ field.
GETPIVOTDATA Function – Example #2
Suppose, we have the same data as above but this time, we have put ‘Location’ as a ‘FILTERS’ field for PivotTable.
- To enter the GETPIVOTDATA function, we will type = (the equal sign) and click on any cell in the PivotTable. (Make sure Generate GetPivotData is active).
As we can see, the formula is automatically entered with arguments. However, this is a static formula as every argument is specified literally. To make this function dynamic, we need to replace the values with cell references as below.
Answer will be –
However, as we change ‘Location’ from ‘Bombay’ to ‘Delhi’, we get #REF! error as value is not visible in the PivotTable.
However, after changing the Location in PivotTable also, we get the result for the function.
Make sure that we have not checked for ‘Select Multiple Items’ as a function will give #REF! error.
Things to Remember
- When we simply give reference to a normal worksheet cell, the notation is simple. The only cell address is written preceded by the equal sign. However, if we give the reference of a cell in a pivot table, then the GETPIVOTDATA function is automatically inserted in the cell. However, to do the same, we need to make sure that ‘Generate GetPivotData’ is active.
- The GETPIVOTDATA function can only return data that is visible.
- If a field data type is a date, the value for an item should be expressed as a serial number or by using the DATE function. Consequently, the value will be retained if the worksheet is opened in a different region. For example, date April 14, 2018, can be entered as 43204 or using the function as DATE(2018,4,14).
- Time values should be entered as decimal values or by using the TIME function.
- GETPIVOTDATA returns a value field based on current “summarize by” settings (sum, count, average, etc.). This field must be visible in the pivot table.
- To use the GETPIVOTDATA function, the field we want to query must be a value field in the pivot table, subtotalled at the right level.
You can download this GetPivotData Function Excel Template from here – GetPivotData Function Excel Template
This has been a guide to GETPIVOTDATA Function. Here we discuss how to use Getpivotdata formula in excel along with examples and downloadable excel sheet. You can learn more about excel function from the following articles –