Ravindra BagaleCourses & study guides

7. PivotTables and PivotCharts

7.10 GETPIVOTDATA

By Last updated:

When you type = and click a value inside a PivotTable, Excel writes a GETPIVOTDATA formula:

=GETPIVOTDATA("Amount", $A$3, "City", "Pune", "Platform", "Blinkit")    → 434

It keeps returning Pune–Blinkit even if the pivot is rearranged or sorted – great for fixed-format reports and KPI cards. Replace the text with cell references to make it dynamic: =GETPIVOTDATA("Amount",$A$3,"City",$H$1).

Steps in Excel

  1. In a cell outside the pivot type = and click the Pune grand-total cell › Enter.
  2. Replace "Pune" with a cell that has a City drop-down.
  3. To get normal references like =B6 instead: PivotTable Analyze › PivotTable › Options ▾ › untick Generate GetPivotData.

Ravindra Bagale's Tip

GETPIVOTDATA disla ki khup students ghabrun te bandh kartat, aani mag pivot sort kelyavar =B6 chukicha city dakhavto. KPI cards aani fixed reports sathi GETPIVOTDATA ch surakshit aahe. Fakt lakshat theva – ji item pivot madhe disat nahi (filter mule), tyasathi #REF! yeto; IFERROR ne handle kara.

Practice task

Create three KPI cells with GETPIVOTDATA: total sales, Pune sales, and sales for the city selected in a drop-down. Sort the pivot and confirm the KPIs don't change.