Creating Dynamic Lists by Using PivotTables in Microsoft Office Excel 2007

Chapter at a Glance

When you create Microsoft Office Excel 2007 worksheets, you must consider how you want the data to appear when you show it to your colleagues. You can change the formatting of your data to emphasize the contents of specific cells, sort and filter your worksheets based on the contents of specific columns, or hide rows containing data that isn’t relevant to the point you’re trying to make.

One limitation of the standard Office Excel worksheet is that you can’t change how the data is organized on the page. For example, in a worksheet in which each column represents an hour in the day, each row represents a day in a month, and the body of the worksheet contains the total sales for every hourly period of the month, you can’t easily change the worksheet so that it displays only sales on Tuesdays during the afternoon.

An Excel 2007 tool enables you to create worksheets that can be sorted, filtered, and rearranged dynamically to emphasize different aspects of your data. That tool is the PivotTable.

In this chapter, you’ll learn how to create and edit PivotTables from an existing worksheet and how to create a PivotTable with data imported from a text file.

Analyzing Data Dynamically by Using PivotTables

Excel 2007 worksheets enable you to gather and present important data, but the standard worksheet can’t be changed from its original configuration easily. As an example, consider the worksheet in the following graphic.

This worksheet records monthly package volumes for each of nine distribution centers in the United States. The data in the worksheet is organized so that each row represents a distribution center, whereas the columns in the body of the worksheet represent a month of the year. When presented in this arrangement, the monthly totals for all centers and the yearly total for each distribution center are given equal billing: neither set of totals stands out.

Such a neutral presentation of your data is versatile, but it has limitations. First, although you can use sorting and filtering to restrict the rows or columns shown, it’s difficult to change the worksheet’s organization. For example, in a standard worksheet you can’t reorganize the contents of your worksheet so that the hours are assigned to the rows and the distribution centers are assigned to the columns.

The Excel 2007 tool to reorganize and redisplay your data dynamically is the PivotTable. You can create a PivotTable, or dynamic worksheet, that enables you to reorganize and filter your data on the fly. For instance, you can create a PivotTable with the same layout as the worksheet shown previously, which emphasizes totals by month, and then change the PivotTable layout to have the rows represent the months of the year and the columns represent a distribution center. The new layout emphasizes the totals by regional distribution center, as shown in the following graphic.

To create a PivotTable, you must have your data collected in a list. The new Excel 2007 data tables mesh perfectly with PivotTable dynamic views; not only do the data tables have a well-defined column and row structure but the ability to refer to a data table by its name also greatly simplifies PivotTable creation and management.

The following graphic shows the first few lines of the data table used to create the PivotTable just shown.

Notice that each line of the table contains a value representing the Distribution Center, Date, Month, Week, Weekday, Day, and Volume for every day of the years 2006 and 2007. Excel 2007 needs that data when it creates the PivotTable so that it can maintain relationships among the data. If you want to filter your PivotTable so that it shows all package volumes on Thursdays in January, for example, Excel 2007 must be able to identify January 11 as a Thursday.

After you create a data table, you can click any cell in that list, display the Insert tab of the user interface, and then, in the Tables group, click PivotTable to display the Create PivotTable dialog box.

In this dialog box, you verify the data source for your PivotTable and whether you want to create a PivotTable on a new worksheet. After you click OK, Excel 2007 creates a new worksheet and displays the PivotTable Field List task pane.

To assign a field, or column in a data list, to an area of the PivotTable, drag the field head from the Choose Fields To Add To Report area at the top of the PivotTable Field List task pane to the Drag Fields Between Areas Below area at the bottom of the task pane. For example, if you drag the Volume field header to the Values area, the PivotTable displays the total of all entries in the Volume column.

If the PivotTable Field List task pane isn’t visible, click any cell in the PivotTable to display it. If you accidentally click the Close button at the upper-right corner of the PivotTable Field List task pane, you can redisplay the task pane by clicking any cell in the PivotTable to display the PivotTable Tools context tabs on the user interface. On the Options contextual tab, in the Show/Hide group, click Field List.

It’s important to note that the order in which you enter the fields in the Row Labels and Column Labels areas affects how Excel 2007 organizes the data in your PivotTable. As an example, the following graphic shows a PivotTable that groups the PivotTable rows by distribution center and then by month.

And here is the same PivotTable data, but this time it’s organized by month and then by distribution center.

In the preceding examples, all the field headers are in the Row Labels area. If you drag the Center header from the Row Labels area to the Column Labels area, the PivotTable reorganizes (pivots) its data to form this configuration.

To pivot a PivotTable, you drag a field header to a new position in the PivotTable Field List task pane. As you drag the task pane, Excel 2007 displays a blue line in the interior of the target area so you know where the field will appear when you release the left mouse button. If your data set is large or if you based your PivotTable on a data collection on another computer, it might take some time for Excel 2007 to reorganize the PivotTable after a pivot. You can have Excel 2007 delay redrawing the PivotTable by selecting the Defer Layout Update button at the lower-left corner of the PivotTable Field List task pane. When you’re ready for Excel 2007 to display the reorganized PivotTable, click Update.

If you expect your PivotTable source data to change, such as when you link to an external database that records shipments or labor hours, ensure that your PivotTable summarizes all the available data. To do that, you can refresh the PivotTable connection to its data source. If Excel 2007 detects new data in the source table, it updates the PivotTable contents accordingly. To refresh your PivotTable, click any cell in the PivotTable and then, on the Options contextual tab, in the Data group, click Refresh.

In this exercise, you create a PivotTable using data from a table, add fields to the PivotTable, and then pivot the PivotTable.

1. Click any cell in the data table.

2. On the Insert tab, in the Tables group, click PivotTable.

   The Create PivotTable dialog box appears.

3. Verify that the DailyVolumes table name appears in the Table/Range field and that the New Worksheet option is selected.

4. Click OK.

   Excel 2007 creates a PivotTable on a new worksheet.

5. In the PivotTable Field List task pane, drag the Center field header to the Row Labels area.

   Excel 2007 adds the Center field values to the PivotTable row area.

   

6. In the PivotTable Field List task pane, drag the Year field header to the Column Labels area.

   Excel 2007 adds the Year field values to the PivotTable column area.

7. In the PivotTable Field List task pane, drag the Volume field header to the Values area.

   Excel 2007 fills in the body of the PivotTable with the Volume field values.

8. In the PivotTable Field List task pane, in the Column Labels area, drag the Year field header to the Row Labels area, and drop it beneath the Center field header.

   Excel 2007 changes the PivotTable to reflect the new organization.