Using Pivot Tables In Excel 97

For support from Microsoft with Excel 97, go to the
Microsoft's Excel Support Page.
Search the Forestry Knowledge Base for Excel 97.

For Additional Help try:

Pivot Table document

Pivot Tables from Mathtools.net

 

 

Pivot tables provide a way to easily summarize, analyze, consolidate, filter, prepare data for charting, and report on large quantities of raw data in a flexible, ad hoc manner. They are called pivot tables because you can change their layout by rearranging, or pivoting, the row and column headings quickly and easily.

1. Create the data that will be summarized. Be sure each column (field) in the data has a column header, e.g., Date, Name, Mean.
2. If you select the first cell of the range before starting the wizard, Excel will automatically search down and to the right to find the range's extent and use that range in the pivot table. The easiest option is to click on the upper-leftmost cell of the range. Then, choose Data/Pivot Table. Excel displays a dialog for step 1 of the Pivot Table Wizard.
3. In step 1 of the Wizard, you specify the location of the source data. Assuming your data is in a single range of cells in Excel, choose Microsoft Excel List or Database. If you chose Microsoft Excel List or Database in step 1 then your data range must include only one set of columns. You'll choose Multiple Consolidation Ranges in Step 1 if your data is not contained in one set of columns. See below for more information.
4. In step 2 of the Wizard, you define the cells to be included in the pivot table's range. If you selected the upper-leftmost cell in the range, the Range text box will be filled in for you. If not, just click and drag in the spreadsheet to highlight the range (all cells) to be included.
5. Step 3 of the Wizard is where things happen. The data fields are represented by buttons at the right of the dialog box. Drag and drop the buttons to create the desired layout. If you place the wrong field onto the table you can remove it by dragging it anywhere outside the table. Row: contains the field(s) to be used as row titles
   Column: contains the field(s) to be used as column titles
   Page: used to filter the row and column data for a particular Page field value. A Page dropdown will contain all the unique values for the field(s) contained in Page.
   Data: at least one field must be placed in the data area. This will be the summary data that is reported inside the rows and columns of the pivot table. The same field can be placed in the data area more than once. Numeric fields will default to Sum and non-numeric fields will default to Count for the summary statistic. You can change the default summarization type by double-clicking on the button in the data area and selecting a different calculation type.
6. In step 4 of the Wizard you specify whether the pivot table is to be placed in a new worksheet or specify an upper-left corner for the pivot table to start in an existing worksheet.

Pivot tables do not automatically recalculate when the source data changes. To refresh a pivot table, select any cell inside the table, then choose Data | Refresh Data or right-click on any cell inside the table and choose Refresh Data.

1. Microsoft Excel List or Database: used when the data is contained in one Excel worksheet range
2. External Data Source: used when the data source is an external text file or database such as Microsoft Access, dBase, or Oracle Before you can create the pivot table, however, you must have installed Microsoft Query and the appropriate Open Database Connectivity (ODBC) drivers to read the external data. To install Microsoft Query or the required ODBC drivers you go through the Office Setup program. In Add/Remove click on Converters, Filters and Data Access, then click Change Option. Click on Data Access and click Change Option again. You'll find Microsoft Query and the ODBC drivers in the list.
3. Multiple Consolidation Ranges: used to consolidate data that is located in more than one worksheet range on the same sheet or different sheets Each of the ranges must be identical in structure with the same row and column names.
4. Another Pivot Table: used to create a pivot table using one that already exists in the same workbook, an important way to optimize memory for situations where you need to create several pivot tables using the same data set Both tables are linked to the same source data. Updating one pivot table causes the other to be updated as well.

You could right-click on any cell inside the table and choose to return to the Wizard... but most users prefer to change the table interactively using drag and drop techniques. You can change the layout by simply dragging the field buttons within the table to another part of the table. The best way to learn is through experimentation.

To remove a field from the table simply drag the field button outside the table range. When the large X appears release the mouse button.

Highlight the entire pivot table and choose Edit | Clear | All. This will delete the data and also the cell formatting and gridlines.

If you highlight the entire pivot table, including any page fields, and use Copy/Paste then you will copy the actual pivot table. Sometimes it's useful to Copy/Paste only selected cells in the table because the result will be normal cells rather than an actual pivot table.
If you aren't able to highlight only selected cells in the table, you may need to enable selection by right-clicking on a cell in the table and choosing Select | Enable Selection.

Right-click on any cell in the table and choose Options to access a dialog box of selectable options. As in any Office 97 dialog box, right-clicking on an option and choosing "What's This" will give you some explanation of the option. One option deserving a bit of explanation is "Save Data With Table Layout." This option determines whether or not the pivot table's data is stored with the spreadsheet. If you clear this check box and save the file, you'll have to refresh the data the next time you open the file. Although this requires an additional step, it helps keep the size of your spreadsheet files to a minimum.

The ability to select parts of a pivot table is dramatically affected by whether Selection is enabled or not. Selection is turned on and off by right-clicking on a cell in the table and choosing Select | Enable Selection. This is a global setting that affects your interactions with all pivot tables, not just the active one.

Field Names -- Changing the field name (even if it looks like a button) is as simple as clicking on the cell and typing in the new name you want to use. Unfortunately you need to remember that the name must be unique within the table AND within the row and column names in the underlying source data.
Orientation -- When multiple data fields appear in a pivot table they are arranged vertically by default. Like any other field button in the table you can use Drag and Drop to change the data fields to horizontal orientation.
Summarization -- To change the summary function used for a data field, right-click any cell in the data field, then choose Field. Select a summary function from the list and click OK.
Details -- You can double-click on any data cell in the table to view the detail behind the cell in a new worksheet in the current workbook.

Because the exact cells included in the Pivot Table will change as you interact with your Pivot Table, you shouldn't (although you could) directly format the cells using the techniques you would use with normal cells. Instead right-click on the field label in the table and choose Field... Click on the Number button to display the Format Cells dialog box. This will format the entire field rather than just specific cells.

Showing Pages -- You can use the Page drop down to view the pivot table page for each value in the page field. If you decide that you really want to create the individual pivot tables you can do it quickly by right-clicking a cell in the Table and choosing Show Pages... Be careful when showing pages for a field consisting of a large number of values since a new worksheet will be added to the active workbook for each value page.
Page Field Layout -- By default the page fields are stacked one on top of another. Right-clicking anywhere in the table and choosing Options will let you set the "Fields Per Column" and more esoteric the "Page Layout." Right-clicking the options and choosing What's This? can give you additional explanation of these options.

By default data is sorted in ascending order within a data label. Sort order can be changed by right-clicking on a data label and choosing Field, or by double-clicking on a data label. In the Field dialog box click Advanced to display the Advanced Options dialog box where you can select the field and sort order desired.

Grouping is useful when the data reported in the pivot table is too detailed for what you need. Maybe with too much detail the table shows all the days in each month or you want to report the data in histogram fashion using ranges. Right-click on the data label of the data you wish to group. Choose Group and Outline | Group (and note the Ungroup option).

There are two things to remember in dynamic charting. First, chart the entire data area of the table. Second, typically you should not include totals as they will cause scaling problems.

Right-click on any cell inside the data area and choose Insert | Calculated Field. When a field name needs to be inserted in your formula, you can select the desired field in the list (could be another calculated field), then click Insert Field.
Summarization Type -- Calculated fields SUM the underlying data and this cannot be changed.
Delete a Calculated Field -- Display the Insert | Calculated Fields dialog box. Select the field from the Name drop down list, then click Delete.
Modify a Calculated Field -- Display the Insert | Calculated Fields dialog box. Select the field from the Name drop down list. Modify the formula then click OK.