Course contents · PivotTables

Filters, grouping and % of total

Open the practice in the trainer →

Report filters

A field in the Filters area shows the report for a single value (e.g. one year). Options → Show Report Filter Pages creates a separate sheet for each value.

Multiple summaries and calculated fields

The same field can be added to Values twice — e.g. sum and count. A calculated field (Analyze → Fields, Items & Sets) is a formula over fields, e.g. =Revenue*0.2.

Grouping

Show Values As

Value Field Settings → Show Values As → % of Grand Total: each row’s share of the total.

Visualisation

A PivotChart is built straight from the pivot and changes with it. Slicers and the timeline are filter buttons for categories and dates, great for dashboards. A chart can live on its own chart sheet (easier to find and print) or be embedded in a worksheet (next to the data).

Check yourself

  1. How do you get a separate report sheet for each region?
  2. What is best for filtering by dates on a dashboard?