Problem: I created a pivot table in Excel 2010. I saved the file as an Excel 97-2003 file and sent it to a co-worker. When my co-worker opens the pivot table, it opens as static values in Excel 2003.
Strategy: You have to create the pivot table in compatibility mode if you want to share it with people who use previous versions of Excel.
When the Excel 2010 machine saved the file, the Compatibility Checker should have presented the warning "A PivotTable in this workbook is built in the current file format and will not work in earlier versions of Excel." However, this warning is buried among trivial warnings that some colors and styles aren't supported, so it is easy to miss.
If you need to use a pivot table in both Excel 2003 and Excel 2007/2010, you need to create the pivot table in Excel 2003 and save the file in 2003. You can then open and manipulate the file in 2007/2010 and save it back as an Excel 2003 file.
Alternatively, open the data set in Excel 2010. Save the data set as an Excel 97-2003 file type. Close the data set. Re-open the data set. You can now create the pivot table in compatibility mode.
Gotcha: When you create and save a file in Excel 2003, you won't be able to use the new Excel 2007 features, such as the new pivot table filtering or slicers.