Pivot Table Tutorial Part 2 - Create a Pivot Table in Excel 2003

The following step-by-step guide shows you how to create a Pivot Table in Excel 2003, using the example spreadsheet from Part 1 of the tutorial. This spreadsheet (shown below), contains details of a company's sales during 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 .
.
.
.
.
.
.
.
.
.
.
.
.
.
.

Imagine you want to create a simple pivot table, showing the total sales for each of the four sales people in the above spreadsheet. The procedure for creating this pivot table, in Excel 2003, is shown in the following steps:

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', which contains the data fields (note that these are the headers from your initial data spreadsheet).

Drag the 'Sales Rep.' 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'.

Add Fields to Excel 2003 Pivot Table
6.

You will be presented with the simple Pivot Table on the right, which shows the total sales for each sales person.

If you want the sales to be displayed as a currency, this is done by formatting the cells containing these values. To do this:

  • Select the column containing the cells to be formatted.
  • Right click on this column with the mouse and select the option Format Cells ...
  • A window will pop up. Ensure the Number tab of this window is selected
  • From the list of data types, select Currency, and then, from the options that appear on the right, select the currency type and the number of decimal places that you want to display.
  • Click OK

Your final Pivot Table will be as shown in the lower table on the right.

Pivot Table Before Formatting:
Excel 2003 Pivot Table

Pivot Table After Currency Formatting:
Excel 2003 Pivot Table with Formatting
Go To Excel Pivot Table Tutorial Part 3 - Advanced Pivot Table in Excel 2003

Return to the Excel Pivot Table Tutorial Page

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