With a PivotTable, you can conveniently drag and drop columns of your data to different areas of the table to examine relationships or trends that may not be obvious in a traditional Excel table or database. (You can base a PivotTable on data in your current workbook or on external data.)
Rather than build several regular tables to explore how columns from an Excel worksheet relate to each other (or to see the data summarized in different ways), you can use one PivotTable to do the same thing. With a PivotTable, you can alter the table design without cutting, copying, pasting, or adjusting formulas and cell references. In short, PivotTables enable you to organize your data in meaningful ways, without doing a lot of tedious work. You could say that a PivotTable is like several data tables rolled into one.
Ideally, the source data for a PivotTable should be structured like a traditional Excel table or database. The source data should have a row of unique column headings distinguishing the data, and there should be no empty columns interspersed within the data. Also, blank rows in the source data can limit the usefulness of your PivotTable.
The following image shows a block of contiguous data that is ideal for a PivotTable.
Notice that there are no empty rows or columns and that every column of data has a unique label. When data like this is arranged as a PivotTable, you can quickly create views of the data that show (among other things):
- The profit for each product type across regions sales.
- The figures for each product type across regions.
- The profit for different product types by various salespeople
These are only a few of the scenarios that you could generate with a PivotTable based on the given data.
Creating a PivotTable
To create a PivotTable, select the range of data that you want to base the table on, and then click the PivotTable button on the Insert tab:
Notice that you are allowed to select data from an Excel table or range, or from an external data source. If you forgot to select the range before you opened the dialog, you can enter it now. If you choose the external data source option, you can base your PivotTable on data outside your current workbook (i.e. another workbook, or another source like an external database).
- To start, select the “Use an external data source” radio button and click Choose Connection:
- Then you will see a list of existing connections. A typical existing connection could be an MS query or a connection you previously made to an Access database for some other purpose.
- Once you select your data source, you can then choose to locate your PivotTable in an existing worksheet or a new worksheet. If you choose to locate it in an existing worksheet, you can specify the location for the upper left corner of the PivotTable by entering it directly into the Location field (as a cell reference), or by clicking the target cell with your mouse.
- If you choose the New Worksheet option, your PivotTable will be located in the upper left corner of a new worksheet that will be added to your workbook.
- Once you are ready, click OK to create your PivotTable:
- You can see a new PivotTable area and the corresponding PivotTable Field List placed in the existing worksheet that contains the source data.
- Once your PivotTable area appears, you can add information to it by placing checks in the boxes next to the fields in the PivotTable Field List. For this example, checks will be placed next to the Month, Salesman, Region, and Profit fields:
- The PivotTable area will now be populated with the corresponding data:
- As you can see in the following image, the profit has been organized by Month. It has also been organized by Salesman, with a total profit for each Salesman in the Sum of Profit column. Because Region has been checked in the PivotTable Field List, you can also see a profit breakdown by region for each salesman.
- The following close-up view of the table tells us that the total profit for Month 1 is 2775. The Salesman A. Smith generated a total of 840 in profit with 590 from the East region, and 250 from the Northeast region.
- As you can see, a PivotTable can provide more informative views of your data than a regular table.
Using the PivotTable Tools Tabs
You may want to change the design of an Excel table or PivotTable, or refresh their data, or set some options for them.
If you don't see Table Tools or PivotTable Tools on your worksheet, do this:
- Click any cell in the table or PivotTable.
If you've selected an Excel table, you'll see the Table Tools with a Design tab. If you've selected a PivotTable, you'll see the PivotTable Tools with an Analyze and a Design tab.
Adding and Removing Data with the Field List
The Field List appears when you click anywhere in the PivotTable. If you’ve clicked inside the PivotTable but don't see the Field List, do this to open it:
- Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.
- Click Analyze> Field List
The Field List has a field section where you’ll pick the fields you want to show in your PivotTable, and an areas section where you can arrange those fields the way you want.
Add and rearrange fields in the Field List
Use the field section of the Field List to add fields to your PivotTable:
- Check the box next to a field name to place the field in its default area of the Field List.
Typically, non-numeric fields are added to the Rows area, numeric fields are added to the Values area, and Online Analytical Processing (OLAP) date and time hierarchies are added to the Columns area.
Use the areas section of the Field List to rearrange fields the way you want by dragging them between the four areas.
Fields you put in the different areas are shown in the PivotTable as follows:
- Filters area fields are shown as top-level report filters above the PivotTable, like this:
- Columns area fields are shown as Column Labels at the top of the PivotTable, like this but depending on the hierarchy of the fields, columns may be nested inside columns that are higher in position:
- Rows area fields are shown as Row Labels on the left side of the PivotTable, like this but depending on the hierarchy of the fields, rows may be nested inside rows that are higher in position;
- Values area fields are shown as summarized numeric values in the PivotTable, like this:
- If you have more than one field in an area, you can rearrange their order by dragging them to the position you want. To delete a field from the PivotTable, drag the field out of its areas section.
Changing the Field List Layout
If you want to change how sections are shown in the Field List, click the Tools button, and then pick the layout you want.
Pivoting Data
In Excel, you can pivot data in a PivotTable or PivotChart by changing the field layout of the data. By using the PivotTable Field List, you can add, rearrange, or remove fields to show data in a PivotTable or PivotChart exactly the way that you want.
By default, changes you make in the PivotTable Field List are automatically updated in the report layout. To improve performance when you are accessing a large amount of external data, you can temporarily switch to manual updating.
Manual updating mode allows you to quickly add, move, and remove fields in the PivotTable Field List. However, you cannot use the report until you switch back to automatic updating mode.
- To enable manual updating of the PivotTable, at the bottom of the PivotTable Field List, select the Defer layout update checkbox.
- In the PivotTable Field List, make the field layout changes, and then click Update to manually update the layout in the PivotTable.
- To return to automatic updating after you finish changing the report layout in the Field List, clear the Defer layout update checkbox.
Note: A PivotTable starts with automatic updating each time that you open the workbook.