Creating a Pivot Table

In this section, I describe the basic steps required to create a pivot table, using the bank account data. Creating a pivot table is an interactive process. It's not at all uncommon to experiment with various layouts until you find one that you're satisfied with.

Specifying the Data

If your data is in a worksheet range or table, select any cell in that range and then choose Insert Tables PivotTable, which displays the dialog box shown in Figure 18-5.

Figure 18-5: In the Create PivotTable dialog box, you tell Excel where the data is and then specify a location for the pivot table.

Excel attempts to guess the range, based on the location of the active cell. If you're creating a pivot table from an external data source, you need to select that option and then click Choose Connection to specify the data source.

Tip 

If you're creating a pivot table from data in a worksheet, it's a good idea to first create a table for the range (by choosing Insert Tables Table). Then, if you expand the table by adding new rows of data, Excel will refresh the pivot table without the need to manually indicate the new data range.

Specifying the Location for the Pivot Table

Use the bottom section of the Create PivotTable dialog box to indicate the location for your pivot table. The default location is on a new worksheet, but you can specify any range on any worksheet, including the worksheet that contains the data.

Click OK, and Excel creates an empty pivot table and displays its PivotTable Field List, as shown in Figure 18-6.

Figure 18-6: Use the PivotTable Field List to build the pivot table.

Tip 

The PivotTable Field List is normally docked on the right side of Excel's window. By dragging its title bar, you can move it anywhere you like. Also, if you click a cell outside the pivot table, the PivotTable Field List is hidden.

Laying Out the Pivot Table

Next, set up the actual layout of the pivot table. You can do so by using either of these techniques:

• Drag the field names to one of the four boxes in the PivotTable Field List.

• Right-click a field name and choose its location from the shortcut menu.

Note 

In previous versions of Excel, you could drag items from the field list directly into the appropriate area of the pivot table. This feature is still available, but it's turned off by default. To enable this feature, choose PivotTable Tools Options PivotTable Options Options to display the PivotTable Options dialog box. Click the Display tab and add a check mark next to Classic PivotTable Layout.

The following steps create the pivot table presented earlier in this chapter (see "A Pivot Table Example"). For this example, I drag the items from the top of the PivotTable Field List to the areas in the bottom of the PivotTable Field List.

1. Drag the Amount field into the Values area. At this point, the pivot table displays the total of all the values in the Amount column.

2. Drag the AcctType field into the Row Labels area. Now the pivot table shows the total amount for each of the account types.

3. Drag the Branch field into the Column Labels area. The pivot table shows the amount for each account type, crosstabulated by branch (see Figure 18-7).

Figure 18-7: After a few simple steps, the pivot table shows a summary of the data.

Formatting the Pivot Table

Notice that the pivot table uses General number formatting. To change the number format used, select any value and choose PivotTable Tools Options Active Field Field Settings to display the Data Field Settings dialog box. Click the Number Format button and change the number format.

You can apply any of several built-in styles to a pivot table. Select any cell in the pivot table and choose PivotTable Tools Design PivotTable Styles to select a style.

Pivot Table Calculations

Pivot table data is most frequently summarized by using a sum. However, you can display your data using a number of different summary techniques. Select any cell in the Values area of your pivot table and then choose PivotTable Tools Options Active Field Field Settings to display the Data Field Settings dialog box. This dialog box has two tabs: Summarize By and Show Values As.

Use the Summarize By tab to select a different summary function. Your choices are Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, and Varp.

To display your values in a different form, use the drop-down control in the Show Values As tab. Your choices are described in the following table.

Open table as spreadsheet

Function	Result
Difference From	Displays data as the difference from the value of the Base item in the Base field
% Of	Displays data as a percentage of the value of the Base item in the Base field
% Difference From	Displays data as the percentage difference from the value of the Base item in the Base field
Running Total in	Displays the data for successive items in the Base field as a running total
% Of Row	Displays the data in each row or category as a percentage of the total for the row or category
% Of Column	Displays all the data in each column or series as a percentage of the total for the column or series
% Of Total	Displays data as a percentage of the grand total of all the data or data points in the report
Index	Calculates data as follows: ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))

You also can use the controls in the PivotTable Design Layout group to control various elements in the pivot table. For example, you can choose to hide the grand totals if you prefer.

The PivotTable Tools Options Show/Hide group contains additional options that affect the appearance of your pivot table. For example, you use the Show Field Headers button to toggle the display of the field headings.

Still more pivot table options are available in the PivotTable Options dialog box, shown in Figure 18-8. To display this dialog box, choose PivotTable Tools Options PivotTable Options Options. Or, right-click any cell in the pivot table and choose Table Options from the shortcut menu.

Figure 18-8: The PivotTable Options dialog box.

Modifying the Pivot Table

After you create a pivot table, changing it is easy. For example, you can add further summary information by using the PivotTable Field List. Figure 18-9 shows the pivot table after I dragged a second field (OpenedBy) to the Row Labels section in the PivotTable Field List.

Figure 18-9: Two fields are used for row labels.

Following are some tips on other pivot table modifications you can make:

• To remove a field from the pivot, select it in the bottom part of the PivotTable Field List and drag it away.

• If an area has more than one field, you can change the order in which the fields are listed by dragging the field names. Doing so affects the appearance of the pivot table.

• To temporarily remove a field from the pivot table, remove the check mark from the field name in the top part of the PivotTable Field List. The pivot table is redisplayed without that field. Place the check mark back on the field name, and it appears in its previous section.

• If you add a field to the Report Filter section, the field items appear in a drop-down list, which allows you to filter the displayed data by one or more items. Figure 18-10 shows an example. I dragged the Date field to the Report Filter area. The report is now showing the data only for a single day (which I selected from the drop-down list).

  
  Figure 18-10: The pivot table is filtered by date.

Copying a Pivot Table

A pivot table is very flexible, but it does have some limitations. For example, you can't insert new rows or column, change any of the calculated values, or enter formulas within the pivot table. If you want to manipulate a pivot table in ways not normally permitted, make a copy of it.

To copy a pivot table, select the entire table and choose Home Clipboard Copy (or press Ctrl+C). Then select a new worksheet and choose Home Clipboard Paste Paste Values. The contents of the pivot table are copied to the new location so that you can do whatever you like to them. You also may want to copy the formats from the pivot table. Select the entire pivot table and then choose Home Clipboard Format Painter. Then click the upper-left corner of the copied range.

Note that the copied information is not a pivot table, and it is no longer linked to the source data. If the source data changes, your copied pivot table does not reflect these changes.