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

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

  1. North's sales from a PivotTable

    ABCDEFG
    1RegionSalesRow LabelsSum of Sales
    2North1200North24002400
    3South800South800
    4North900West400
    5West400Grand Total3600
    6North300

    In G2: =GETPIVOTDATA("Sales", D1, "Region", "North") → 2400

    The PivotTable at D1 totals sales by region. Sort it or add a region, and the formula still finds North.

    Try this example

  2. The grand total, with no item named

    ABCDEFG
    1RegionSalesRow LabelsSum of Sales
    2North1200North24003600
    3South800South800
    4North900West400
    5West400Grand Total3600
    6North300

    In G2: =GETPIVOTDATA("Sum of Sales", D1) → 3600

    Try this example

Common mistakes

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.

Translate a whole formula

Try GETPIVOTDATA in the playground

Related functions