Leading the way in Microsoft Office Development

Step 1 - Type of Report

Highlight any cell within the data and select (Data > PivotTable and PivotChart Wizard).

Your data must contain column headings as these will be automatically used for the pivot table fields.

Creating just a pivot table with an Excel list is the default option so press Next.

Note that the graphics down the left hand side change to confirm your selection.

Microsoft Excel list or database - You data table must have unique column labels at the top of each column.

External data source - Includes Access databases and other Excel workbooks.

Multiple consolidation ranges - Multiple ranges containing similar data.

Another PivotTable or PivotChart -

Step 2 - Source Data

If you are using an Excel list and you have selected a cell within the list before invoking the wizard then the continuous range of cells will be selected.

If you select a single cell for the source data before displaying the Wizard the current region will be automatically selected. You can change this in step 3 of the Wizard ??

All the data should be highlighted so press Next.

Excel will automatically select the range of cells in the continuous range.

It is possible to change the source data range after the pivot table has been created.

Holding down the Shift key and pressing on the lower right cell will extend the data source to include that cell.

You can also insert rows into the data source and the data will automatically be included the next time the pivot table is refreshed.

Step 3 - Location

Decide whether you want to insert the pivot table onto a new worksheet or the existing worksheet.

Before clicking on the Finish button you can select Layout button to define the table layout of your pivot table.

New worksheet - A new worksheet will be inserted before the active sheet containing your pivot table report. This is the default.

Existing worksheet -

Layout - You can always changes these at any point after the pivot table has been created.

Options - Displays the (PivotTable > Table Options) dialog box.

Step 4 - Layout

After you have completed the steps the pivot table field list will be displayed to let you change how the table is organised.

Step 5 - Final PivotTable

The following pivot table summarising your data will be displayed.

Step 6 - Things to Remember

You should also enter a name for the pivot table, the default will be PivotTable1, PivotTable2, etc

If possible it is always better to base a new pivot table on an existing one as it will use the same memory cache for both tables.