XL: How PivotTables Differ from Version 4.0 Crosstab Tables

ID: Q104309

The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel versions 5.0 and later, you can use PivotTables to quickly summarize large amounts of data as well as analyze and display it using calculation methods and formats that you choose. In version 4.0, the Crosstab add-in provided similar functionality although it was less easy to use and edit, and it did not provide custom format options. In PivotTables you can "pivot" your row and column headings around the core data to get a different view of your source data.

While PivotTables provide more features, power, flexibility, and ease of use, there are some features available in crosstab tables that are not available in PivotTables. The following is a list of these features:

  • In a crosstab table, you can use the same field for both your row heading and your column heading. PivotTables will not use the same field for both headings; if you attempt to do this, the column heading will be ignored.


  • When a criteria range is defined on a worksheet, the crosstab table will include only the data that meets the criteria. A PivotTable will use all of the data in a list, ignoring any ranges named "criteria" or the result of an AutoFilter. To create a PivotTable from selected records in database, use the Advanced Filter to copy the result set to a different location and specify a criteria range. Then, create the PivotTable from the result. To do this, see the "More Information" section later in this article.


  • With the Crosstab command, you have the option to total all of the data in the database or total only the data in the crosstab table. Totals in PivotTables include all of the data in the PivotTable view.


  • When you create a crosstab table, an outline is created on the sheet so that you can display different levels of detail in your data. When you create a PivotTable, an outline is not automatically created and you won't be able to apply an outline to the table.



MORE INFORMATION

In Microsoft Excel version 5.0, you can use the PivotTable Wizard to create crosstab tables from Microsoft Excel data or external data. The PivotTable Wizard is a set of interactive dialog boxes that guide you through the steps of selecting a data source and choosing a layout for your table. Once you have created the table, called a "pivot table," you can customize it by moving categories, changing the calculation and formatting, and hiding and showing detail.

To start the PivotTable Wizard, choose the PivotTable command from the Data menu.

Converting Crosstab Tables to Pivot Tables

To convert existing Microsoft Excel version 4.0 crosstab tables to version 5.0 pivot tables:
  1. Open the worksheet containing the Microsoft Excel version 4.0 crosstab table in Microsoft Excel version 5.0.


  2. From the Data menu, choose PivotTable.


  3. Follow the instructions in the PivotTable Wizard for choosing a layout.


  4. When you are done, choose the Finish button.


NOTE: This conversion process permanently changes the crosstab table to a pivot table.

Other Differences Between Crosstab Tables and Pivot Tables

  • Totals in pivot tables include all the data in the pivot table view. With the Crosstab command, you have the option to total all the data in the database or total only the data in the crosstab table.

  • You cannot disable the double-click drill-down feature in a pivot table; when you double-click a cell in the table, the supporting data is displayed.

  • If you want your pivot table to analyze only data that meets certain complex criteria, you must extract that data from the list before you create the pivot table. In Microsoft Excel version 4.0, you don't need to extract data first; a crosstab table uses only the data that matches the specified criteria. To extract data in Microsoft Excel version 5.0:

    1. Set up the criteria that you want to base your extract on. For more information on setting up criteria, see pages 397-405 in the User's Guide.


    2. From the Data menu, choose Filter, and then choose Advanced Filter.


    3. Under Action, select the Copy To Another Location option.


    4. In the List Range box, enter the reference for the range containing the list.


    5. In the Criteria Range box, enter the reference for the range containing your criteria.


    6. In the Copy To box, enter the reference for the range containing your extract range and choose OK. Microsoft Excel copies only those rows that meet the criteria to the extract range


    7. To create a pivot table from the extract range, select a cell in the extract range, and choose PivotTable from the Data menu.



REFERENCES

For more information about creating and customizing pivot tables, see Chapters 24 and 25 in the User's Guide.

For more information about setting up criteria and extract ranges and using the Advanced Filter command, see Chapter 21 of the User's Guide.

Additional query words: 5.00a 5.00c 7.00a 97 98 XL98 XL97 XL7 XL5 pivot tables

Keywords :
Version : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,98
Platform : MACINTOSH WINDOWS
Issue type :


Last Reviewed: September 30, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.