Spreadsheet Practice

Google Sheets lesson 6 of 14, Data tools

Pivot tables

Total, count and rank a whole table in a few clicks, with no formulas to write.

FreeSheets mode12 minutes, 4 exercisesRowsColumnsValuesFiltersCOUNTUNIQUE

Watch it done

4 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

A pivot table summarizes a table without a single formula. Select the data with its header row, choose Insert > Pivot table, pick a new sheet or an existing one, and click Create. The pivot table editor opens on the right with four areas: Rows, Columns, Values and Filters. Each has an Add button that lists your column headers.

A field in Rows gives one row per distinct value, such as one row per store. A field in Values supplies the numbers. Number fields start as SUM and text fields as COUNTA. Change Summarize by to AVERAGE, MAX, MIN or COUNTUNIQUE, which counts distinct values, for example how many different reps sold in each store. A field in Columns spreads its values across the top and turns the list into a grid.

Each row field has an Order, ascending or descending, and a Sort by setting. Sort by the field itself for alphabetical order, or by a value such as SUM of Revenue to rank the rows. Show totals adds the Grand Total. In Values, Show as "% of grand total" turns each number into its share of the whole.

Filters leave rows out before anything is added up: add a field, open its status list and tick only the values you want. The pivot table recalculates when the source data changes, so it suits a report you refresh every week.

Syntax

Rows: StoreOne row per store
Values: Revenue, SUMTotal revenue for each row
Values: Rep, COUNTUNIQUEHow many different reps
Columns: StoreOne column per store
Sort by: SUM of Revenue, DescendingRank the rows, largest first
Show as: % of grand totalEach value as a share of the whole

Watch out for

  • Select the header row along with the data. The headers become the field names in the editor.
  • Blank or repeated headers make fields hard to find. Give every column a short, unique header first.
  • Sorting by the field gives alphabetical order. To rank rows, change Sort by to the value.
  • A filter changes the totals too: % of grand total becomes a share of the rows that are left.

The exercises

  1. Revenue and reps by store. Build a pivot table from A1:F17 with one row per Store. In Values, add Revenue summarized by SUM, then Rep summarized by COUNTUNIQUE, to show how many different reps sold in each store. Keep the default ascending order and the totals.
  2. Rank the reps. Build a pivot table from A1:F17 that ranks the reps by revenue: one row per Rep, Revenue summarized by SUM, and the rows sorted by SUM of Revenue, descending, so the top seller is first. Keep the totals.
  3. Units by category and store. Build a grid from A1:F17: one row per Category, one column per Store, and Units summarized by SUM in the middle. Keep the default order and totals.
  4. Online share by category. Build a pivot table from A1:F17 that shows each Category's share of online revenue: one row per Category, Revenue summarized by SUM and shown as % of grand total, and a filter on Channel that keeps only Online.

The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.

Revenue and reps by store

Build a pivot table from A1:F17 with one row per Store. In Values, add Revenue summarized by SUM, then Rep summarized by COUNTUNIQUE, to show how many different reps sold in each store. Keep the default ascending order and the totals.
Source data: A1:F17

Pivot table editor

Rows
Columns
Values
Filters

Pivot table

Add fields to Rows and Values to build the table.