Example of Creating a PivotTable on an Excel Worksheet with Excel VBA

Before listing the various features and operations available for PivotTables, let us look at an example of how to prepare one using the built-in tools of Microsoft Office Excel 2010.

Suppose the worksheet Data contains a list of cars. The fields of this list are:

• Plate numbers (digits)
• Plate numbers (letters)
• Car make
• Year of manufacture
• Year of acquisition
• Color
• Mileage
• Price (in units)
• Inspection
• Owner

Steps to Create a PivotTable

• Go to a new worksheet in the workbook and run the PivotTable command, which is selected from the PivotTable list located in the Tables group on the Insert tab.
• In the Create PivotTable dialog box, in the Table or Range field, enter the reference to the data list of cars

and click OK.

NOTE
As data for a PivotTable, you can use an Excel list, an external data source (for example, database tables), consolidation ranges, or another PivotTable.

• A space will be reserved on the worksheet for creating the table, and the necessary tools will be displayed, including the contextual tabs Options and Design under the PivotTable Tools mod.

• Define the required elements of the PivotTable by dragging the corresponding fields to the areas for Filters, Rows, Columns, and Values in the PivotTable Field List window (Fig. 8.30).

NOTE
The Values area always contains calculated data. By default, the basic operation added is Sum.
To change the aggregate function or define a calculated field for the Values area, click the drop-down arrow next to the field in the Values area and choose Value Field Settings.
In the Value Field Settings dialog box,

you can:

• select the required aggregate function (on the Summarize Values By tab),
• define additional calculations (on the Show Values As tab),
• and set the data display format (using the Number Format button).
• Create a calculated field Operation Period, defined by the formula:

=’Year of acquisition’ – ‘Year of manufacture’

To do this, go to the Options contextual tab under PivotTable Tools, in the Calculations group, choose Fields, Items, & Sets → Calculated Field.
In the Insert Calculated Field dialog box

create the required field.
After definition, it will appear in the upper part of the PivotTable Field List window.
Add this field to the Values area and choose Sum as the aggregate function.

TIP
To make the PivotTable more readable, move the Values field (which appears after adding fields to the Values area) from the Column Labels area to the Row Labels area.

• Add another field, Inspection, as a filter for the PivotTable.
• Group the data by the field Year of manufacture: place the pointer in the row of column headings and choose the Group Field command in the Group group on the Options contextual tab under PivotTable Tools.

In the Grouping dialog box specify the start and end dates and the grouping interval.

NOTE
You can also group rows of the PivotTable manually: select the required rows and use the Group Selection command from the Group group on the Options contextual tab. The Ungroup button removes the grouping.

• Format the PivotTable using one of the available styles in the PivotTable Styles collection on the Design contextual tab under PivotTable Tools.
• The completed PivotTable is shown:

Additional Notes on PivotTables

• A PivotTable is only a display tool for data. Data cannot be edited directly in a PivotTable.
• To change PivotTable data, you must edit the source data and then refresh the PivotTable.
• To refresh, use Refresh or Refresh All commands in the Data group on the Options contextual tab under PivotTable Tools.

Common Actions with PivotTables

• Rename fields (this does not affect the source data).
• Rearrange fields for filters, rows, and columns by dragging them.
• Group field items at different hierarchy levels (and rename groups as desired).
• Show or hide details in groups; top-level items are placed at the top or left edge of the PivotTable.
• Create PivotCharts.
• Sort PivotTable items.
• Display PivotTable pages on separate worksheets (Show Report Filter Pages command in the Options tab).
• Manage grand totals and subtotals.
• Use different aggregate functions for data analysis.
• Insert calculated fields.
• Apply styles for formatting.

TIP
To delete a PivotTable:

• Place the cursor inside it.
• Use Select → Entire PivotTable from the Actions group on the Options tab.
• Then go to the Home tab, in the Editing group, choose Clear → Clear All.