INTERACTIVE WORKSHEET TABLES USING EXCEL

A review by Paul D. Warner

A PivotTable is an interactive worksheet table that quickly summarizes large amounts of data using your own format and calculation methods. It is called a PivotTable because you can rotate its row and column headings around the core data area to give different views of the same data. As source data changes, you can update a PivotTable. Because it resides on a worksheet, you can integrate a PivotTable into a larger worksheet model using standard formulas.

A PivotTable provides an easy way to display and analyze summary information about data already created in Microsoft Excel or imported from another application. For example, with a Microsoft Excel database that contains sales records for several salespeople, you can create a PivotTable that organizes and totals sales data using categories (i.e., fields) such as year or product. The only requirement is that the field exist in the template.

A PivotTable summarizes data using calculations or summary functions such as Sum or Average. Controls are provided on how subtotals and grand totals are calculated.

PivotTable Data

Data for use in a PivotTable can come from‹

* a single Microsoft Excel list, database, or any worksheet range that has labeled columns.

* a collection of Microsoft Excel ranges that contain data to be consolidated. (These ranges must have both labeled rows and columns.)

* a database file or table created in an external application or database management system such as Microsoft Access, FoxPro, dBASE, ORACLE, or SQL Server.

* data from an existing PivotTable or a Microsoft Excel version 4.0 crosstab table.

How a PivotTable Works

Because a PivotTable is created from existing data, knowing the organization of the source data and how it is used in a PivotTable is important. Table 1 is used for the examples that follow.

Fields and Items Control How Data Is Organized

You specify what data you want to include and how you want to organize it by choosing from the source, the fields, and items that are to appear in the table. A field (i.e., column label) is a category of data, such as product, year, or sales. An item is a subcategory in a field, such as dairy, produce, etc. in the product field, or 1992 and 1993 in the year field.

There are two types of fields:

* Row, column, and page labels in the PivotTable are fields that usually contain a limited set of text values, such as East, West, etc. (Page fields break the PivotTable into separate pages so the data can be seen one item at a time.)

* Data fields contain the data to be summarized. These fields typically contain numeric data, such as sales amounts, inventory totals, or statistical data, but can also contain text.

There is no limit on the number of fields that can be used.

Excel contains a "Wizard" that is used to generate the PivotTable. (Wizards are accessed through the Data menu.) The Wizard requires the following four steps to generate a PivotTable:

1. Select the source.

* Microsoft Excel or database

* External data source

* Multiple consolidation ranges (i.e., multiple ranges from the spreadsheet)

* Another PivotTable

2. Select the range (do not include empty cells outside the data).

3. Select the columns and/or rows.

4. Choose the final options.

* Starting cell for the table

* Table name

* Grand totals for columns and/or rows

* Saving data with the table layout

* Auto formatting the table

Items from the source list become row or column labels in the resulting PivotTable. These labels, or PivotTable items, are subcategories of a field in a PivotTable, just as they are subcategories of the field in the source table.

The selection above produced Table 2and the chart in Table 4.

In addition to Sum, the Wizard provides other functions, such as Count, Average, Maximum, etc. Data can also be shown as the percentage of a field or a value within the field, as differences from, etc. When one of these options is selected, the Wizard presents a screen containing the field and items within the field that can be selected as the base for the comparisons. Full field formatting is also provided for.

In the example in Table 3, the produce and dairy items that appear in the product field of the source table appear as items under the column field product in the PivotTable. Notice the difference‹product now appears as a field within year, which, in turn, appears within salesperson. This was achieved by simply moving the field names into the row section of the Wizard in the order desired.

Why Do They Call It a PivotTable?

If you look at the Wizard step 3 above, you will see why. To exchange the column and row fields (i.e., pivot them), you merely have to change the location of the field names from the row to the column or visa versa. If we wanted the preceding table to show how the sales were by region within year, we would merely move the field labels. The results are shown in Table 5. The underlying data is not affected.

Other features include the ability to suppress data (e.g., eliminate a region) and to change the table by moving fields and associated data with the mouse. Excel for Windows 95 provides for the generation of PivotTables by geographical locations through the use of its built-in mapping capability. Microsoft claims a PivotTable in Excel for Windows 95 requires 50% less memory than Excel 5.0. *

TABLE 2

Editors:
Paul D. Warner, PhD, CPA
Hofstra University

L. Murphy Smith, DBA, CPA
Texas A&M University

DECEMBER 1995 / THE CPA JOURNAL