This part of the tutorial details how to create a PivotTable in Excel. This article was written for Excel 2007 (as well as later versions). Instructions for earlier versions of Excel can be found in a separate article: How to create a PivotTable in Excel 2003?
As an example, consider the following table, which contains sales data for a company for the first quarter of 2016:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Invoice Ref | Amount | Sales Rep. | Region |
| 2 | 01/01/2016 | 2016 – 0001 | $ 819 | Barnes | North |
| 3 | 01/01/2016 | 2016 – 0002 | $ 456 | Brown | South |
| 4 | 01/01/2016 | 2016 – 0003 | $ 538 | Jones | South |
| 5 | 01/01/2016 | 2016 – 0004 | $ 1,009 | Barnes | North |
| 6 | 01/02/2016 | 2016 – 0005 | $ 486 | Jones | South |
| 7 | 01/02/2016 | 2016 – 0006 | $ 948 | Smith | North |
| 8 | 01/02/2016 | 2016 – 0007 | $ 740 | Barnes | North |
| 9 | 01/03/2016 | 2016 – 0008 | $ 543 | Smith | North |
| 10 | 01/03/2016 | 2016 – 0009 | $ 820 | Brown | South |
| 11 | … | … | … | … | … |
To begin with, let’s create a very simple pivot table that will show the total sales of each of the sellers according to the table above. To do this, do the following:
- Select any cell from the data range or the entire range to be used in the pivot table.ATTENTION: If you select one cell from a data range, Excel will automatically detect and select the entire data range for the PivotTable. In order for Excel to select a range correctly, the following conditions must be met:
- Each column in the data range must have its own unique name;
- The data must not contain empty lines.
- Clicking the button summary table (Pivot Table) in section Tables (Tables) tab Insert (Insert) Excel menu ribbons.
- A dialog box will appear on the screen. Create a PivotTable (Create PivotTable) as shown in the figure below.
Make sure the selected range matches the range of cells that should be used to create the PivotTable. Here you can also specify where the created pivot table should be inserted. You can select an existing sheet to insert a pivot table on it, or option – To a new sheet (New worksheet). click OK. - An empty pivot table will appear, as well as a panel Pivot table fields (Pivot Table Field List) with multiple data fields. Note that these are the headers from the original datasheet.
- In panels Pivot table fields (Pivot Table Field List):
- drag and drop Sales Rep. to the area Rows (Row Labels);
- drag and drop Amount в The values (Values);
- We check: in The values (Values) must be a value Amount field Amount (Sum of Amount), а не Amount by field Amount (Count of Amount).
In this example, the column Amount contains numeric values, so the area Σ Values (Σ Values) will be selected by default Amount field Amount (Sum of Amount). If in a column Amount will contain non-numeric or empty values, then the default pivot table can be selected Amount by field Amount (Count of Amount). If this happens, then you can change the quantity to the amount as follows:
- In the Σ Values (Σ Values) click on Amount by field Amount (Count of Amount) and select the option Value field options (Value Field Settings);
- On the Advanced tab Operation (Summarise Values By) choose an operation Sum (Sum);
- Click here OK.
Make sure the selected range matches the range of cells that should be used to create the PivotTable. Here you can also specify where the created pivot table should be inserted. You can select an existing sheet to insert a pivot table on it, or option – To a new sheet (New worksheet). click OK.
The PivotTable will be populated with the sales totals for each salesperson, as shown in the image above.
If you want to display sales volumes in monetary units, you must format the cells that contain these values. The easiest way to do this is to highlight the cells whose format you want to customize and select the format Monetary (Currency) section Number (Number) tab Home (Home) Excel menu ribbons (as shown below).
As a result, the pivot table will look like this:
- pivot table before number format setting
- pivot table after setting currency format
Please note that the default currency format depends on the system settings.
Recommended PivotTables in Latest Versions of Excel
In recent versions of Excel (Excel 2013 or later), on the Insert (Insert) button present Recommended pivot tables (Recommended Pivot Tables). Based on the selected source data, this tool suggests possible pivot table formats. Examples can be viewed on the Microsoft Office website.