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.
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:
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.
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.
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.
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.
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:
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.
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.
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:
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.
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.
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.)
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.)
In a pivot chart, the legend represents the column field and the charting category represents the row field. To customize either of these values, right-click the associated field button in the chart, and select a command from the pop-up menu that appears.
Figure 22-13. Excel 2000 allows you to analyze your database lists with customizable pivot charts.
When you created your pivot table by using the PivotTable And PivotChart Wizard, you had the option of using external data as the source of your information. One method of extracting external data for your pivot table is by using Microsoft Query, a program shipped with Microsoft Office that you can use to connect to external data sources using a software driver called ODBC (Open Database Connectivity). Query acts as a link between Excel and database files that have diverse data formats, such as Access, FoxPro, SQL Server, dBASE, Paradox, and Btrieve. In Excel 2000, you can also use Query to access external data sources for your pivot tables known as OLAP cubes. OLAP stands for On-Line Analytical Processing, a format designed to consolidate massive amounts of corporate information using units that are sometimes referred to as data warehouses.
Query uses ODBC to translate complex data-filtering questions, or queries, into a language called SQL (structured query language). As a result, you can use Query to extract information about compatible database files in sophisticated ways. For example, your query might be, How many sales reps do we have who sell more than $20,000 in products per year and who work in the South or the Midwest?
When you want to work with external database files, consider using Query as a stand-alone tool or as a utility to import your data into Excel. Start Query in Excel by clicking the Data menu and picking a command from the Get External Data submenu. To use Query for accessing data in constructing a PivotTable, select External Data Source in Step 1 of the PivotTable And PivotChart Wizard, and then click Get Data in Step 2. (Query is an add-in program, so you'll have to install it using the Office Setup program before it can be accessed.)
For more information about using Query to manage external data sources, search for "Query (Microsoft)" in the Excel Help Index. You can also access a series of helpful cue cards from within the Query Help menu.