The Excel GETPIVOTDATA Function

Pivot Tables in Excel

Excel Pivot Tables are used to group tables of data stored in Excel spreadsheets.

They are an excellent tool for performing quick and easy analysis of large amounts of data.

If you want to learn more about pivot tables in Excel, see our Excel Pivot Table Tutorial or the more concise page on creating Excel Pivot Tables.

Function Description

The Excel Getpivotdata function extracts data from specified fields of an Excel Pivot Table.

The syntax of the function is:

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

where the function arguments are:

data_field

The pivot table data field (i.e. the data header in your original data set) that you want to retrieve.

pivot_table

A reference to any cells or range of cells within the Pivot Table to be searched.

(This reference is simply used to specify the pivot table).

[field1], [item1],
[field2], [item2],
etc

Up to 126 optional pairs of fields and item names, (i.e. The row and column headers and individual categories) for which you want the returned value.

Note: The item values should be entered as follows:

Numbers can be entered directly;
Dates should be entered as date serial numbers or by using the date function;
Times should be entered as decimals or by using the time function;
Text values should be entered in quotations.

Note that, if the requested fields are not visible in the specified Pivot Table, the Getpivotdata function returns the #REF! error.

Automatically Insert the Getpivotdata Function

If the 'Use GetPivotData functions for PivotTable references' Excel option is enabled, the easiest way to input the Getpivotdata function is simply to type "=" into a cell and then click on the Pivot Table value that you want to return. Excel then automatically inserts the Getpivotdata function into the active cell.

(Note: the 'Use GetPivotData functions for PivotTable references' option is located in the 'Formulas' section of the 'Excel Options' dialog box, which is accessed from the 'File' tab of Excel).

Getpivotdata Function Examples

The examples below all refer to the following Pivot Table, which is located in columns A-G of the current Excel Worksheet.

	A	B	C	D	E	F	G
1
2	Sum of Invoice Amount Total		Item Type
3	Date	Salesperson	Digital TV	DVD Player	Ipod	Computer	Grand Total
4	Jan	John	$68,600	$13,800	$6,840	$69,600	$158,840
5		Kevin	$64,400	$7,800	$12,780	$36,800	$121,780
6		Pete	$46,200	$2,400	$2,700	$25,600	$76,900
7	Jan Total		$179,200	$24,000	$22,320	$132,000	$357,520
8	Feb	John	$68,600	$8,400	$9,720	$52,800	$139,520
9		Kevin	$61,600	$4,500	$7,920	$43,200	$117,220
10		Pete	$29,400	$3,900	$6,300	$33,600	$73,200
11	Feb Total		$159,600	$16,800	$23,940	$129,600	$329,940
12	Mar	John	$71,400	$9,300	$7,560	$80,000	$168,260
13		Kevin	$70,000	$10,200	$13,680	$58,400	$152,280
14		Pete	$43,400	$5,400	$3,240	$40,800	$92,840
15	Mar Total		$184,800	$24,900	$24,480	$179,200	$413,380
16	Grand Total		$523,600	$65,700	$70,740	$440,800	$1,100,840
17

Example 1

=GETPIVOTDATA( "Invoice Amount", $A$2, "Date", "Jan" )

- Returns the value $357,520, which is the value of the Invoice Amount Total for all of the Date field, "Jan".

Example 2

=GETPIVOTDATA( "Invoice Amount", $A$2, "Date", "Feb", "Item Details", "IPod" )

- Returns the value $23,940, which is the value of the Invoice Amount Total for the Date field, "Feb" and the Item Details field, "IPod".

Example 3

=GETPIVOTDATA( "Invoice Amount", $A$2, "Date", "Feb", "Item Details", "IPod", "Salesperson", "Kevin" )

- Returns the value $7,920, which is the value of the Invoice Amount Total for the Date field, "Feb", the Item Details field, "IPod", and the Salesperson field "Kevin".

Example 4

=GETPIVOTDATA( "Invoice Amount", $A$2, "Salesperson", "Kevin" )

- Returns the Excel #REF! error as the Pivot Table doesn't show the totals for the Salesperson "Kevin".

Note that although the above examples all use the reference $A$2 as the pivot_table argument, this argument is simply used to specify the pivot table and so could actually be any cell or cell range within the pivot table.

For further information on the Getpivotdata Function, see the Microsoft Office website.

Getpivotdata Function Error

If you get an error from the Excel Getpivotdata function, this is likely to be the #REF! error:

Common Error

#REF!

Occurs if either:

The supplied pivot_table reference does not relate to a pivot table;
Any of the fields specified by the data_field, [field] or [item] arguments are not valid fields within the specified pivot table;
The field details are not displayed in the specified pivot table (e.g. total sales figures for SalesPerson 'Kevin' are not displayed in the example spreadsheet above).

Return to the Excel Lookup & Reference Functions Page
Return to the Excel Built-In Functions page