Building a pivot table from fields

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

2. Which area holds the field that gets calculated, such as the Sum of Points?

A Filters area

B Values area

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

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

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

Definitions

Rows, Columns, Values, Filters

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Insert > __________ From Table/Range: A1:D9

____: House Values: Sum of Points

Rows: House _______: Year Level Values: Sum of Points

Pivot table editor Rows: House ______: Average of Points

_______: Year Level Rows: House Values: Sum of Points

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Insert > PivotTable

B Rows: Sum of Points

C Values: Sum of Points

Question 2

A Insert > PivotTable

B Rows: Subject

C Columns: YearLevel

D Filters: Average of Mark

Question 3

A Insert > PivotTable

B Values: Category

C Values: Sum of Qty

Question 4

A Insert > Table

B Rows: Day

C Values: Max of Rain

Question 5

A Insert > Pivot table

B Rows > Add: House

C Values > Add: House (SUM)

Answer Key

Building a pivot table from fields

Section A: Quick Questions

B — Rows area

B — Values area

A — Click inside the data, then choose Insert > PivotTable

A — Each year level becomes a column heading across the top

A — Filters area

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

Insert > PivotTable
From Table/Range: A1:D9

Rows: House
Values: Sum of Points

Rows: House
Columns: Year Level
Values: Sum of Points

Pivot table editor
Rows: House
Values: Average of Points

Filters: Year Level
Rows: House
Values: Sum of Points

Section E: Spot the Error

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.

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.

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.

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.

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.