This page provides a step-by-step guide of how to create an advanced pivot table in current versions of Excel (2007 and later). If you have an earlier version of Excel, go to the page on how to create an advanced pivot table in Excel 2003.
For the example pivot table, we will use the spreadsheet below, which stores a company's sales figures for the first quarter of 2013:
|1||Date||Invoice Ref||Amount||Sales Rep.||Region|
For our advanced pivot table example we will create a pivot table that shows the total sales for each month of the year, broken down by sales region and sales rep. The process for creating this Pivot Table is described below. Note that the initial steps are the same as for the basic pivot table in part 2 of this tutorial.
Select any cell within the data range or select the entire data range to be used in your Pivot Table.
Click on the Pivot Table button, which is generally 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 is the range 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 specify a worksheet in which to place the pivot table. If you are not sure, select the option 'New worksheet'.
You will now be presented with an empty Pivot Table, and a window entitled 'PivotTable Field List' (or 'PivotTable Fields', depending on your version of Excel). This list contains the data fields for your Pivot Table. Note that these are the headers from your data spreadsheet.
We want the Pivot Table to show the sums of the sales figures for each month, broken down by region and sales rep.
Therefore, from the 'Pivot Table Field List':
The resulting Pivot Table will be populated with the daily sales totals for each sales region and each sales rep, as shown on the rightabove.
As required, the final pivot table (shown on the rightabove) displays the total monthly sales, broken down by sales region and sales rep.
The Pivot Table might be improved visually, by formatting. For example, if columns B - G are formatted as currencies, this will make the Pivot Table easier to read.
The Pivot Table report filter allows you to view the data for a single value, or a selection of specified values in your data fields. For example, in the pivot table above, you could view just the data for the North sales region or just the data for the South sales region.
In order to view just the data for the 'North' sales region, return to the 'Pivot Table Field List', and drag the 'Region' field header into the 'Report Filter' area (named the 'Filters' area in Excel 2013).
You will see that the 'Region' field is now located in the page drop area, at the top left of the Pivot Table. Use the drop-down list for this field to select the Region 'North'. The resulting Pivot Table, (shown on the rightabove), displays just the sales for the North region.
You can also quickly view just the sales for the South region by selecting 'South' from the drop down menu.