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. If you have a more recent version of Excel, go to the Create a Pivot Table page.

This example uses the spreadsheet below, which contains records of a company's sales figures during 2010:

 ABCDE
1DateInvoice RefAmountSales Rep.Region
205/01/20102010-0001$33,242SmithNorth
307/01/20102010-0002$41,234BarnesSouth
408/01/20102010-0003$3,255BrownNorth
512/01/20102010-0004$34,124BrownNorth
615/01/20102010-0005$4,312SmithNorth
719/01/20102010-0006$41,233JonesSouth
822/01/20102010-0007$10,313SmithNorth
930/01/20102010-0008$26,001JonesSouth
1002/02/20102010-0009$34,234BarnesSouth
11.
.
.
.
.
.
.
.
.
.
.
.
.
.
.


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 in Excel 2003:

  1. Select Cell in Data Range Before Starting Pivot Table

    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.


  2. Excel 2003 Pivot Table Wizard Step 1 of 3

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

    Make sure the options

    'Microsoft Office Excel list or database'

    and

    'PivotTable'

    are selected and click Next.


  3. Excel 2003 Pivot Table Wizard Step 2 of 3

    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 range that you want to use for your Pivot Table and click Next.


  4. Excel 2003 Pivot Table Wizard Step 3 of 3

    The final dialog box is entitled 'PivotTable and PivotChart Wizard - Step 3 of 3' (see rightabove).

    This 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 your pivot table in a specified worksheet if you want to. If you are not sure, select the option New worksheet.

    Click Finish.


  5. Add Fields to Excel 2003 Pivot Table

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


  6.   Pivot Table Before Formatting:
    Excel 2003 Pivot Table




      Pivot Table After Currency
      Formatting:
    Excel 2003 Pivot Table with Formatting

    You will be presented with the simple Pivot Table (see rightabove), 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 ...;
    • The 'Format Cells' dialog box will be displayed. Ensure the Number tab of this dialog box 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 table on the right above.


Continue to Part 3 of the Excel Pivot Table Tutorial  >>