Hi there I am trying to figure out how to change the absolute reference created automatically by GETPIVOTDATA to a cell reference. GETPIVOTDATA function - Description Returns data stored in a PivotTable. You can use GETPIVOTDATA to retrieve summary data from a PivotTable, provided the summary data is visible in the report. Note: You can quickly enter a simple GETPIVOTDATA formula.
You can build formulas that retrieve data from a pivot table. Following is a quick description of each of the GETPIVOTDATA function arguments in Excel:
- Data_field: The Data_field argument names the data field that you want to grab information from. For example, the Data_field name in the following figure is Sales $. This simply names the item that you drop into the Values area of the pivot table.The Function Arguments dialog box for the GETPIVOTDATA function.
- Pivot_table: The Pivot_table argument identifies the pivot table. All you need to do here is to provide a cell reference that’s part of the pivot table. In the GETPIVOTDATA function that used here, for example, the Pivot_table argument is $A$3. Because cell A3 is at the top-left corner of the pivot table, this is all the identification that the function needs in order to identify the correct pivot table.Pivot_table argument is $A$3
- Field1 and Item1: The Field1 and Item1 arguments work together to identify which product information that you want the GETPIVOTDATA function to retrieve. Cell C8 holds Kona Koast sales information. Therefore, the Field1 argument is Product, and the Item1 argument is Kona Koast. Together, these two arguments tell Excel to retrieve the Kona Koast product sales information from the pivot table.
- Field2 and Item2: The Field2 and Item2 arguments tell Excel to retrieve just Oregon state sales of the Kona Koast product from the pivot table. Field2 shows the argument State. Item2, which isn’t visible in the figure, shows as Oregon.
Excel Functions Tutorials
- Lookup Reference in Excel
Related Courses
GetPivotData in Excel (Table of Contents)
GetPivotData in Excel
![Getpivotdata Getpivotdata](/uploads/1/2/5/7/125725440/652397256.gif)
A pivot table is an easy way to summarize data. But somewhere, there can be many weak points where the pivot table cannot be organized data as per your requirements. By using GetPivotData you can get data from the pivot table as per the need in an easy way.
Basically, GETPIVOTDATA is used to fetch the required data from the pivot table. GetPivotData formula in Excel uses certain criteria so that correct data is returned from the pivot table.
GetPivotData Formula
Recommended Courses
Explanation of GetPivotData in Excel
There are three parameters are used in GetPivotData in Excel.
- Data_field: Name or value that you are looking for.
- Pivot_Table: Any cell reference or range of cells which is in the pivot table.
- [field1],[item1],[field2],[item2],etc: Up to 126 pairs of fields and item names can be found. This is optional part in getpivotdata.
Note: The values must be present in below details:
- The number can be directly entered.
- Use date in the serial number or use date by using date function.
How to Use the GetPivotData Function in Excel?
GETPIVOTDATA in Excel is very simple and easy to use. We can understand its working by the following examples.
You can download this GetPivotData Function Excel Template here – GetPivotData Function Excel Template
GetPivotData in ExcelExample #1
Suppose you have a region in the first column, Month in the second column, agent names in the third column, sale in the fourth column and target in the sixth column. Now you have to simply find total sale by using GETPIVOTDATA. Below is the data, you can copy it and paste it into your excel for practice.
Now simply create a pivot table.
Below is the Image as well for your understanding.
Below is the example that how to use GetPivotData function to find out total sale.
=GETPIVOTDATA(“Sale”,C4)
![Getpivotdata Function Excel Getpivotdata Function Excel](/uploads/1/2/5/7/125725440/572781120.jpg)
=GETPIVOTDATA(“Sum of Sale”,C4)
GetPivotData in ExcelExample #2
The next question is find out the total target by using GETPIVOTDATA function.
All in One Excel VBA Bundle (35 Courses with Projects)35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
4.9 (1,353 ratings)
Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course(Use same data which mentioned above)
=GETPIVOTDATA(“Target”,D4)
=GETPIVOTDATA(“Sum of Target”,D4)
GetPivotData in ExcelExample #3
In next question, By using same data let’s find out the sale of Mar month.
Now there are two ways to find the sale of Mar Month. First, you can type item of field names in the GETPIVOTDATA in Excel function and in the second one you can refer to worksheet cells.
=GETPIVOTDATA(“Sale”,C4,”Month”,”Mar”)
=GETPIVOTDATA(“Sale”,C4,A4,A9)
GetPivotData in ExcelExample #4
In next question, By using same data let’s find out the Target of Mar month.
=GETPIVOTDATA(“Target”,D4,”Month”,”Mar”)
=GETPIVOTDATA(“Target”,D4,A4,A9)
Here you need to know important things
If you change the first argument, data_field, to a reference F4, the result is a getpivotdata #REF! error
So, always remember use text in the data_field syntax for ignoring errors.
GetPivotData in ExcelExample #5
Now Let’s move to another task that how to use Dates in GetPivotData function.
If you use dates in GETPIVOTDATA formula, there might be a few errors. Below are the few examples.
In below-given table, we have a date in first column and sale in the second column.
Now just create a pivot table
By using this table, find out the sale of 26th may.
To get a correct answer while typing a date
=GETPIVOTDATA(“Sale”,B3,”Date”,”5/26/2018″)
Things to remember the GetPivotData Function in Excel
There is two kind of errors found in GetPivotData function in excel.
- GETPIVOTDATA will return a #REF error if any supplied fields name puts incorrectly.
- The GetPivotData function always accepts the name of the data_field, and field/item values in the double quotes.
Recommended Articles
This has been a guide to GetPivotData in Excel. Here we discuss the GetPivotData Formula in excel and how to use GetPivotData Function on Excel along with practical examples and downloadable excel templates.
All in One Excel VBA Bundle (35 Courses with Projects)- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion