The following guide shows you how to create an advanced Pivot Table in Excel 2003. If you have a more recent version of Excel, go to the Advanced Pivot Table page.
For this example, we use the following spreadsheet, which lists a company's sales figures for the first quarter of 2010:
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Date | Invoice Ref | Amount | Sales Rep. | Region |
2 | 05/01/2010 | 2010-0001 | $33,242 | Smith | North |
3 | 07/01/2010 | 2010-0002 | $41,234 | Barnes | South |
4 | 08/01/2010 | 2010-0003 | $3,255 | Brown | North |
5 | 12/01/2010 | 2010-0004 | $34,124 | Brown | North |
6 | 15/01/2010 | 2010-0005 | $4,312 | Smith | North |
7 | 19/01/2010 | 2010-0006 | $41,233 | Jones | South |
8 | 22/01/2010 | 2010-0007 | $10,313 | Smith | North |
9 | 30/01/2010 | 2010-0008 | $26,001 | Jones | South |
10 | 02/02/2010 | 2010-0009 | $34,234 | Barnes | South |
11 | . . . |
. . . |
. . . |
. . . |
. . . |
In this example, we create a pivot table that shows the total sales for each month of the year, broken down by sales region and sales rep.
The procedure for creating this advanced pivot table in Excel 2003, is as follows:
Click any single cell within the data or select the entire range of data that you want to use in your Pivot Table.
(Note: If you click any single cell within the data range, Excel will select the whole of your current data range as the range to be used in your Pivot Table.)
From the drop-down menu at the top of your spreadsheet, select Data → PivotTable and PivotChart Report...
You will be presented with a dialog box entitled 'PivotTable and PivotChart Wizard - Step 1 of 3' (shown on the rightabove).
Make sure the options
'Microsoft Office Excel list or database'
and'PivotTable'
are selected and click Next.
You will now be presented with the dialog box entitled 'PivotTable and PivotChart Wizard - Step 2 of 3' (see rightabove).
Make sure that the selected range is the data range that you want to use for your Pivot Table and click Next.
The final dialog box is entitled 'PivotTable and PivotChart Wizard - Step 3 of 3' (see rightabove).
This dialog box contains an option asking if you want the Pivot Table to appear on a new worksheet or in an existing worksheet. This allows you to place the pivot table onto a specified worksheet if required. Otherwise, select the default option New worksheet.
Click Finish.
Excel will now present you with an empty Pivot Table, and a 'Pivot Table Field List'. Note that the data fields within the 'Field List' are the headers from your initial data spreadsheet.
As we want to break down the sales figures per month, we will use the 'Date' field as the row field (we will group this by month later). Therefore, we need to drag the 'Date' field into the area of the Pivot Table marked 'Drop Row Fields Here'.
Drag the 'Amount' field into the area of the Pivot Table marked 'Drop Data Items Here'. This provides you with the sum of the sales amounts, in the middle of the Pivot Table.
We also want to break down the data by sales region and sales rep. Therefore, we need to drag the 'Sales Rep.' and then the 'Region' field into the area of the Pivot Table marked 'Drop Column Fields Here'.
You will be presented with the above pivot table on the right, which shows the total sales amounts for each sales rep., and each sales region, on each date.
However, we want to group the dates by month. To do this:
The resulting Pivot Table is shown below:
As required, the final pivot table shows the total sales for each of the sales regions and for each sales rep. during each month of the year.
The Pivot Table might be improved visually, by formatting. For example, if you format columns B - G in a currency format, this will make the values easier to read. The resulting spreadsheet, with currency formatting, is shown in the following section.
The Pivot Table page field allows you to view the data for a single value or set of values in one or more of 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, in the above example pivot table, simply drag the 'Region' field header into the page drop area, as shown below.
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 Sales Region 'North'.
The resulting Pivot Table, which displays just the sales for the North region, is shown aboveon the right.
You can also quickly view just the sales for the South region by selecting 'South' from the drop down menu.