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
Click a cell in the PivotTable, then drag Product from the field list onto the Columns area.
Turn it around: products down the side, regions across the top. Drag Product onto Rows and Region onto Columns.
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
- A field in Rows and another in Columns makes a cross-tab, with totals for every row and column.
- Swap fields between areas to look at the same data from another side.
- Show values as % of Grand Total turns amounts into shares that add up to 100%.
Previous: PivotTables: count, average and more values · Next: PivotTables: sort, top 3 and filters