Create a PivotTable

Excel provides two ways to create a PivotTable report. When you use an automatic PivotTable, Excel evaluates both the structure and kind of data in your range and creates the PivotTable for you. This takes the guesswork out of designing the PivotTable because Excel determines which fields are most likely to be rows, columns, or values.

If you prefer to design the PivotTable yourself, you can create a manual PivotTable.

The following example shows how a simple list of expenses can become a meaningful summary of total expenditures by category.

Amounts spent in January, February, and March on four categories of expenses

Monthly expenses source data

The same data summarized in a PivotTable

Monthly expenses as a PivotTable

Do any of the following:

ShowCreate an automatic PivotTable

If you have limited experience with PivotTables, or are not sure about how to get started, an automatic PivotTable is a good choice. When you use this feature, Excel determines a meaningful layout by matching the data with the most suitable areas in the PivotTable. This helps give you a starting point for additional experimentation. Automatic PivotTable layouts are intended as an aid and are based on a best guess about the contents of the source data (source data: The list or table that's used to create a PivotTable. Source data can be taken from an Excel list or database, or another PivotTable.). After a basic PivotTable is created and you can visualize its components, you should explore different orientations and rearrange fields to achieve your specific results.

  1. Open the workbook in which you want to create the PivotTable.
  2. Click a cell in the list or table that contains the data that you want to use in the PivotTable.
  3. On the Data tab, under Analysis, click PivotTable.

Data tab, Analysis group

Excel creates a PivotTable on a new sheet and displays the PivotTable Builder.

PivotTable Builder

Callout 1  Fields that can be included in the PivotTable

Callout 2  Areas that populate the PivotTable; try different layouts by dragging fields between these areas

  1. Do any of the following:
To Do this
Add a field In the Field name area, select the check box for the field. By default, nonnumeric fields are added to the Row Labels area, date and time hierarchies are added to the Column Labels area, and numeric fields are added to the Values area.
Remove a field In the Field name area, clear the check box for the field.
Move a field Drag the field from one area of the PivotTable Builder to another, for example, from Column Labels to Row Labels.
Refresh the PivotTable

On the PivotTable tab, under Data, click Refresh.

PivotTable tab, Data group

  1. The PivotTable is automatically updated as you make changes.

ShowCreate a manual PivotTable

If you are an experienced PivotTable user, or already know the data arrangement that you want, you can create a PivotTable manually.

  1. Open the workbook in which you want to create the PivotTable.
  2. Click a cell in the list or table that contains the data that you want to use in the PivotTable.
  3. On the Data tab, under Analysis, click the arrow next to PivotTable, and then click Create Manual PivotTable.

Data tab, Analysis group

  1. Verify the location of the data that you want to analyze (assumed from step 2), click where Excel should place the PivotTable, and then click OK.

Excel creates a PivotTable in the location that you specified and displays the PivotTable Builder.

PivotTable Builder

Callout 1  Fields that can be included in the PivotTable

Callout 2  Areas that populate the PivotTable; try different layouts by dragging fields between these areas

  1. Do any of the following:
To Do this
Add a field In the Field name area, select the check box for the field. By default, nonnumeric fields are added to the Row Labels area, date and time hierarchies are added to the Column Labels area, and numeric fields are added to the Values area.
Remove a field In the Field name area, clear the check box for the field.
Move a field Drag the field from one area of the PivotTable Builder to another, for example, from Column Labels to Row Labels.
Refresh the PivotTable

On the PivotTable tab, under Data, click Refresh.

PivotTable tab, Data group

  1. The PivotTable is automatically updated as you make changes.

ShowUse an external data source or multiple data sources to create a PivotTable

If you want to create a PivotTable from an external source of data, or from multiple ranges in one or more worksheets, you can use the PivotTable Wizard to guide you through the process.

  1. Press COMMAND + ALT + P .
  2. In the PivotTable Wizard, click to select External data source or Multiple consolidation ranges, and then follow the rest of the steps in the wizard.

See also

About PivotTables

Consolidate multiple data sources in a PivotTable

 
 
Applies to:
Excel for Mac 2011