Excel
All Top 50 Excel & Business Modeling Questions#39
Extracting KPI Figures from Pivot Tables with GETPIVOTDATA
MediumMorgan StanleyInterview Question #39
Asked at Morgan StanleyYou have a Pivot Table starting at cell `A3`. On an executive summary sheet, cell `B2` must display the Total Revenue for 'Enterprise' clients in the 'West' region. If you link directly via `=C14`, refreshing or expanding the Pivot Table shifts C14 to a different metric. Write a robust GETPIVOTDATA formula.
Input Table: PivotSummary (A3:C8)
4 rows preview| Region (Col A) | Segment (Col B) | Revenue (Col C) |
|---|---|---|
| East | SMB | 45000 |
| East | Enterprise | 120000 |
| West | SMB | 35000 |
| West | Enterprise | 95000 |
Expected Output Structure1 rows
| Target Metric | Safe Formula Output (B2) |
|---|---|
| West Enterprise Revenue | 95000 |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Region (Col A) | Segment (Col B) | Revenue (Col C) | |||||||
| 2 | East | SMB | 45000 | Target [Enter Formula] | ||||||
| 3 | East | Enterprise | 120000 | |||||||
| 4 | West | SMB | 35000 | |||||||
| 5 | West | Enterprise | 95000 | |||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
This question demonstrates a technique that can't be graded as a single cell formula (it needs another sheet, a real PivotTable, or compares two methods side-by-side). Read the explanation below instead.