Pivot Table Tutorial Part 4 - Create an Advanced Pivot Table in Excel 2003

The following guide shows you how to create an advanced Pivot Table in Excel 2003. In this example, we use the same data spreadsheet as was used to create the basic pivot table in part 2 of this tutorial. For convenience, this spreadsheet is repeated below:

  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 .
.
.
.
.
.
.
.
.
.
.
.
.
.
.

For this advanced pivot table example we want to create a pivot table that shows the total sales amounts for each of the sales regions and for each sales rep., during each month of the year. The procedure for creating this advanced pivot table in Excel 2003, is shown in the following steps. The first few steps are the same as for the basic pivot table, described in part 2 of this tutorial.

1.

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 as long as:

- The data has headers at the top of each column
and
- The data does not contain any blank rows
Select Cell in Data Range Before Starting Pivot Table
2.

From the drop-down menu at the top of your spreadsheet, select Data → PivotTable and PivotChart Report...

You will be presented with the window entitled 'PivotTable and PivotChart Wizard - Step 1 of 3' (shown on the right).

Make sure the options

'Microsoft Office Excel list or database'
and
'PivotTable'

are selected and click Next

Excel 2003 Pivot Table Wizard Step 1 of 3
3.

You will now be presented with the window entitled 'PivotTable and PivotChart Wizard - Step 2 of 3' (see right).

Make sure that the selected range is the range that you want to use for your Pivot Table and click Next

Excel 2003 Pivot Table Wizard Step 2 of 3
4.

The final window is entitled 'PivotTable and PivotChart Wizard - Step 3 of 3' (see right).

There is an option asking if you want the Pivot Table to appear on a new worksheet or in an existing worksheet. If you are not sure, select the option 'New worksheet'

Click Finish

Excel 2003 Pivot Table Wizard Step 3 of 3
5.

Excel will now present you with an empty Pivot Table, and a 'Pivot Table Field List'. The data fields within the data fields 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'.

Add Fields to Excel 2003 Pivot Table

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 our data by sales region and sales rep. Drag the 'Sales Rep.' and then the 'Region' field into the area of the Pivot Table marked 'Drop Column Fields Here'.

6.

You will be presented with the 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:

  • Right click on any of the dates in the left hand column of the Pivot Table
  • Select the options Group and Show Detail and then Group...
  • A window will pop up. Select the option Months
    (note that you can also group dates and times by other time periods, such as quarters, days, hours, etc)
  • Click OK

The resulting Pivot Table is shown below:

Advanced Pivot Table in Excel 2003
Advanced Pivot Table in Excel 2003, Grouped by Month

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. This has been done in the spreadsheet used in the following section (below).


Pivot Table Page Field

The Pivot Table page field allows you to view the data for a single value in one of your data fields. For example, in the example pivot table above, you could view just the data for the North sales region or just the data for the South sales region. This is illustrated below.

In order to view just the data for the 'North' sales region, in the above example, simply drag the 'Region' field header into the page drop area, as shown below.

Add Page Field in Excel 2003 Advanced Pivot Table
Excel 2003 Advanced Pivot Table with page field

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 on the right.

You can also quickly view just the sales for the South region by selecting 'South' from the drop down menu.


Go To Excel Pivot Table Tutorial Part 5 - How to Sort a Pivot Table

Return to the Excel Pivot Table Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net