Free demo · Google Sheets · Lesson 9 · Advanced · Pivot tables

A pivot table: answers from a long list

A pivot table turns a long list into a small table of answers. No formulas: you only choose what goes in the rows and what to add up.

Name | Course | Fee | Paid Asha | Art | 1500 | Yes Kabir | Sport | 1200 | Yes Zoya | Computers | 1800 | No Arjun | Art | 1500 | Yes Diya | Music | 1400 | Yes Isha | Sport | 1200 | No Dev | Computers | 1800 | Yes
[b]Course | [b]SUM of Fee Art | 3000 Computers | 3600 Music | 1400 Sport | 2400 [b]Grand Total | [b]10400
  1. 1A clean list: headings in row 1, one record per row
  2. 2Click in the list › Insert › Pivot table
  3. 3Create: a new sheet with the Pivot table editor
  4. 4Rows › Add › Course: one row per course
  5. 5Values › Add › Fee: SUM of Fee, with a Grand Total
  6. 6Filters › Add › Paid › only Yes
  7. ✓Edit the list: the pivot table updates by itself
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. Enrol

Tip: press → or Space to step through, ← to go back, R to replay.

About this lesson

A pivot table turns a long list into a small table of answers without a single formula. Start with a clean list: one heading per column in row 1 and one record per row. Click inside the list and choose Insert → Pivot table, check the data range, keep New sheet and click Create. A new sheet opens with the Pivot table editor on the right. Add a field under Rows to get one row for each value, for example each course, and add a field under Values to add up a number, such as SUM of Fee; the Grand Total comes with it. A field under Filters limits the table, for example to the fees that have been paid. The original list is never changed, and in Google Sheets the pivot table updates by itself when the list changes. The full course goes on to Columns for a table of course by month, counts and averages with Summarise by, percentages with Show as, date groups, calculated fields, slicers and pivot charts.

Key points

  • A clean list, then Insert → Pivot table → Create
  • Rows = what to list, Values = what to add up
  • Filters ask a new question; the table updates by itself