Creating Dynamic Worksheets by Using PivotTables in Microsoft Excel 2010

Chapter at a Glance

In this chapter, you will learn how to

• Analyzing Data Dynamically by Using PivotTables.

• Filtering, Showing, and Hiding PivotTable Data.

• Editing PivotTables.

• Formatting PivotTables.

• Creating PivotTables from External Data.

When you create Microsoft Excel 2010 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 Excel worksheet is that you can’t easily 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 change the worksheet quickly so that it displays only sales on Tuesdays during the afternoon.

There is an Excel tool with which you can 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, focus your PivotTable data using filters and Slicers, format PivotTables, and create a PivotTable with data imported from a text file.

Analyzing Data Dynamically by Using PivotTables

With Excel worksheets you can gather and present important data, but the standard worksheet can’t be changed from its original configuration easily. As an example, consider a worksheet that 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 and each column represents 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 this worksheet, you can’t easily reorganize the contents of your worksheet so that the months are assigned to the rows and the distribution centers are assigned to the columns.

The Excel 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 described 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 the distribution centers. The new layout emphasizes the totals by regional distribution center.

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

In the Excel table used to create the distribution PivotTable, each row of the table contains a value representing the distribution center, date, month, week, weekday, day, and volume for every day of the years 2009 and 2010.

Excel 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 must be able to identify January 11 as a Thursday.

After you create an Excel table, you can click any cell in the table, display the Insert tab and then, in the Tables group, click PivotTable to open 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 or an existing worksheet. After you click OK, Excel displays a new or existing worksheet and displays the PivotTable Field List task pane.

If the PivotTable Field List task pane isn’t visible, you can display it by clicking any cell in the PivotTable to display the PivotTable Tools contextual tabs. On the Options contextual tab, in the Show/Hide group, click Field List.

To assign a field, or column of data, to an area of the PivotTable, drag the field header 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.

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 organizes the data in your PivotTable. As an example, consider a PivotTable that groups the PivotTable rows by distribution center and then by month.

The same PivotTable data could also be 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 a different configuration.

To pivot a PivotTable, you drag a field header to a new position in the PivotTable Field List task pane. As you drag a field within the task pane, Excel 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 to reorganize the PivotTable after a pivot. You can have Excel delay redrawing the PivotTable by selecting the Defer Layout Update check box in the lower-left corner of the PivotTable Field List task pane. When you’re ready for Excel 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, you should ensure that your PivotTable summarizes all the available data. To do that, you can refresh the PivotTable connection to its data source. If Excel 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’ll create a PivotTable by using data from a table, add fields to the PivotTable, and then pivot the PivotTable.

Set Up

You need the Creating_start workbook located in your Chapter09 practice file folder to complete this exercise. Start Excel, open the Creating_start workbook, and save it as Creating. Then follow the steps.

1. Click any cell in the Excel table.

2. On the Insert tab, in the Tables group, click the PivotTable button (not the arrow).

   

   The Create PivotTable dialog box opens.

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 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 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 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 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 changes the PivotTable to reflect the new organization.

   

Clean Up

Save the Creating workbook, and then close it.