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.

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.

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.

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.