A PivotTable needs one header row, no blank rows and one record per row. Press Ctrl+T first: a Table grows automatically, so new rows end up in the pivot after a refresh.
Source: tblSales
A1fx.
A
B
C
D
1
Region
Rep
Product
Sales
2
North
Asha
Laptop
1,200
3
South
Ben
Phone
800
4
South
Asha
Laptop
1,500
5
North
Chen
Tablet
600
6
East
Ben
Laptop
1,500
7
North
Asha
Phone
900
Six sales. In real life this would be six thousand, and the steps are identical.
Build it in four drags
Click anywhere in the data and choose Insert → PivotTable → New worksheet.
Drag Region into Rows.
Drag Product into Columns.
Drag Sales into Values. Excel picks “Sum of Sales” for number fields.
PivotTable1
E2fx=GETPIVOTDATA("Sales",$A$1,"Region","North")
A
B
C
D
E
1
Sum of Sales
Laptop
Phone
Tablet
Grand Total
2
North
1,200
900
600
2,700
3
South
1,500
800
2,300
4
East
1,500
1,500
5
Grand Total
4,200
1,700
600
6,500
Click a pivot value from a formula and Excel writes GETPIVOTDATA for you.
Three settings beginners miss
Setting
Where
Why
Number format
Value Field Settings → Number Format
Formatting cells directly is lost on refresh
Show values as %
Value Field Settings → Show Values As
See each region’s share of the total
Refresh on open
PivotTable Options → Data
Pivots don’t update by themselves
Try it in a live spreadsheet
Practise these formulas in interactive lessons with instant grading.
Sara Lind 3 min readData
Lena Okafor
Marco Ruiz