GETPIVOTDATA function in Excel
To add a GETPIVOTDATA formula easily, type an equal sign (=) and then click on a cell inside the PivotTable. The GETPIVOTDATA function can be quite useful.
1. First, click on cell B14 and type =D7 (do not click on cell D7 in the PivotTable) to refer to the amount of beans exported to France.
2. Apply the filter to show just the quantities of vegetables exported to each country.
Note: Cell B14 currently refers to the quantity of carrots exported to France, not beans. GETPIVOTDATA to the rescue!
3. Remove the filter. Click cell B14 once more, type =, and then select cell D7 from the PivotTable.
Note: Excel adds the GETPIVOTDATA function automatically as shown above.
4. Again, apply the filter to show just the quantities of vegetables exported to each country.
Note: The GETPIVOTDATA function correctly gives the total beans sent to France.
5. The GETPIVOTDATA function can return only the data that is visible in the PivotTable. For example, use the filter feature to show only the fruit export figures for each country.
Note: The GETPIVOTDATA function shows a #REF! error because the value 680 (beans to France) is not currently visible in the pivot table.
6. The dynamic GETPIVOTDATA function below shows the amount of mangoes exported to Canada.
Note: In Excel, the GETPIVOTDATA function takes six arguments – one data field, one cell reference from inside the PivotTable, and two field and item pairs. Create a drop-down list in cell B14 and cell B15 to quickly select the first and second item (see downloadable Excel file).
7. In the example below, the GETPIVOTDATA function takes four arguments: the data field, a reference to a cell inside the PivotTable, and one field/item pair. It returns the total export amount for the USA.
8. If you change the total export amount to the USA by applying a filter, the result from the GETPIVOTDATA function changes as well.
If you don’t want Excel to automatically insert a GETPIVOTDATA function, you can turn off this feature.
9. Click any cell inside the pivot table.
10. On the PivotTable Analyze tab, in the PivotTable group, click the drop-down arrow next to Options and uncheck Generate GetPivotData.
9/9 Completed! Learn much more about pivot tables ➝
Next Chapter: Tables