This page shows you how to create an Excel Pivot Table. The instructions on this page apply to current versions of Excel (2007 and later). If you have an earlier version of Excel, go to the page on how to create a pivot table in Excel 2003.
The following example uses the spreadsheet below, which contains the records of a company's sales figures during the first quarter of 2013:
|1||Date||Invoice Ref||Amount||Sales Rep.||Region|
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. In order for Excel to successfully do this, the following must be satisfied:
Click on the Pivot Table button, which is located on the left, within the 'Insert' tab.
You will be presented with the window entitled 'Create PivotTable' (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. If you are not sure, select the option 'New worksheet'
You will now be presented with an empty Pivot Table, and a 'Pivot Table Field List', which contains several data fields. Note that these are the headers from your initial data spreadsheet.
Within this 'Pivot Table Field List':
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 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 change this to sum the values as follows:
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. To do this:
Your final Pivot Table will be as shown on the rightabove.
If you are using Excel 2013, 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.