Let us understand the working of GETPIVOTDATA Function in Excel by Some Examples. GETPIVOTDATA Function in Excel is very simple and easy to use. How to Use GETPIVOTDATA Function in Excel? : We are looking for a Grand Total of Sales Amt for the Region (i.e. ) East (i.e.Sales Amt, is residing in cell A3, which is why it is taking a reference as A3. In the example, shown above the data filed, i.e. Pivot Table: This is a reference to your Data Field cell.This is not an optional argument but a required argument.

In the above example, we are looking for the Sales Amount Total.

Data Field: in the above example, the data field means what is the heading total you are looking for.

In the above function, we are looking for the Grand Total Amount of the Region East. īefore explaining, the syntax one by one, let us looks into the simple example. This function consists of Data Field, Pivot Table,,. The Formula for the GETPIVOTDATA Function in Excel is as follows: Getpivotdata can query a pivot table and retrieve specific data based on the table structure instead of the references. On the PivotTable Analyze tab, in the PivotTable group, click the drop-down arrow next to Options and uncheck Generate GetPivotData.Excel functions, formula, charts, formatting creating excel dashboard & others Click any cell inside the pivot table.ġ0. If you don't want Excel to automatically insert a GETPIVOTDATA function, you can turn off this feature.ĩ. If the total amount exported to the USA changes (for example, by using a filter), the value returned by the GETPIVOTDATA function also changes. The GETPIVOTDATA function below has 4 arguments (data field, a reference to any cell inside the pivot table and 1 field/item pair) and returns the total amount exported to the USA.Ĩ. Create a drop-down list in cell B14 and cell B15 to quickly select the first and second item (see downloadable Excel file).ħ.

Note: this GETPIVOTDATA function has 6 arguments (data field, a reference to any cell inside the pivot table and 2 field/item pairs). The dynamic GETPIVOTDATA function below returns the amount of mango exported to Canada. Note: the GETPIVOTDATA function returns a #REF! error because the value 680 (beans to France) is not visible.Ħ. For example, use the filter to only show the amounts of fruit exported to each country. The GETPIVOTDATA function can only return data that is visible.

Excel getpivotdata function true