GETPIVOTDATA

Question:  Is there a way to quickly extract certain data from a PivotTable in Microsoft Excel?

Answer: Yes, but using the GETPIVOTDATA function

Description:  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

Applies To MS Excel 2007, 2010

  1. The PivotTable report below includes detailed information on the sales by customer and product; to easily extract the Grand Total for Sales you can use the GETPIVOTDATA function

 

 

 

 

 

  1. Select in a blank cell and enter the following GETPIVOTDATA function
  2. =GETPIVOTDATA(“TotalSale”,$E$10)
  3. This will give you the Grand Total Figure for Total Sale $1,029,790.03

 

 

 

 

 

 

SYNTAX

GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)

The GETPIVOTDATA function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

  • 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. The pairs can be in any order. Field names and names for items other than dates and numbers are enclosed in quotation marks