Creating a PivotTable in Excel - Formula Evaluation Walkthrough

Sample Data

Sales data showing Region, Product, and Sales amount.

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

Formula Trace

PivotTable created from A1:C7 with Region as rows, Product as columns, and sum of Sales as values

Step 1: Select data range A1:C7

Step 2: Insert PivotTable using selected data

Step 3: Set 'Region' as Row Labels

Step 4: Set 'Product' as Column Labels

Step 5: Set 'Sales' as Values with SUM aggregation

Step 6: PivotTable displays sums: e.g. North-Apples = 120, South-Oranges = 130

Cell Reference Map

    A       B       C
1 |Region |Product |Sales
2 |  ↑    |   ↑    |  ↑ 
3 |  ↑    |   ↑    |  ↑ 
4 |  ↑    |   ↑    |  ↑ 
5 |  ↑    |   ↑    |  ↑ 
6 |  ↑    |   ↑    |  ↑ 
7 |  ↑    |   ↑    |  ↑ 

The PivotTable uses the data from cells A1:C7. Arrows show the data columns Region (A), Product (B), and Sales (C) feeding into the PivotTable.

Result

      Apples  Oranges  Grand Total
East     90       110          200
North   120       100          220
South   150       130          280
Grand Total 360     340          700

The PivotTable summarizes sales by Region (rows) and Product (columns). Each cell shows the sum of sales for that Region and Product. Grand Totals show sums across rows and columns.

Sheet Trace Quiz - 3 Questions

What does the PivotTable use as row labels?

A Product

B Sales

C Region

D Grand Total

Key Result

PivotTable summarizes data by grouping rows and columns and aggregating values with SUM.