Excel functions / Lookup
GETPIVOTDATA function
Reads one value out of a PivotTable by naming it, so that the formula still finds it when the PivotTable grows or is sorted.
Syntax
=GETPIVOTDATA(data_field, pivot_table, [field1, [field2], …])
Arguments
data_field: The value to read, in quotes: the name of the field ("Sales") or as the PivotTable shows it ("Sum of Sales").pivot_table: Any cell of the PivotTable, usually its top-left cell.field(optional): A row or column field to narrow by, in quotes, followed by the item to find in it. Without any, the result is the grand total.
When to use it
GETPIVOTDATA reads one value out of a PivotTable by naming it: the field, and the items to find. Excel writes it when you click a PivotTable cell while typing a formula. Unlike a cell reference, it still finds the value when the PivotTable is re-sorted, filtered or grows.
Examples
North's sales from a PivotTable
A B C D E F G 1 Region Sales Row Labels Sum of Sales 2 North 1200 North 2400 2400 3 South 800 South 800 4 North 900 West 400 5 West 400 Grand Total 3600 6 North 300 In G2:
=GETPIVOTDATA("Sales", D1, "Region", "North")→ 2400The PivotTable at D1 totals sales by region. Sort it or add a region, and the formula still finds North.
The grand total, with no item named
A B C D E F G 1 Region Sales Row Labels Sum of Sales 2 North 1200 North 2400 3600 3 South 800 South 800 4 North 900 West 400 5 West 400 Grand Total 3600 6 North 300 In G2:
=GETPIVOTDATA("Sum of Sales", D1)→ 3600
Common mistakes
- A layout that hides the item. If the PivotTable no longer shows the item asked for, the result is #REF!.
- Turning it off. In Excel, PivotTable Analyze, Options, Generate GetPivotData switches the automatic version off if plain references are wanted.
GETPIVOTDATA in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: ZÍSKATKONTDATA
- Danish: GETPIVOTDATA
- Dutch: DRAAITABEL.OPHALEN
- Finnish: NOUDA.PIVOT.TIEDOT
- French: LIREDONNEESTABCROISDYNAMIQUE
- German: PIVOTDATENZUORDNEN
- Hungarian: KIMUTATÁSADATOT.VESZ
- Italian: INFO.DATI.TAB.PIVOT
- Norwegian: HENTPIVOTDATA
- Polish: WEŹDANETABELI
- Portuguese (Brazil): INFODADOSTABELADINÂMICA
- Portuguese (Portugal): OBTERDADOSDIN
- Russian: ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
- Spanish: IMPORTARDATOSDINAMICOS
- Swedish: HÄMTA.PIVOTDATA
- Turkish: ÖZETVERİAL
Try GETPIVOTDATA in the playground
Related functions
- PIVOTBY: Summarises values by row fields down the side and a column field across the top, with totals: a PivotTable by formula that updates live. Spills.
- GROUPBY: Groups rows by one or more fields and aggregates values for each group, with a total row: a pivot by formula that updates live. Spills.
- SUMIFS: Adds the cells that meet every one of several conditions.