Free demo · Microsoft Excel · Lesson 8 · Advanced · PivotTables

A PivotTable in a minute: summarise a list without formulas

A PivotTable turns a long list into a short summary. Choose a field for the rows and a field for the values, and Excel does the adding and the counting.

Child | Course | Fee Asha | Art | 1500 Kabir | Sport | 1200 Riya | Computers | 1800 Imran | Sport | 1200 Tara | Music | 1400 … | … | [r]… Dev | Art | 1500
| | [b,fill=#dbe9f7]Row Labels | [b,fill=#dbe9f7,r]Sum of Fee | [b,fill=#dbe9f7,r]Count of Child Art | 36000 | 24 Computers | 32400 | 18 Music | 21000 | 15 Sport | 37200 | 31 [b]Grand Total | [b]126600 | [b]88
PivotTable Fields
Choose fields to add to report:
ChildCourseFee
Drag fields between areas below:
RowsCourse
ValuesSum of Fee
Count of Child
  1. 1Click any cell in the list, then Insert › PivotTable
  2. 2Keep New Worksheet, click OK: an empty PivotTable and the PivotTable Fields pane
  3. 3Tick Course: it goes to Rows, one row per course
  4. 4Tick Fee: it goes to Values and is added up
  5. 5Drag Child into Values: names are counted
  6. ✓88 rows summarised, no formulas. List changed? PivotTable Analyze › Refresh
  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 PivotTable summarises a long list in seconds, without a single formula. The list has to be tidy: one header row, one record per row, and no blank rows or merged cells. Click any cell in it and choose Insert › PivotTable; in the dialog, PivotTable from table or range, keep New Worksheet and click OK. Excel shows an empty PivotTable and the PivotTable Fields pane, in which every column heading of the list is a field. A field placed in Rows gives one line per item, here one line per summer camp course. A number field placed in Values is added up (Sum of Fee), a text field dragged into Values is counted (Count of Child), and a Grand Total appears at the bottom. In this lesson 88 registrations become four lines with the children and the fees per course. A PivotTable does not update by itself: after changing the list, click Refresh on the PivotTable Analyze tab. The full course goes on to Value Field Settings, Show Values As percentages, grouping, slicers and timelines, calculated fields and PivotCharts.

Key points

  • Insert › PivotTable on a tidy list
  • Rows give one line per item; Values add up or count
  • Refresh the PivotTable after the list changes