PivotTable Compatibility
Today Eric Patterson finishes his series of posts on compatibility.
For my last post about compatibility, I would like to drill down into more detail on Excel’s PivotTable feature. In Excel 2007, we have invested a lot of our time improving PivotTables. If you are just joining, you can find a number of blog posts describing that work here.
With all of the changes to PivotTables in Excel 12, it is important to understand how files containing PivotTables can be shared between versions of Excel.
PivotTable Versioning
First a little background information. PivotTables have a version property separate from the Excel version number that controls certain behaviors for compatibility with previous versions of Excel. New Excel 12 PivotTables will have a version of 12 (xlPivotTableVersion12). PivotTables created in previous versions will have an earlier version number, such as 10 (xlPivotTableVersion10) for Excel 2002 and 2003.
Compatibility Mode and PivotTables
In Excel 12, the PivotTable version is associated with Compatibility Mode. If your current workbook is in compatibility mode, new PivotTables created in that workbook will be version 10 PivotTables.
When your current workbook is not in Compatibility Mode, new PivotTables that you create will be version 12 PivotTables.
PivotTable Upgrade
When you save a workbook in Compatibility Mode to one of the new file formats, the PivotTables in that workbook will be marked for upgrade. When each PivotTable is next refreshed, it will be upgraded to a version 12 PivotTable and new functionality will be enabled for that PivotTable.
Version Specific Functionality
The version property is used to determine which functionality is disabled for PivotTables. The following new features are disabled for version 10 PivotTables:
- Hiding intermediate levels of hierarchies in OLAP data sources
- Label Filtering – For example: Show only product names that contain ‘Ab’
- Value Filtering (except top 10 filters, which are supported) – For example: Show only products that sold more than $10,000
- Manual inclusive filtering – New items are not included in the filter
- Key Performance Indicators – See PivotTables 11: Key Performance Indicators, Actions, and Named Sets
- Format Persistence – Version 12 OLAP PivotTables track customizations of items even when those items are not visible temporarily in the PivotTable.
- Increased Limit Support – See PivotTable limit changes described here.
Backwards Compatibility
Version 12 PivotTables are not “downgraded” to a version 10 PivotTable during save to a previous version file format or at any other time. When a version 12 PivotTable is viewed in previous versions of Excel, the PivotTable will not be refreshable. The PivotTable will continue to be refreshable in Excel 2007.
Strategies for sharing PivotTables with other users
As noted above, version 12 PivotTables are not downgraded to version 10 PivotTables and will not be refreshable in previous versions of Excel. If you wish to share PivotTables with people using a previous version of Excel AND they have a need to refresh the PivotTables, you will need to ensure that these PivotTables were created as version 10 PivotTables.
How do I create a version 10 PivotTable in Excel 12?
The simplest way to do this is by using compatibility Mode. If you start with a new file, save it to the Excel 97-2003 file format and re-open the file, you will enter compatibility Mode. Any PivotTable that you create while in compatibility mode will be a version 10 PivotTable and will be refreshable when opened in previous versions of Excel.
