Analyzing Data
Creating PivotTables
The procedures used in Excel 95 and 97 for creating PivotTable reports have been enhanced in Excel 2000. In previous versions of Excel, you had to determine the placement of the fields while working in the PivotTable wizard and changing the configuration was less flexible. In Excel 2000, when you choose the PivotTable command (Data, PivotTable and PivotChart Report), you start a quick three-step wizard. In step one, you identify the source of the data to be used in the PivotTable (see Figure 3.24). In step two, you indicate the range of cells to be included in the PivotTable. In the final step of the wizard, you indicate where you want the PivotTable placed: embedded in an existing worksheet (in the active workbook or any other open workbook), or in a new worksheet (see Figure 3.25).
){kind=link}
){kind=link}
After you have identified the destination for the PivotTable, select Finish. The PivotTable layout is displayed along with the PivotTable toolbar, as shown in Figure 3.26. The column headings in your Excel list become 1 fields, and appear as part of the PivotTable toolbar. You drag the field names from the toolbar to any of the four layout areas. You do not have to use all four areas, but you must use the Data area and at least one other area.
){kind=link}
-
Row—Drag the field containing the data you want to become the row labels in the PivotTable to the Row area. You can place more than one field in the row area.
-
Column—Drag the field containing the data you want to become the column labels in the PivotTable to the Column area. You can place more than one field in the column area.
-
Data—Drag the numeric field containing the data you want to summarize in the PivotTable to the Data area.
-
Page—When you place a field in this area, it acts as a filter on the PivotTable.
TIP
Generally, it is best to place the field that has the fewest unique entries in the column area and the field that has the most unique entries in the row area. The exception to this is entry length. If the field has very long entries, they should be placed in the row area, where you can see more of them at one time.
FIG. 3.24 First step of the PivotTable Wizard.
FIG. 3.25 Second step of the PivotTable wizard.
FIG. 3.26 The PivotTable layout is composed of four parts: row, column, data, and page.
PivotTable Enhancements
Many of the improvements to the PivotTable feature can be accessed through the PivotTable button 1 on the PivotTable toolbar (shown in Figure 3.27). Many of these enhancements are in the Options dialog box (shown in Figure 3.28). The PivotTable enhancements include
){kind=link}
){kind=link}
-
Selecting part of a PivotTable—You can select parts of the PivotTable structure to format or use in formulas using the PivotTable, Select command 2.
-
Calculated fields and items—Creating calculated fields, and items within fields, is accomplished by choosing the PivotTable, Formulas command 3.
-
Automatic sorting—Items in a PivotTable can be sorted automatically, even when the PivotTable layout is changed. Choose PivotTable, Field Settings 4 and click the Advanced button to locate the sorting options.
-
Page field retrieval options—When the PivotTable is extracting data from a large external database, you can extract each item in a page field as you display that item. Choose PivotTable, Field Settings 4 and click the Advanced button to locate the retrieval options.
-
Formatting—When you apply formatting and AutoFormats to selected parts of a PivotTable, the formatting remains even after you refresh the data or change the PivotTable layout by using the Preserve Formatting option. Choose PivotTable, Table Options 5 to access the Preserve Formatting option.
-
Error values or empty cells—When a PivotTable generates an error value or an empty cell, you can tell Excel to display something else in the PivotTable. For example, if an error value, such as #VALUE or #REF, is generated, you can have a blank or the word "Error" displayed. If an empty cell is generated, you can have a zero (0) displayed instead. The Error value and empty cell options can be accessed by choosing PivotTable, Table Options 5.
-
Running background queries—You can run a query on an external database in the background so that you can continue working in Excel while the data is being retrieved for a PivotTable. The Background Queries option can be accessed by choosing PivotTable, Table Options 5.
-
Using PivotTable data in formulas—The new GETPIVOTDATA worksheet function enables you to create formulas that perform calculations using data in PivotTables.
FIG. 3.27 The PivotTable toolbar.
FIG. 3.28 The PivotTable Options dialog box.
Introducing PivotCharts
Corresponding graphic charts, called PivotCharts, have been added to the PivotTable report feature in Excel 2000. These charts are tied to the PivotTable data and refresh when the items summarized by the PivotTable (or the data) changes.
To create a PivotChart from an existing PivotTable, select a cell in the PivotTable and click the Chart Wizard button on the PivotTable toolbar. PivotCharts are displayed on their own chart sheet.
To create the PivotTable and PivotChart at the same time, select a cell anywhere in your list and choose Data, PivotTable and PivotChart Report to begin the PivotTable Wizard. On the first step of the wizard, choose PivotChart as the item you want to create. PivotCharts are always placed on a new worksheet 1. In the third step of the wizard (see Figure 3.29), you can designate the location of the corresponding PivotTable. The PivotChart layout displays in the new worksheet, along with the PivotTable toolbar 2 (see Figure 3.30).
){kind=link}
){kind=link}
As with a PivotTable, you drag the field names from the toolbar to any of the four layout areas in the PivotChart:
-
Category—The data in the field you drag to the Category area becomes the labels along the x-axis of the chart.
-
Series—The data in the field you drag to the Series area becomes the plotted groups in the chart and the labels in the legend.
-
Data—The numerical data in the field you drag to the Data area is plotted (grouped by series) in the chart.
-
Page—Although you don't have to place a field in this area, it acts as a filter on the PivotChart when you do.
In Figure 3.31, the Salesrep field has been placed in the Category area and the Year field in the Series Area. The Sales field is being summed in the Data area. The Customer field is in the Page area.
){kind=link}
FIG. 3.29 Your only choice in the final step of the wizard is the placement of the corresponding PivotTable.
FIG. 3.30 The PivotChart layout is composed of four parts or "drop-zones": category, series, data, and page
FIG. 3.31 The PivotChart summary label indicates the calculation being performed in the PivotChart.
- Page field label
- PivotChart summary label
- Series field label
- Category field label