 Number 195 - August 1999 |
| Using Pivot Tables In EXCEL - A brief Tutorial | |
| by Bob Russell, (c) Microsoft, Inc 1998 | |
|
Microsoft Excel is the
centerpiece of the tools that are bundled with the Microsoft Office
suite. Excel is the analytical tool, Access is the database tool, Word
is the word processing tool, and Power Point is the presentation tool.
[Excel is what we used to call a Spreadsheet -rjt]
A major feature of all these tools is their ability to import data from external databases and accounting systems, and to exchange data among the tools using relatively simple programming. Since Excel is the analytical one, it follows that Excel has some powerful number crunching and summarizing capabilities. The Pivot Table is one of the major features, in that it provides both number crunching and summarization. Pivot Table Only Seems Complicated We find that few people use the Pivot Table because it is unfamiliar and seems complicated until you have used it a few times. By setting up a custom Pivot Table, you can change the data in its source range and have an automatic summary of whatever data you have entered in the source range. A checkbook chart comes to mind. The following is extracted from all article in the Microsoft Knowledge Base. Microsoft Excel 97 introduces two new features for customizing PivotTables: calculated fields and calculated items. This article describes the function of each feature and provides examples for using the features in Pivot Tables. Calculated Fields A Calculated Field is a user-defined field in a PivotTable that can perform calculations using the contents of other fields in the PivotTable. Calculated field formulas can refer to one or more fields. For example, a calculated field named "Profit" that you define as: =Sales-Expense refers to two fields in the PivotTable named "Sales" and "Expense." [Field names are in the first row of the table containing the data under consideration.] The following example uses a calculated field in a PivotTable. This example subtracts the two fields Sales and Expense to give a subtotal for the new field named "Profit." 1. In a new workbook type the following data:
2. Select cell A1. On the Data menu, click Pivot-Table Report. In step 1 of the wizard, click Microsoft Excel list or database and click Next. 3. In step 2 of the wizard, click Next for the Range $A$1:$D$13. In step 3 of the wizard, drag the field Month to the COLUMN area. Drag the field Region to the ROW area and drag the fields Sales and Expenses to the DATA area. Click Finish. The PivotTable appears on a new worksheet and resembles the following table. Month Grand4. After the PivotTable appears, click any item under Region. On the PivotTable toolbar, click Pivot-Table, point to Formulas and click Calculated Field. 5. In the "Insert Calculated Item in 'Region'" dialog box, in the Name box, type "Profit" (without the quotation marks). 6. In the Formula box, type: "=Sales-Expenses" (without the quotation marks) and click Ok. You may alternately select the field name in the Fields box. To insert the selected field in the formula, click Insert Field. The PivotTable displays the calculated field Profit as a new field under Data. This PivotTable resembles the following table. Month Grand |
Calculated Items
A calculated item is a user-defined item in a Pivot-Table field that can perform calculations using the contents of other fields and items in the Pivot-Table. Calculated item formulas can each include only items from the field in which you create the calculated item. For example, you can define a calculated item named "NorthWest" as "--North+West" in the field named "Region." The following example uses a calculated item in a PivotTable. This example totals two items, North and West, in the Region field to give a total for the new region named "NorthWest." 1. In a new workbook type the following data: 2. Select cell A1. On the Data menu, click Pivot-Table Report. In step 1 of the wizard, click Microsoft Excel list or database and click Next. 3. In step 2 of the wizard, click Next for the Range $A$1:$C$13. In step 3 of the wizard, drag the field Month to the COLUMN area. Drag the field Region to the ROW area and drag the field Sales to the DATA area. Click Finish. The Pivot-Table should appear on a new worksheet and resembles the following table: Sum of Sales Month Grand4. After the PivotTable appears, click any item under Region. On the PivotTable toolbar, click Pivot-Table, point to Formulas, and click Calculated Item. 5. In the "Insert Calculated Item log box, in the Name box, type "NorthWest" (without quotation marks). 6. In the Formula box, type (without quotation marks): "=North+West" and click OK. You may alternately select the items for each field by clicking the field name in the Fields box, and then clicking the corresponding item for that field in the Items box. To insert the selected item into the formula, click Insert Item. The PivotTable displays the calculated item as a new region. The table resembles the following table. Sum of Sales Month GrandREFERENCES For more information about performing calculations in PivotTables, click the Index tab in Microsoft Excel 97 Help, type the following text pivottables, calculated fields or pivottables, calculated items and then double-click the selected text to go to the "Create a calculated field in a PivotTable" or "Create a calculated item in a PivotTable" topic. As noted above, once the data have been summarized and the resultant table is displayed in its own worksheet or embedded on the same sheet as the source data, it is easy to use the Chart Wizard to build a chart showing the data. Comparing a pair of lines on a chart is easier than comparing pairs of numbers, and it is much easier to perceive a trend in a chart than it is in a set of numbers. If you are tracking data for your company or for your household relative to a budget, a chart can be very helpful. The source data can be updated periodically and reflected in the chart, with no more pivot table manipulation necessary. If you have a question about Microsoft Office applications you would like to see addressed here, e-mail your question to questions@microsoft.com |
|
Number 195 - August 1999 |
|