Microsoft Office Tutorials and References
In Depth Information
Chapter 17: Working with Pivot Tables
Pivot table compatibility
If you plan to share a workbook that contains a pivot table with users of previous versions of
Excel, you need to pay careful attention to compatibility. If you look at the recorded macro in
the “Creating a pivot table” section, you see the following statement:
If your workbook is in compatibility mode, the recorded statement is:
You’ll also find that the recorded code is completely different because Microsoft has made
significant changes in pivot tables beginning with Excel 2007.
Assume that you create a pivot table in Excel 2010 and give the workbook to a coworker who
has Excel 2003. The coworker will see the pivot table, but it will not be refreshable. In other
words, it’s just a dead table of numbers.
To create a backward compatible pivot table in Excel 2010, you must save your file in XLS
format and then re-open it. After doing so, pivot tables that you create will work with versions prior
to Excel 2007. But, of course, you won’t be able to take advantage of all the new pivot table
features introduced in Excel 2007 and Excel 2010.
Fortunately, Excel’s Compatibility Checker will alert you regarding this type of compatibility
issue (see the accompanying figure). However, it won’t check your pivot table–related macros
The macros in this chapter do not generate backward compatible pivot tables.