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
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 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 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect