Migrating to Excel 2010
A large company just asked us what advice we had for migrating from Excel 2007 to 2010. I sent them back the following; it's worth bearing in mind that I constantly refer to Excel 2003 and its xls files. That's because there are precious few changes between 2007/2010 and the client was more concerned with a general migration towards more modern functionality
Anyway, it's standard advice so I thought I'd share it here...
Our standard guidance for users upgrading from Excel 2007 to 2010 is that there are very few differences between the two versions of Excel and that there will be no compatibility issues between them. There are a few bits of new functionality for Excel 2010 but it is likely such things would only be used by expert users who would be more than capable of adapting their work for users on older versions of Excel. I believe the most significant new functionality that you cannot replicate in earlier versions would be PowerPivot tables that can be created in Excel 2010 with an add-in from Microsoft. These allow you to create pivot tables that store data far more efficiently and may be useful for big data analysis projects; but they would never be used by general users as they require the separate add-in.
In terms of migrating existing “xls” spreadsheets to the new “xlsm”/”xlsx” formats; I would suggest there is very little benefit. If spreadsheets were usable with 97-03 functionality, they are unlikely to require the additional functionality that new versions of Excel afford. You may see a reduction in file size by migrating to the new formats but it may then encourage users to add functions that are incompatible with earlier versions of Excel.
When saving xls spreadsheets in Excel 2010, you will see a list of compatibility issues. Unfortunately, new Excel versions use a much more extensive colour palette than earlier versions so any coloured cells will be picked up as a compatibility issue. This may make users ignore the compatibility messages as they will nearly always appear, and 90% of the time they will not contain critical information.
The most common issues with compatibility will be where pivot tables are created in Excel 2010, they are unlikely to be backward compatible. Also, if buttons/checkboxes are added to 2010 sheets, they will need to be ActiveX controls as the more customisable image controls available in 2007/2010 are not backward compatible. Bear in mind, these should be issues you have already encountered in using Excel 2007; I don’t believe a further upgrade will create further issues.
Finally, any existing embedded macros should be able to run with minimal adaptation. Generally adaptation would only be necessary where e.g. you are referring to other files, at which point the change in file extension can have consequences. If people are developing macros by using the record feature, they should be aware that certain recorded code e.g. for sorting, is not backward compatible; and they’d have to look up the 2003 code which is forward compatible.
These are really minor points. Microsoft realise that customers can use versions of Excel for a decade or more so try to make different versions as compatible as possible. Having produced spreadsheets that financial institutions send to their clients, there is evolving a culture of “if we can’t make this 2003 compatible then, as long as this is an addition to client service, we can tell them to upgrade”. I would recommend beginning to phase out 97-03 support for new spreadsheets once you have upgraded, and seeking out help where you have problems with existing spreadsheets.
At Excel4Business, we run all versions of Microsoft Office back to 2000 so would gladly assist in this process, as and when you require assistance
