Pivot Table Compatibility Hint

Honestly, we love Excel '07 (Excel version 12). There's just one problem... compatibility.

First let me say the upgrades in '07 are amazing. Once you get used to the navigation changes (ribbon versus drop down menus) you will never go back. Unfortunately, 99% of our report users are still on Excel '03 (version 10) and compatibility is a big issue. We end up using version 12 to hold and maintain the data (the table functionality is fantastic and with the increased data capacity, we can load a whole lotta data in one workbook) and we use '03 (version 10) for reports. Took alot of research to find a solution so I thought I would share how we overcome our primary compatibility problem.

Pivot Tables: the basis of all our reports

If you create a pivot table in version 12, and even if you save as an "Excel 97-2003 Workbook", the pivot table functionality will not work in Excel 03 (version 10).

Here's how we overcome this:

1. Save data set in '07/version 12. (Recommend tabling the data set)
2. Open a new workbook
3. Save As "Excel 97-2003 Workbook"
4. Close the "Excel 97-2003 Workbook" you just created
5. Re-open the "Excel 97-2003 Workbook"
> You should now see "Compatibility Mode" in the workbook title bar.
6. Create your pivot table in the "Excel 97-2003 Workbook"
> You can use the data set or table in the '07(version 12) workbook.

By doing this, you are ensuring the original pivot table is created in the earlier version and in compatibility mode from the start, therefore enabling pivot table functionality in the '03 (version 10) file.

To help, we have a machine set up with Office '03 to test the reports before we send them out.

-Analytics