The GETPIVOTDATA function returns data stored in a PivotTable report. You can use GETPIVOTDATA to retrieve summary data from a PivotTable report, provided the summary data is visible in the report. You can quickly enter a simple GETPIVOTDATA formula by typing = in the cell you want to return the value to and then clicking the cell in the PivotTable report that contains the data you want to return.
The GETPIVOTDATA function syntax has the following arguments:
- Data_field Required. The name, enclosed in quotation marks, for the data field that contains the data that you want to retrieve
- Pivot_table Required. A reference to any cell, range of cells, or named range of cells in a PivotTable report. This information is used to determine which PivotTable report contains the data that you want to retrieve
- Field1, Item1, Field2, Item2 Optional. 1 to 126 pairs of field names and item names that describe the data that you want to retrieve
Applies To: MS Excel 2003, 2007 and 2010
- With reference to the PivotTable below, we are going to extract the total Dairy Products sales for the month of September
2. Select cell A2 and type the following formula:
=GETPIVOTDATA(“Sum of Product Sales”,E3,”Category Name”,”Dairy Products”,”Date”,”Sep”)
3. The answer will be 12054 as displayed below
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations
