Working with different PivotTable formats in Office Excel

Because of the new features added to PivotTable and PivotChart reports in Microsoft Office Excel 2007, there are now three PivotTable formats that you can work with: version 2007, version 2002-2003, and version 2000. Furthermore, you can work with each PivotTable format in Microsoft Office Excel 2000, 2002, 2003, and 2007. 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 2007.

In this article


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 2007, its PivotTable format is version 2007. In Excel 2007, 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 2007.

You can also open an earlier version of the file format by opening an Excel 97-2003 file format in Excel version 2007, by saving it back to the Excel 97-2003 file format, and then by opening it again in Excel version 2007 (also called roundtripping), and still preserve each PivotTable format of version 2000, 2002-2003, or 2007. However, if you save a PivotTable format version 2007 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 2007 to upgrade PivotTable format version 2000 and 2002-2003 to version 2007, you refresh the PivotTable report in Excel 2007 (On the PivotTable Options tab, in the Data group, click Refresh), and then save the file to PivotTable format version 2007.

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 2007, you can have fully interactive PivotTables in all formats of versions 2000, 2002-2003, and 2007.
  • In versions of Excel earlier than Excel 2007, you can have a fully interactive PivotTable in version 2000 and 2002-2003 format, and a read-only PivotTable in version 2007 format.

The following tables list the possible interactions.

Using Excel 2007    

Opened file format version Initial PivotTable format version Refreshed? Saved file format version Saved PivotTable format version PivotTable functionality
2007 2007 NA 2007 2007 Fully interactive
2007 2000, 2002-2003 Yes 97-2003 2000, 2002-2003 Fully interactive
2007 2000, 2002-2003 No 97-2003 2000, 2002-2003 Fully interactive
97-2003 2000, 2002-2003 Yes 2007 2007 Fully interactive
97-2003 2000, 2002-2003 No 2007 2000, 2002-2003 Fully interactive
97-2003 2007 Yes 2007 2007 Fully interactive
97-2003 2007 No 2007 2007 Fully interactive
97-2003 2000, 2002-2003 Yes 97-2003 2000, 2002-2003 Fully interactive
97-2003 2000, 2002-2003 No 97-2003 2000, 2002-2003 Fully interactive

Using versions of Excel earlier than Excel 2007    

Opened file format version Initial PivotTable format version Refreshed? Saved file format version Saved PivotTable format version PivotTable functionality
97-2003 2007 NA 97-2003 2007 Read-only
97-2003 2000, 2002-2003 NA 97-2003 2000, 2002-2003 Fully interactive
2007 2007 NA 97-2003 2007 Read-only
2007 2000, 2002-2003 NA 97-2003 2000, 2002-2003 Fully interactive

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

Top of Page Top of Page

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

When you use a PivotTable format version 2007 in Excel 2007, you can use all of the features of Excel 2007. When you use a PivotTable format for versions 2000 and 2002-2003 in Excel 2007, you can use most of the features of Excel 2007, 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 2007.

Top of Page Top of Page

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

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

Sharing refreshable PivotTable reports     PivotTable reports in format version 2007 will be read-only in earlier versions of Excel 2007. 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 2007.

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 2007, then do not to save the workbook to an Excel version 2007 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 2007. 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 2007 and versions 2000 and 2002-2003.

Top of Page Top of Page

 
 
Applies to:
Excel 2007