Excel GETPIVOTDATA function

Summary

The GETPIVOTDATA function in Excel is designed to retrieve specific data from a PivotTable report. It’s particularly useful for creating formulas that reference PivotTable data, allowing for dynamic and precise data extraction. This function is part of the Lookup & Reference functions category in Excel. It ensures that your references to PivotTable data remain accurate even if the PivotTable layout changes.
Syntax
				
					=GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], ...)
				
			
  • data_field: The name of the data field to query in the PivotTable.
  • pivot_table: A reference to any cell, range, or named range within the PivotTable.
  • [field1, item1], [field2, item2], …: [Optional] Pairs of field names and item names that define the data you want to retrieve.
Return value
Returns the data stored in the PivotTable that matches the specified criteria.

How to use

Use GETPIVOTDATA by specifying the data field you want to extract from a PivotTable and a reference to that PivotTable. You can also specify additional field/item pairs to refine the data you’re retrieving. This function is ideal for reports and analysis where you need to pull specific data points from complex PivotTables.

Examples

Simple GETPIVOTDATA
Retrieving a Specific Value:
				
					=GETPIVOTDATA("Sales", $A$1)

				
			
This formula retrieves the Sales value from the PivotTable referenced at $A$1.
GETPIVOTDATA with Criteria
Extracting Data with Specific Criteria:
				
					=GETPIVOTDATA("Sales", $A$1, "Region", "North", "Product", "Widgets")

				
			
This retrieves the Sales amount for Widgets in the North region from the PivotTable at $A$1.
Dynamic GETPIVOTDATA
Referencing PivotTable Fields Dynamically:
				
					=GETPIVOTDATA("Sales", $A$1, "Region", B1, "Product", C1)

				
			
Assuming B1 and C1 contain criteria (like region and product names), this formula dynamically retrieves sales data based on those criteria.
GETPIVOTDATA for Date Ranges
Extracting Data for Specific Date Range:
				
					=GETPIVOTDATA("Sales", $A$1, "Date", ">=01/01/2023", "Date", "<=31/12/2023")

				
			
This formula retrieves Sales data for the year 2023.

Additional Notes

  • GETPIVOTDATA is dynamic and will update automatically if the PivotTable changes.
  • It’s an excellent tool for creating interactive dashboards and reports.
  • If you prefer not to use GETPIVOTDATA, you can disable this feature in PivotTable options.

Related Functions

Excel MATCH function

The Excel MATCH function finds the position of a value within a range, essential for dynamic lookups and complex searches.

Excel INDEX function

The Excel INDEX function retrieves values at a specific row and column in a range, key for dynamic data lookups and retrieval.

Excel VLOOKUP function

The Excel VLOOKUP function searches a table's first column for a value, returning a specified column's value from the same row.

Content Navigation