# Pivot tables (Google Sheets lesson 6 of 17)

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

Source: https://spreadsheetpractice.com/google-sheets/learn/pivot-tables · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Data tools"
- **Time:** about 12 minutes, 4 graded exercises
- **Functions:** `Rows`, `Columns`, `Values`, `Filters`, `COUNTUNIQUE`
- **Access:** free
- **Key formula:** `Rows: Store`: One row per store

## 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

| Formula | What it does |
|---|---|
| `Rows: Store` | One row per store |
| `Values: Revenue, SUM` | Total revenue for each row |
| `Values: Rep, COUNTUNIQUE` | How many different reps |
| `Columns: Store` | One column per store |
| `Sort by: SUM of Revenue, Descending` | Rank the rows, largest first |
| `Show as: % of grand total` | Each 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

Solve them in the live grid at [Pivot tables](https://spreadsheetpractice.com/google-sheets/learn/pivot-tables). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

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.

Previous: [Conditional formatting with custom formulas](https://spreadsheetpractice.com/google-sheets/learn/conditional-formatting) · Next: [Charts that fit the data](https://spreadsheetpractice.com/google-sheets/learn/charts)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
