Working with different PivotTable formats in Office Excel

Because of the new features added to PivotTable and PivotChart reports in Microsoft Office Excel, there are now three PivotTable formats that you can work with: version, version 2002-2003, and version 2000. Furthermore, you can work with each PivotTable format in Microsoft Office Excel 2000, 2002, 2003, and. It's important to understand the dynamics of working with the different program versions and the different PivotTable format versions to decide whether you should upgrade right away to the new PivotTable format version.In this article

Working with different Excel program versions, Excel file format versions, and PivotTable versions

Feature differences between different PivotTable formats versions 2000 and 2002-2003 and version

Important considerations when deciding whether to upgrade a PivotTable format from versions 2000 and 2002-2003 to version

Working with different Excel program versions, Excel file format versions, and PivotTable versions

As you might expect, if you create a new PivotTable report in Office Excel, its PivotTable format is version. In Excel, when you open an Excel file format version 97-2003 that contains a PivotTable format version 2000 or 2002-2003, which puts you in Compatibility Mode, you can interact with the PivotTable without upgrading it right away to PivotTable format version.

You can also open an earlier version of the file format by opening an Excel 97-2003 file format in Excel version, by saving it back to the Excel 97-2003 file format, and then by opening it again in Excel version (also called roundtripping), and still preserve each PivotTable format of version 2000, 2002-2003, or. However, if you save a PivotTable format version in Excel file format 97-2003, the PivotTable is read-only, and you will not be able to create a PivotChart from this read-only PivotTable report.

When you use Excel version to upgrade PivotTable format version 2000 and 2002-2003 to version, you refresh the PivotTable report in Excel (On the PivotTable Options tab, in the Data group, click Refresh), and then save the file to PivotTable format version.

As you can see, the PivotTable format that you are actually working with depends on a number of factors, such as the current Excel file format, if you are in Compatibility Mode, and if you refresh the PivotTable. To summarize the most important points:

• In Excel, you can have fully interactive PivotTables in all formats of versions 2000, 2002-2003, and.
• In versions of Excel earlier than Excel, you can have a fully interactive PivotTable in version 2000 and 2002-2003 format, and a read-only PivotTable in version format.

The following tables list the possible interactions.

Using Excel

Using versions of Excel earlier than Excel

Note After you convert a PivotTable format from version 2000 and 2002-2003 to version, you cannot convert it back to version 2000 and 2002-2003 format.

Feature differences between different PivotTable formats versions 2000 and 2002-2003, and version

When you use a PivotTable format version in Excel, you can use all of the features of Excel. When you use a PivotTable format for versions 2000 and 2002-2003 in Excel, you can use most of the features of Excel, but there are some features that are not supported, including the following:

• Filtering labels and values (except by using a Top 10 filter, which are supported).
• Filtering fields not in the current PivotTable report layout from the PivotTable Field List.
• Hiding or unhiding intermediate levels of hierarchies in Online Analytical Processing (OLAP) data sources.
• Using manual, inclusive filtering (by clearing the Include new items in manual filter field check box in the Subtotals & Filters tab of the Field Settings dialog box).
• Exceeding new PivotTable limits, such as the new unique items per field limit of 1,048,576.

Although you can attempt to use these features, you may receive an alert asking you to refresh the PivotTable so that the PivotTable can be upgraded to PivotTable format version.

Important considerations when deciding whether to upgrade a PivotTable format from versions 2000 and 2002-2003 to version

Keep in mind the following important considerations when deciding whether to upgrade a PivotTable format from versions 2000 and 2002-2003 to version.

Sharing refreshable PivotTable reports PivotTable reports in format version will be read-only in earlier versions of Excel. If your users need to share the refreshable PivotTable reports that are connected to the same data source, you probably want to have those users consistently use Excel.

Using PivotTable reports in a mixed version environment If you need to create a PivotTable that can be used in versions of Excel earlier than Excel, then do not to save the workbook to an Excel version file format. This will ensure PivotTable compatibility across different versions of Excel with full interactivity.

Filter results may vary There are changes in the way that filtering works in a PivotTable format version. Filter criteria are additive, which means that each successive filter filters the current applied filter. In addition, the top filter items included in a Top 10 filter are now selected by dynamically evaluating their values in the context of where the field is placed in the PivotTable. In both cases, depending on the filter and data, you may see different filter results between PivotTable reports in format version and versions 2000 and 2002-2003.

See also:

• Use Office Excel with earlier versions of Excel