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", "FreshGo") → 434
It keeps returning Pune–FreshGo 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 tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of getpivotdata on a blank sheet for BrightLedger Finance.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Aarti Kulkarni and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Anna Kowalski.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply getpivotdata to a 5-row made-up BrightLedger Finance table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with getpivotdata.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as BrightLedger_practice.xlsx.
Hint: Use a clear file name for class.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
