← ACARA Digital Technologies Years 9–10

Building a pivot table from fields

The four areas of the PivotTable field list. Drag a field into one to lay out the summary: Rows and Columns label it, Values calculates, Filters narrows the data.

Part of: ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 52

Understanding Building a pivot table from fields in ACARA Digital Technologies Years 9–10

What it does: The Values area does the calculating, so a number field such as Price goes there to be summed. A field in the Filters area adds a drop-down above the table that limits which data is summarised. Fields in the Rows area become the row labels down the left side. The Values area calculates numbers instead. A pivot table is made from the Insert tab. There is no =PIVOT formula that creates one. The Columns area makes headings across the top. It does not calculate anything on its own. Google Sheets has a pivot table editor side panel with the same four sections as Excel's field list.

Common mistakes: Rows holds a label field such as House. Sum of Points is a calculation, so it belongs in Values. The line should be Rows: House. The Filters area only narrows the data. A calculation like Average of Mark belongs in the Values area, so the line should be Values: Average of Mark.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Building a pivot table from fields to a class →

This module's key terms are also in a printable crossword (Modules 378–412) and word search (Modules 333–412).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
You want a pivot table that lists each house down the left side. Which area do you drag the House field into?
Values areaRows area
Matches
Columns area: Sum of Points (to add up the points)
↓
The Columns area only labels the headings across the top. Calculations belong in the Values area, so Points should be dragged to Values.
Applying
Meanings
Term
Rows, Columns, Values, Filters
Meaning
The four areas of the PivotTable field list. Drag a field into one to lay out the summary: Rows and Columns label it, Values calculates, Filters narrows the data.
Practice
Filters: Region
Testing
Gaps
Insert >
Check Answer
Spots
1Insert > PivotTable
2Rows: Sum of Points

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'African Wild Dog' badge — Savannah Streak, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. You want a pivot table that lists each house down the left side. Which area do you drag the House field into?
A. Values area
B. Rows area ✓
Fields in the Rows area become the row labels down the left side. The Values area calculates numbers instead.
2. Which area holds the field that gets calculated, such as the Sum of Points?
A. Filters area
B. Values area ✓
The Values area does the maths (sum, count, average). The Filters area only narrows which data is included.
3. Which step starts building a pivot table in Excel?
A. Click inside the data, then choose Insert > PivotTable ✓
B. Type =PIVOT( into an empty cell
A pivot table is made from the Insert tab. There is no =PIVOT formula that creates one.
4. You drag Year Level into the Columns area. What happens?
A. Each year level becomes a column heading across the top ✓
B. Each year level is added up in the Values area
The Columns area makes headings across the top. It does not calculate anything on its own.
5. You want to see the summary for only the Red house, without changing the rows or columns. Where should the House field go?
A. Filters area ✓
B. Columns area
A field in the Filters area adds a drop-down above the table so you can narrow the data. Columns would show every house as a heading.

Match the Pairs

Insert > PivotTable → Opens the dialog that creates a new pivot table from your selected data, so you can then drag fields into the field list
Rows area: Category → Lists each different Category down the left side, giving one row label for every category
To calculate a total for each item, drag a number field into the Values area → Values area: Sum of Price
To narrow the whole pivot table to one house without changing its layout, use the Filters area → Filters area: House
Columns area: Sum of Points (to add up the points) → The Columns area only labels the headings across the top. Calculations belong in the Values area, so Points should be dragged to Values.
Values area: House (to list the houses down the side) → Listing items down the side is the job of the Rows area. House should be dragged to Rows, not Values.

Key Term

Rows, Columns, Values, Filters
The four areas of the PivotTable field list. Drag a field into one to lay out the summary: Rows and Columns label it, Values calculates, Filters narrows the data.

Fill in the Blanks

1. Insert > PivotTable From Table/Range: A1:D9
Hint: Type the Insert menu command that creates a summary table from your data.
2. Rows: House Values: Sum of Points
Hint: Type the area that lists each group down the left side of the pivot table.
3. Rows: House Columns: Year Level Values: Sum of Points
Hint: Type the area that spreads a field's items across the top as headings.
4. Pivot table editor Rows: House Values: Average of Points
Hint: Type the area that does the calculation, such as sum, count or average.
5. Filters: Year Level Rows: House Values: Sum of Points
Hint: Type the area that narrows the data without adding rows or columns.

Spot the Error

Question 1
A Insert > PivotTable
B Rows: Sum of Points
C Values: Sum of Points
Line B — Rows holds a label field such as House. Sum of Points is a calculation, so it belongs in Values. The line should be Rows: House.
Question 2
A Insert > PivotTable
B Rows: Subject
C Columns: YearLevel
D Filters: Average of Mark
Line D — The Filters area only narrows the data. A calculation like Average of Mark belongs in the Values area, so the line should be Values: Average of Mark.
Question 3
A Insert > PivotTable
B Values: Category
C Values: Sum of Qty
Line B — Category is a label field. It should go in Rows so each category gets its own row. Values is for a calculation such as Sum of Qty.
Question 4
A Insert > Table
B Rows: Day
C Values: Max of Rain
Line A — A pivot table is made with Insert > PivotTable. Insert > Table only formats the data as a Table and does not open the PivotTable field list.
Question 5
A Insert > Pivot table
B Rows > Add: House
C Values > Add: House (SUM)
Line C — SUM needs a number field. House is text, so it cannot be added up. In the Google Sheets pivot table editor, add Points under Values.

Student with a class code? Join your class →