PivotTable formatting in Google Sheets - 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	North
B3	Oranges
C3	150
A4	South
B4	Apples
C4	200
A5	South
B5	Oranges
C5	120

Formula Trace

QUERY(A1:C5, "select A, sum(C) group by A pivot B", 1)

Step 1: QUERY(A1:C5, "select A, sum(C) group by A pivot B", 1)

Cell Reference Map

  A       B        C
1 Region  Product  Sales
2 North   Apples   100
3 North   Oranges  150
4 South   Apples   200
5 South   Oranges  120

Formula references A1:C5 for data input.

The formula uses the data range A1:C5 containing Region, Product, and Sales.

Result

  E        F        G
1 Region  Apples  Oranges
2 North   100     150
3 South   200     120

The pivot table shows Regions in column E, with Apples and Oranges sales as columns F and G.

Sheet Trace Quiz - 3 Questions

What does the 'pivot B' part of the formula do?

A It creates columns for each unique Product

B It sums all sales without grouping

C It filters only Apples sales

D It sorts data by Region

Key Result

QUERY(data_range, "select column1, sum(column3) group by column1 pivot column2", header_rows)