1.

To begin, start Excel 2003 and open the file Practice 3 in your Excel 2003 Practice folder. File->Open

Sheet4 of this workbook should contain an empty pivot table frame, and a PivotTable Field List based on the data in Sheet1.

2.

Arrange the data by dragging the Month field from the PivotTable field List to the Drop Page field Here area of the PivotTable.

Next, drag the Salesman field to the row area of the PivotTable, and the Region field to the column area of the table.

Finally, drag the Sales field to the data area of the table. The resulting Pivot Table should look like this.

Note: you can move and position your Field list or PivotTable toolbar as required by dragging them with the mouse.

3.

Next, click on the sum of sales heading in the upper left of the table, and then click the Field Settings button on the PivotTable toolbar.

This will display the PivotTable field dialogue box.

The next step is to click on the number button, and choose the Accounting number format from the list that is displayed in the Format Cells box.

Click the OK button when you are finished.

4.

When you click OK in the Format cells box, you will be returned to the PivotTable field box.

Select Average from the Summarise by: list, and then click OK.

You have now added formatting to, and modified calculations in the PivotTable.

You can see that the sales data is now presented in accounting format, and that the totals in the grand total row and column are calculated as averages instead of sums.

5.

To create a chart based on this table, first click on any cell in the table, and then click the chart button on the standard toolbar, or on the PivotTable toolbar.

This action will automatically create a chart based on this PivotTable. The chart will be located on a new sheet labelled Chart 1.

If you rearrange the fields in the PivotTable, the PivotChart will be automatically rearranged accordingly. If you click the refresh button (!) the PivotTable and The PivotChart will be updated with any changes in the source data. (The source data is available on Sheet1).

6.

Save your work and exit Excel.