7. PivotTables and PivotCharts
7.10 GETPIVOTDATA
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
- In a cell outside the pivot type
=and click the Pune grand-total cell › Enter. - Replace
"Pune"with a cell that has a City drop-down. - To get normal references like
=B6instead: PivotTable Analyze › PivotTable › Options ▾ › untick Generate GetPivotData.
Ravindra Bagale's Tip
When they see GETPIVOTDATA, many students panic and switch it off, and then after sorting the pivot, =B6 shows the wrong city. For KPI cards and fixed reports, GETPIVOTDATA is the safe choice. Just remember – for an item that isn't visible in the pivot (because of a filter), you get #REF!; handle it with IFERROR.
Ravindra Bagale's Tip – मराठी
GETPIVOTDATA दिसलं की बरेच students घाबरून ते बंद करतात, आणि मग pivot sort केल्यावर =B6 चुकीची city दाखवतो. KPI cards आणि fixed reports साठी GETPIVOTDATA च सुरक्षित आहे. फक्त लक्षात ठेवा – जी item pivot मध्ये दिसत नाही (filter मुळे), तिच्यासाठी #REF! येतो; IFERROR ने handle करा.
Ravindra Bagale's Tip – हिंदी
GETPIVOTDATA दिखते ही बहुत से students घबराकर उसे बंद कर देते हैं, और फिर pivot sort करने पर =B6 गलत city दिखाता है. KPI cards और fixed reports के लिए GETPIVOTDATA ही सुरक्षित है. बस याद रखो – जो item pivot में नहीं दिख रहा (filter की वजह से), उसके लिए #REF! आता है; उसे IFERROR से handle करो.
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.