PivotTables

A PivotTable summarises a table without formulas. It is the fastest way to explore a dataset in Excel.

Create one

1. Click any cell in the Sales table.
2. Choose Insert > PivotTable and place it on a new worksheet.
3. Use the PivotTable Fields pane on the right to build the summary.

The four areas

Area	What it does
Rows	One row for each distinct value of the field
Columns	One column for each distinct value
Values	The numbers to summarise
Filters	A filter that applies to the whole PivotTable

Drag Region to Rows and Revenue to Values. You have revenue by region. Drag Category to Columns and you have a cross-tabulation of region by category.

Change the calculation

Right-click a number and choose Summarize Values By to switch between Sum, Count, Average, Max and Min.

Show Values As changes how the number is displayed without changing the calculation. Useful options are:

• % of Grand Total
• % of Column Total
• Running Total In
• Difference From

Drag Revenue into Values twice to show the total and the percentage of the grand total side by side.

Group dates and numbers

Put Date in Rows, right-click any date and choose Group. Pick Months, Quarters or Years. Number fields can be grouped into bands the same way, for example Units in steps of 5.

Sort and filter

Right-click a value and choose Sort to rank rows by a total. In the Rows drop-down, Value Filters > Top 10 keeps only the highest or lowest items.

Slicers

A slicer is a set of buttons that filters the PivotTable. Click the PivotTable, then choose PivotTable Analyze > Insert Slicer and tick Rep. Use Report Connections on the slicer to make one slicer control several PivotTables.

Refresh

A PivotTable does not update on its own when the data changes. Right-click it and choose Refresh, or use Data > Refresh All. Because the source is an Excel Table, new rows are included automatically after a refresh.

Calculated fields

PivotTable Analyze > Fields, Items & Sets > Calculated Field adds a new value computed from other fields, such as an average price:

=Revenue/Units

The calculation is applied to the totals shown in each cell, not row by row.

PivotCharts

With the PivotTable selected, choose PivotTable Analyze > PivotChart. The chart follows the PivotTable’s filters and slicers.

Practice

Build a PivotTable that shows revenue by rep for each month, with a slicer for Category.