Lookup & Reference

GETPIVOTDATA Function in Excel

Returns data stored in a PivotTable report.

Syntax

  • =GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...)

Arguments

  • data_field (required): Name of the value field
  • pivot_table (required): Reference to any cell in the PivotTable
  • field1 (optional): Field name
  • item1 (optional): Item in the field

Examples

  • =GETPIVOTDATA("Sales", A3, "Region", "North") - Get North region sales - Result: Sales value

When to use GETPIVOTDATA

  • Use GETPIVOTDATA when you need to retrieve values from a table or range based on a key.
  • Confirm data types match (text vs number) and trim spaces before lookups.
  • Pair with [IFERROR](/functions/iferror/) or [IFNA](/functions/ifna/) for cleaner reports.
  • See the [lookup functions guide](/guides/excel-lookup-functions-guide/) for VLOOKUP, XLOOKUP, and INDEX MATCH paths.

Common GETPIVOTDATA mistakes

  • #N/A often means the key is missing or exact match is wrong — see [#N/A error](/errors/na/).
  • Lock table references with $ when copying formulas down a column.
  • Compare legacy vs modern lookups in [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/).

Common errors

  • #REF! if PivotTable not found

Use cases

  • PivotTable reporting
  • Dynamic dashboards
  • Data extraction

Frequently asked questions

  • What is GETPIVOTDATA used for? GETPIVOTDATA extracts specific values from PivotTables. Unlike cell references that break when PivotTable layout changes, GETPIVOTDATA uses field names and items, making it robust for dashboards and reports.
  • How do I turn off automatic GETPIVOTDATA? When you click a PivotTable cell, Excel auto-generates GETPIVOTDATA. To disable: File > Options > Formulas > uncheck 'Use GetPivotData functions for PivotTable references'. Or type the cell reference manually.
  • How do I make GETPIVOTDATA dynamic with cell references? Reference cells for field items: =GETPIVOTDATA("Sales", $A$3, "Region", B1) where B1 contains 'North'. Change B1 to get different regions. This creates dynamic dashboards from PivotTable data.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.