3.1 PivotTables

Work through these

• Rows, columns, values, filters — the four wells

  A pivot is built by dragging fields into four areas. Which area a field lands in decides whether it becomes a row heading, a column heading, a number or a filter. Once these four roles are clear, every pivot you meet becomes readable.

• Value settings: sum, count, average, % of total

  The same field can be totalled, counted, averaged or shown as a share of the whole. Changing that is a setting rather than a rebuild. The share option is the one most people never find.

• Grouping dates and numbers; expanding and drilling down

  Dates can be grouped into months or years and numbers into bands, and any figure can be opened up to see the rows behind it. That drill-down is the fastest way to check a suspicious total.

• Refreshing, and the source-range mistake everyone makes once

  A pivot is a snapshot: when the underlying data changes, the table stays stale until refreshed. The classic error is adding rows below the original range and wondering why they never appear.

Practice file

• xl-05-pivot-sales.xlsx

  800 rows of fictional sales data for PivotTables, grouping, % of total and a first measure.

Opens in Excel. Every name, number and address in these files is made up. No account needed to download.