Creating Pivot Tables and Pivot Charts
The most sophisticated data-management feature in Excel is the pivot table, an organization and analysis tool that displays the fields and records in your list in new and potentially useful combinations. Pivot tables are made easy in Excel by a powerful wizard on the Data menu, and in Excel 2000 the wizard has been expanded to create colorful pivot charts: compelling graphical reports that display pivot table information visually. In this section, you'll learn how to create both pivot tables and pivot charts by using the PivotTable And PivotChart Wizard, and you'll learn how to connect to external data sources to feed this command in powerful new ways.
Using the PivotTable and PivotChart Wizard
The best way to learn about a pivot table is to create one. Fortunately, the PivotTable And PivotChart Wizard gives you complete control over the position of row and column headings in your table, so that you can rearrange all the important variables down the road. To create a pivot table, follow these steps:
- Click a cell in the list that you want to view as a pivot table.
- From the Data menu, choose PivotTable And PivotChart Report. The PivotTable And PivotChart Wizard starts and prompts you for the source of data for the table, as shown at the top of the next page.
NOTE
If the Office Assistant is enabled, you'll have an opportunity now to learn more about pivot tables and the various options you have when creating a database report. Feel free to seek guidance from the Office Assistant as you use the PivotTable And PivotChart Wizard. When you no longer need it, click the Excel Help button in the lower left-hand corner of the wizard dialog box to dismiss the Office Assistant.
In this example, you'll create a pivot table from a list in your worksheet. However, you can also create pivot tables from external data (such as records received by Microsoft Query), multiple consolidation ranges, or another pivot table or pivot chart. After you select a data source, Excel prompts you for a data range.
Don't worry about the dialog box getting in the way— Excel will minimize it when you start selecting cells, giving you a full-window look at your data. Our example screen looks like this:
In Excel 97, the PivotTable wizard displayed a layout grid at this point to help you build your pivot table, but this feature is now provided directly in the worksheet by means of an enhanced PivotTable toolbar. However, you can use the old pivot table "construction" dialog box if you like by clicking the Layout button in the third wizard screen (Step 3). In addition, you can use the Options button now or in the future to fine-tune how your pivot table or pivot chart appears.
Fields placed in the Row area will become rows in your pivot table, fields placed in the Column area will become columns, and fields placed in the Data area will be added together with the SUM function. You can arrange, or pivot, these values later, so don't worry too much about the final placement of fields now. (The Page area is reserved for fields that you want to take a closer look at.)
In the following illustration, the Month field has been placed in the Row area, the Sales Rep field has been placed in the Column area, and the Sale field is being selected on the PivotTable toolbar. (We're just starting to drag it to the Data area.)
After a field has been placed in the Data area, Excel finalizes the pivot table, as shown in Figure 22-9.
Figure 22-9. A pivot table allows you to rearrange the rows and columns in your worksheet, creating new views of your data.
Evaluating a Pivot Table
It might take you a moment to recognize the data in your pivot table, because it presents an entirely new view of your list. It's almost as if you had created new row and column headings, typed all the data again, and used the Subtotals command to summarize the results! However, you didn't have to rearrange your worksheet manually— the PivotTable And PivotChart Wizard did it for you. Best of all, you can easily transpose one or more fields and use new functions to highlight other trends in your list.
To help you work with the pivot table and create pivot charts, Excel displays the PivotTable toolbar, shown in Figure 22-10. You'll find this toolbar useful when evaluating and customizing your pivot tables and pivot charts. Take a moment to examine the buttons and commands on the PivotTable toolbar, and then read the summary data in your new pivot table, especially the Grand Total row and column.
Figure 22-10. The PivotTable toolbar contains several unique commands and buttons specifically designed for manipulating pivot tables.
Rearranging Fields in a Pivot Table
To rearrange, or pivot, the data in your pivot table, just click the fields in the table and move them to new locations. You can also remove unwanted fields by dragging them to the PivotTable toolbar and add new fields by dragging field names from the toolbar onto the pivot table.
TIP
As you edit or rearrange the data in the pivot table, note that your changes don't affect the data in your list (which is in its own worksheet)— your original rows and columns remain the same. However, if you change the cells in your list, you'll need to click the Refresh Data button on the PivotTable toolbar to see the changes.
Changing the Function in a Pivot Table
By default, the PivotTable And PivotChart Wizard uses the SUM function to add up values in the Data area of your pivot table, but you can easily change the function to calculate another value. For example, you could use the AVERAGE function to calculate the average sales in a month, or the COUNT function to total up the number of sales orders written by a particular employee. The list of functions available is identical to the set employed by the Subtotals command, described in Table 22-1.
To change the function used in a pivot table, follow these steps:
- Open the sheet containing your Excel pivot table, if it isn't already open.
- In the upper left corner of your pivot table (the cell above the Row field and to the left of the Column field), double-click the Data field name. In our example, the cell's location is A3 and contains the title Sum of Sale.
- In the Summarize By list box, select the new function that you want to use. (For example, select the MAX function to display the largest sales total in the field.)
You'll see the PivotTable Field dialog box, as shown in Figure 22-11.
Figure 22-11. Double-click the cell containing the current function and field to display the PivotTable Field dialog box.
TIP
You can also use the Field Dialog button on the PivotTable toolbar to display the PivotTable Field dialog box and change the function used to summarize a field in your pivot table. The PivotTable Field dialog box displays different options, depending on the type of entry selected.
Adjusting the Formatting in a Pivot Table
When you use the PivotTable And PivotChart Wizard to modify a pivot table, Excel automatically reformats the table to match the data in your list and to calculate the result of the function that you're using. Avoid making manual changes to the table formatting, because the AutoFormat table feature will overwrite them each time you rearrange the pivot table.
However, you can make lasting changes to the numeric formatting in the Data area by following these steps:
- Click any numeric data cell in the pivot table (not a row or column heading).
- Click the Field Settings button on the PivotTable toolbar. The PivotTable Field dialog box opens.
- Click the Number button. The familiar Format Cells dialog box appears, as shown in Figure 22-12. This dialog box allows you to adjust the formatting of the numbers in the Data area.
- Select a type of numeric format in the Category list box, and then select a formatting style. For example, to add currency formatting to numbers, click the Currency category, specify the number of decimal places you want, and specify a style for negative numbers.
- Click OK to close the Format Cells dialog box, and then click OK to close the PivotTable Field dialog box. Excel will change the numeric formatting in the table, and these changes will persist each time you modify the pivot table.
Figure 22-12. Use the Number button in the PivotTable Field dialog box to display a list of numeric formatting options for data in your pivot table.
TIP
Use AutoFormat for Fast Style Makeovers
To change the heading and line style, highlight a cell in the pivot table, and choose AutoFormat from the Format menu. Excel will display a list of table styles for you to choose from. Select the style you want, and then click OK to reformat and recalculate the pivot table.
Displaying Pivot Charts
If you find pivot tables addictive, you'll want to take advantage of Excel 2000's newest data analysis tool, the pivot chart. A pivot chart is a graphical version of an Excel pivot table. Pivot charts are created from existing pivot tables and are placed in new charting worksheets in the workbook. Like pivot tables, pivot charts have dynamic, customizable fields that you can drag back and forth to the PivotTable toolbar and move around the charting area. You can also modify the functions used to analyze data in a pivot chart.
To analyze an Excel list using a pivot table chart, follow these steps. (If you already have an existing pivot table in your workbook, click a cell in the pivot table, and start with step 4.)
- Click a cell in the list that you want to view as a pivot chart.
- From the Data menu, choose PivotTable And PivotChart Report.
TIP
Although the PivotTable And PivotChart Wizard gives you the option of creating a pivot chart in the first wizard step, you'll still be required to create a pivot table in the third wizard step to base the pivot chart on. For this reason, we recommend that you simply follow the default pivot table options when you create a pivot chart. (You won't lose any time doing so.)
