There is a division in the office between the have’s and the have nots. Those that have taken the brave leap into the unknown and upgraded to Excel 2007 (or even Excel 2010) and those that are resolutely sticking to the old stalwart, Excel 2003.
It’s a nuisance. Perfectly decent spreadsheets are incapacitated when you either save as an Excel 97-2003 workbook or open them in the earlier version. Pivot tables are ruined, formatting goes to pot and any spreadsheet protection you’ve carefully thought out is ignored.
It’s Agreement of Balances time in the NHS at the moment, which means multiple accountants are sharing and working on the same excel spreadsheets. Compatibility issues within the office have caused problems and when you think that the output needs to be shared with other NHS organisations with their own versions of Excel it becomes vital to understand how to create a pivot table in Excel 2007 that someone can use in Excel 2003.
Saving a spreadsheet as Excel 97-2003 will not save your pivot tables[/caption]
If you want your pivot table to work when somebody opens your spreadsheet in an earlier version of Excel you will need to create it in an earlier version of Excel, or at least you will have to force Excel 2007 or 2010 to work in compatibility mode. It is not sufficient to simply save your Excel workbook as an earlier version.
If you open Excel 2007 (or 2010) and create a new spreadsheet you can choose file, save-as, and create an Excel 97-2003 workbook version. If you then create a pivot table in this workbook and re-save you will be rewarded with a compatibility error message, informing you that your carefully constructed pivot table will be useless if you persist on saving it in the antiquated version you’ve selected.
If you search around the help files or internet for a solution you’ll find that you need to create the old version while in compatibility mode. It is not altogether clear how to go out about creating a new spreadsheet in compatibility mode.
How Do You Force Microsoft Excel 2007 or 2010 to Work in Compatibility Mode?
Recognising when Excel 2007 has opened in compatibility mode[/caption]
If you open an old excel spreadsheet in either Excel 2007 or Excel 2010 the new version will automatically default to compatibility mode.
Compatibility mode is effectively Excel 2003 in disguise and once you’ve got your version of Excel in this state you can create pivot tables safe in the knowledge that they will work in the earlier version.
You can recognise compatibility mode by the [square brackets] that appear after your file name in the workbook header. If you don’t see this, you aren’t in compatibility mode so don’t bother creating your pivot table yet.
So to open Excel 2007 in compatibility mode you need to open an earlier version of an excel spreadsheet. If you don’t have one to hand, do the following:
- File - New - Create Blank Workbook
- File - Save As - Excel 97-2003 Workbook. I suggest calling it “blank 2003 workbook”.
- Although you now have a blank Excel 97-2003 workbook you are not yet in compatibility mode.
- Close the new workbook
- Re-open the blank Excel 97-2003 workbook. Tiresome I know.
- You have now forced Excel 2007 to open in compatibility mode!
You can either keep the blank Excel 97-2003 workbook handy for future use or you could save it as a 97-2003 template so that you can easily create new workbooks in compatibility mode.