Filtering PivotTable data in Excel - Cell-by-Cell Formula Trace

Sample Data

Sales data by Region and Product

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

Formula Trace

GETPIVOTDATA("Sales", $E$3, "Region", "North")

Step 1: GETPIVOTDATA("Sales", $E$3, "Region", "North")

Cell Reference Map

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

The formula references the PivotTable starting at cell E3, filtering by Region 'North' to get the sum of Sales.

Result

    E       F       G
1 PivotTable
2 Region  Product  Sum of Sales
3 North   Apples   100
4 North   Oranges  200
5 South   Apples   150
6 South   Oranges  120
7 East    Apples   90
8 East    Oranges  110

J1 Formula Result:
J2 300

The formula result 300 is the sum of Sales for 'North' region (100 + 200) shown in cell J2.

Sheet Trace Quiz - 3 Questions

What does the formula GETPIVOTDATA("Sales", $E$3, "Region", "North") return?

A Total Sales for Region 'North'

B Sales for Product 'North'

C Total Sales for all regions

D Sales for Region 'South'

Key Result

GETPIVOTDATA("DataField", PivotTableCell, "Field1", "Item1", ...) extracts filtered data from a PivotTable.