A PivotTable summarises a large table, such as total sales by region and month, with a few clicks and no formulas.
Prepare your data
- One header row, with a name on every column.
- No blank rows or columns inside the data.
- No merged cells. One kind of data per column.
Press Ctrl + T to turn the data into a table, so the PivotTable grows with it.
Create the PivotTable
- Click any cell in the data.
- Go to Insert → PivotTable.
- Choose a new worksheet and click OK.
Build the report
In the field list on the right, drag fields into four areas:
- Rows: categories down the side, such as Region.
- Columns: categories across the top, such as Month.
- Values: the numbers to summarise, such as Sales (Sum).
- Filters: a field to filter the whole report.
Useful options
- Right-click dates and choose Group to see months, quarters or years.
- Change Sum to Count or Average in Value Field Settings.
- Insert a Slicer for click-to-filter buttons.
Refresh
A PivotTable does not update by itself. After changing the data, right-click and choose Refresh, or press Alt + F5.