Your first PivotTable
Every Friday, Maria from the sales team works out how much revenue each manager brought in that week. A week adds up to forty orders:
Maria filters the table by Emma, adds up her orders and copies the total into a separate small table. Then she does the same for Ben, Olivia and Leo. Half an hour for four numbers, and she starts over if an order turns out to have a mistake.
Excel can build this kind of report by itself. It's called a PivotTable: a short table of totals that Excel calculates from a long one.
You could build the same report with SUMIF formulas, one per manager. But then you'd have to make the list of managers yourself and write new formulas for every new question. A PivotTable finds all the managers in the table by itself, and you change the question with a single checkbox.
A question turns into fields
A PivotTable is built from fields. A field is a column of the source table, and its name comes from the header: "Manager", "City", "Revenue, $".
You arrange fields in the PivotTable Fields pane. It has a list of fields with checkboxes and four areas: Filters, Rows, Columns and Values. For your first report, Rows and Values are enough.
To build a report, split the question into two parts:
- what to group by: "each manager". This field goes into the Rows area, and each manager gets a row of their own in the report;
- what to calculate: "revenue". This field goes into Values, and Excel adds up the revenue within each row.
| Manager | City | Category | Revenue, $ |
|---|---|---|---|
| Emma | Boston | Electronics | 12,400 |
| Ben | Denver | Clothing | 3,200 |
| Emma | Austin | Electronics | 8,700 |
| Olivia | Boston | Clothing | 5,100 |
| Olivia | Denver | Electronics | 4,800 |
| Ben | Boston | Clothing | 2,900 |
| Leo | Austin | Electronics | 15,300 |
| Emma | Denver | Clothing | 6,100 |
| Leo | Boston | Electronics | 7,400 |
| Ben | Austin | Clothing | 3,900 |
| Emma | Boston | Clothing | 4,400 |
| Leo | Denver | Electronics | 9,800 |
| Row Labels | Sum of Revenue, $ |
|---|---|
| Ben | 10,000 |
| Emma | 31,600 |
| Leo | 32,500 |
| Olivia | 9,900 |
| Grand Total | 84,000 |
| Row Labels | Sum of Revenue, $ |
|---|---|
| Austin | 27,900 |
| Boston | 32,200 |
| Denver | 23,900 |
| Grand Total | 84,000 |
| Row Labels | Sum of Revenue, $ |
|---|---|
| Clothing | 25,600 |
| Electronics | 58,400 |
| Grand Total | 84,000 |
In the diagram, the same twelve orders give three different reports, and the only thing that changes is the field in Rows.
Two checkboxes instead of formulas
Build Maria's report right on her table. You don't need to select the whole table: click any of its cells, and Excel finds where the table starts and ends by itself. After you click PivotTable, Excel opens the Create PivotTable dialog with the table range and the report location already filled in, so all that's left is to click OK. An empty frame for the future report appears on the sheet, with the field list next to it. There, Excel sends a text field to Rows and a number field to Values by itself.
The report doesn't follow the table
A PivotTable remembers the numbers at the moment it's created and doesn't follow the table by itself. If you fix an order in the table, the report keeps showing the old total. To recalculate it, click the report and on the PivotTable Analyze tab click Refresh.
You don't change the report cells by hand. A wrong number is fixed in the source table, and then the report is refreshed. The same goes for messy names: the PivotTable treats "Boston" and "Boston " with a space at the end as two different cities and gives them two rows. Clean such names with the TRIM function and click Refresh.
Refresh recalculates only the rows the report was built from. An order added below the table won't make it into the report: to include it, create the PivotTable again.
Maria's Friday half hour has shrunk to two checkboxes. And a mistake in an order no longer means "count everything again": just fix the number in the table and click Refresh.