Pivot Table Tutorial Part 2 - Create an Excel Pivot Table
This page provides a step-by-step guide of how to create a basic Excel Pivot Table in current versions of Excel (2007 and later).
For this pivot table example we use the spreadsheet below, which lists a company's sales figures during the first quarter of 2016. The spreadsheet records the sale date, the invoice reference, the invoice total, the name of the sales representative, and the sales region.
| 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 | . . . |
. . . |
. . . |
. . . |
. . . |
We will first create a very simple pivot table, which shows the total sales for each of the four sales reps in the above spreadsheet. To do this:
-
Select any cell within the data range or select the entire data range to be used in your Pivot Table.
(Note: If you select a single cell in the data range, Excel will automatically identify, and select the whole data range for your Pivot Table.)
-
Click on the Pivot Table button, which is located within the 'Tables' grouping, on the 'Insert' tab of the Excel ribbon.
-
You will be presented with the 'Create PivotTable' dialog box (shown on the rightabove).
Make sure that the selected range refers to the range of cells that you want to use for your Pivot Table.
There is also an option asking where you want the Pivot Table to be placed. This allows you to place your pivot table in a specified worksheet. Otherwise, select the default option 'New worksheet'.
Click OK.
-
You will now be presented with an empty Pivot Table, and the 'Pivot Table Field List' task pane, which contains several data fields. Note that these are the column headers from your data spreadsheet.
Within the 'Pivot Table Field List' task pane:
- Drag the 'Sales Rep.' field into the area marked 'Row Labels' (or 'Rows');
- Drag the 'Amount' field into the area marked 'Σ Values';
- Check: Make sure that the value in the 'Σ Values' section reads "Sum of Amount" and not "Count of Amount".
In this example, the values in the 'Amount' column are all numeric and so the 'Σ Values' section will default to calculating the "Sum of Amount".
However, if you have any non-numeric or blank values in the 'Amount' column of your original data sheet, your pivot table may default to displaying the "Count of Amount" instead of the "Sum of Amount". If this happens, you can correct this as follows:
- Within the 'Σ Values' box, click on the text "Count of Amount" and select the option Value Field Settings...;
- Within the 'Summarise Values By' tab, select the option Sum;
- Click OK.
-
Pivot Table Before Formatting:
Pivot Table After Currency
Formatting:
Your Pivot Table will be populated with the total sales for each sales rep, as shown on the rightabove.
If you want the sales totals to be displayed as currency values, this is done by formatting the cells containing these values.
The easiest way to do this is to highlight the cells to be formatted and then to click on the currency format button, which is found in the 'Number' group on the 'Home' tab of the Excel ribbon (see below).
The resulting Pivot Table will be as shown on the rightabove.
Note that the default currency format used will depend on the settings on your computer.
'Recommended Pivot Tables' in the Latest Versions of Excel
If you are using one of the latest versions of Excel (Excel 2013 or later), your insert tab will also have the option to produce 'Recommended Pivot Tables'. This option presents you with suggested pivot table formats, based on your data. An example of this is available on the Microsoft Office website.