PivotTable layouts in Excel - Cell-by-Cell Formula Trace

Sample Data

Sales data by Region and Product with sales amounts.

Cell	Value
A1	Region
B1	Product
C1	Sales
A2	North
B2	Apples
C2	120
A3	North
B3	Oranges
C3	150
A4	South
B4	Apples
C4	200
A5	South
B5	Oranges
C5	180
A6	East
B6	Apples
C6	90
A7	East
B7	Oranges
C7	110

Formula Trace

GETPIVOTDATA("Sales", $E$2, "Region", "North", "Product", "Apples")

Step 1: GETPIVOTDATA("Sales", $E$2, "Region", "North", "Product", "Apples")

Cell Reference Map

    A       B        C       D       E       F
1 |Region |Product |Sales |       |PivotTable Start|
2 |North  |Apples  |120   |       |Region  Product  Sales|
3 |North  |Oranges |150   |       |North   Apples   120  |
4 |South  |Apples  |200   |       |North   Oranges  150  |
5 |South  |Oranges |180   |       |South   Apples   200  |
6 |East   |Apples  |90    |       |South   Oranges  180  |
7 |East   |Oranges |110   |       |East    Apples   90   |
                                |East    Oranges  110   |

The formula references the PivotTable starting at cell E2, which summarizes sales by Region and Product.

Result

    E       F       G
1 |Region  Product  Sales|
2 |North   Apples   120 |
3 |North   Oranges  150 |
4 |South   Apples   200 |
5 |South   Oranges  180 |
6 |East    Apples   90  |
7 |East    Oranges  110 |

The PivotTable shows sales totals by Region and Product. The formula extracts the sales for North Apples, which is 120.

Sheet Trace Quiz - 3 Questions

What value does the formula GETPIVOTDATA("Sales", $E$2, "Region", "North", "Product", "Apples") return?

A 120

B 150

C 200

D 90

Key Result

GETPIVOTDATA extracts specific data from a PivotTable by specifying the data field and filter criteria.