What is the GETPIVOTDATA Function?
The GETPIVOTDATA Function is categorized under Lookup and Reference functions. The function helps to extract data from specified fields in an Excel Pivot Table. The pivot table is used often in financial analysis to facilitate deeper analysis of given data. The function helps extract, group or add data from a pivot table.
Formula
=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)
The GETPIVOTDATA function uses the following arguments:
- Data_field (required argument) – It is the worksheet information from which we intend to remove nonprintable characters.
- Pivot_table (required argument) – It is a reference to a cell, range of cell or named range of cells in a pivot table. We use the reference to specify the pivot table.
- Field1, Item1, Field2, Item2 (optional argument) – It is a field/item pair. There are up to 126 pairs of field names and item names that describe the data that we wish to retrieve.
We need to enclose in quotation marks all field names and names for items other than dates and numbers. We need to keep in mind that:
- Dates are entered as date serial numbers by using the DATE function so that they are in the proper date format.
- Numbers can be entered directly.
- Time should be entered in decimals or as the TIME function.
How to use GETPIVOTDATA Function in Excel?
To understand the uses of the GETPIVOTDATA function, let’s consider a few examples:
Example 1
Let’s assume we are given the pivot table below:
Suppose we wish to get the sum of laptops from the pivot table given above. In such scenario, we will use the formula =GETPIVOTDATA( “laptops”, $J$4) and get the result as 651,000.
Continuing with the same example, suppose we are given the pivot table below:
Now, we wish to get the total sales. The formula to use will be =GETPIVOTDATA( “sum of Total”, $J$4).
Example 2
Using dates in the GETPIVOTDATA function may sometimes give an error. Suppose we are given the following data:
We drew the following pivot table from it:
If we use the formula =GETPIVOTDATA(“Qty”,$L$6,”Date”,”1/2/17″), we will get a REF! error:
To prevent such date errors, we can use the following methods:
- Match the Pivot Table’s date format – In order get the correct error-free results, we will use same date format as in the pivot table. For example, we took 2-Jan as shown below:
As seen below, the same format gives us the desired result:
- Using the GETPIVOTDATA function with function DATEVALUE function – Here, instead of just typing the date in the formula, add the DATEVALUE function to the date. As shown below, the formula will be:
We will get the result below:
- Using the GETPIVOTDATA function with the DATE function (Example 1) – Here, instead of just typing the date in the formula, we will add the DATE function. As shown below, the formula will be:
We will get the result below:
- Using the GETPIVOTDATA function with the DATE Function (Example 2) – Here, instead of just typing the date in the formula, we can refer to a cell that contains a valid date, in any format recognized as a date by MS Excel. The formula to be used is =GETPIVOTDATA(“Qty”,$L$6,”Date”,S5).
We will get the result below:
Few notes about the GETPIVOTDATA Function
- #REF! error – Occurs when:
- The given pivot_table reference does not relate to a pivot table.
- When we provide invalid fields for arguments data_field, [field] or [item].
- The field details are not displayed in the specified pivot table.
- Remember that if an item contains a date, it should be in date format or serial number.
- The function can be automatically inserted by enabling ‘Use GetPivotData functions for PivotTable references’ option in MS Excel.
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources: