PivotTables

A PivotTable summarises a table of data, totals by region or counts by month, with no formulas, just by dragging fields. It's one of Excel's most powerful features, and one of the easiest to learn.

The data

PivotTables need data laid out as a simple table: one header row, one row per record, no blank rows or columns, and no merged cells.

Creating a PivotTable

1. Click anywhere in the data.
2. Choose Insert → PivotTable, and accept the suggested range and a new worksheet.
3. In the PivotTable Fields pane, tick or drag fields into the four areas:

Total by region

Drag Region to Rows and Amount to Values:

Eight rows became a three-line summary. With eighty thousand rows, it would be just as quick.

Two dimensions

Now drag Date to Columns. Excel groups dates by month automatically (right-click a date and choose Group to pick days, months, quarters or years):

Changing the calculation

Click the field in Values and choose Value Field Settings to summarise differently:

On the Show Values As tab, % of Grand Total turns totals into shares: East 43.1%, North 28.7%, South 28.2%.

Filtering and slicers

• Drag Rep to Filters, then pick a rep from the drop-down above the pivot to see only their figures.
• Or use PivotTable Analyze → Insert Slicer for clickable filter buttons, which are friendlier for anyone reading the report.

Keeping it up to date

A PivotTable doesn't recalculate on its own when the data changes. Right-click it and choose Refresh, or use Data → Refresh All. If you added rows beyond the original range, and the data isn't a Table, use Change Data Source to include them.

Charts from a pivot

With the pivot selected, PivotTable Analyze → PivotChart draws a chart that follows the pivot: filter or rearrange the pivot, and the chart changes too.