Course / PivotTables

PivotTables: rows, columns and percentages

Build a cross-tab with one field down the side and another across the top, then show each cell as a share of the total. Intermediate · 5 min · PivotTable, Columns, Cross-tab, % of Grand Total

Put one field in Rows and another in Columns, and a PivotTable becomes a cross-tab: a grid with a total for every combination, like sales of each product in each region, with totals for each row and column.

The PivotTable at H1 totals Sales by Region. You'll spread it across products, turn it around, then show shares instead of dollars.

Steps

  1. Click a cell in the PivotTable, then drag Product from the field list onto the Columns area.

  2. Turn it around: products down the side, regions across the top. Drag Product onto Rows and Region onto Columns.

  3. Which combinations matter most? Double-click Sum of Sales in the corner of the PivotTable (H1) and set Show values as to % of Grand Total.

Remember

Previous: PivotTables: count, average and more values · Next: PivotTables: sort, top 3 and filters