The upgrade from Excel 2003 to Excel 2007 is probably one of the most significant changes for Excel users yet. There are many new functions, most of which are an improvement to 2003 but they do take some getting used to! As a financial modelling consultant and trainer, I’m often asked by my clients what are the advantages and disadvantage of upgrading to Excel 2007 and whether organisations should even bother.
Major changes between versions
The biggest change is the new “Ribbon” format replacing the toolbars. It is now more task-orientated and things are easier to find so you’ll probably find many features you never knew existed! It will take some time get up to speed, however, and you will probably lose some productivity at first.
- The office assistant paper clip is gone for good!
- The colours and charting are much more professional looking in 2007 (see below for options)
- Themes and styles have much more functionality.
- Live Preview – when you move the mouse over the various formatting selections, your document takes on the formatting attributes of the current selection — just as a preview. So you can flit from one choice to another and try different formatting without actually applying it until you’re ready.
- Conditional formatting is no longer limited to three conditions, and easier to use.
- It is possible to add comments to named ranges.
- The formula bar is resizable so you can write even longer formulas (not always a good thing!)
- As with 2003, you can edit named ranges – but the tool for this is now called the Name Manager. Except for the new name, it’s pretty much the same as it was before, although you can now delete multiple names at once and assign comments to each name.
- The size of each worksheet has expanded from 65,000 to over 1 million rows!
- It now remembers up to fifty recently used files instead of nine.
- There are more options in the right-click menu.
- Sort and Filter are now together, so you can sort from the filter drop-down list.
- Sorting limits have increased from three to 64 levels and you can even sort by colour! (This feature is activated when at least one cell contains a fill or font colour)
- It has a new file format which facilitates integration with external data sources, and also offers reduced file sizes and improved data recovery. In Excel 2007, the default format for an Excel workbook is the XML-based file format (.xlsx). If the file contains macros, the format will need to be .xlsm.
- “On demand” Ribbons which only appear when required for less used tools like pivot tables, charts etc. Although many new users hate the Ribbon, the “on demand” feature is much better than the old Excel 2003 which clutter up your screen, are sometimes not loaded when you open Excel and remain floating after you don’t need them any more.
- Pivot tables and charting do not use wizards any more, and are completely different.
Completely New in 2007
- Many new functions, including: IFERROR, SUMIFS, COUNTIFS, AVERAGEIFS, XNPV, XIRR
- Data bars in conditional formatting will show dynamic bar relative to the size of the value in the cell.
- Remove duplicates tool (very useful!)
- There are many more new features – feel free to add your favourites below!
Hot Tip: Press the ALT key, and the ribbon shortcut keys will appear as shown below:
Really hating Excel 2007 but stuck with it?
You can also purchase add-in software which installs the Classic menu for Excel 2007, which looks like this:
Excel 2007 Compatibility
Until everyone has upgraded to the new version, compatibility will remain the biggest challenge for Excel 2007 users, especially if others need to open the model in 2003. There are a number of new functions in 2007 which are not available in 2003. If you are the only person using 2007 and everyone in your office or your clients are still on 2003, you will have to make sure you do not use the new functions otherwise you will have compatibility issues. Note that all the great graphics and colours still look fine when you open the model in 2003, as long as you do not use a formula not supported in 2003.
A compatibility checker tells you if your workbook contains features that previous versions of Excel will not support. But be careful – you will need to remember to save a document in Excel 2003 format to maintain compatibility with other users until the new 2007 file format becomes the standard. While Microsoft has released a converter to read 2007 files in earlier versions, do not rely on other users to have it installed. I’ve also heard reports of the converter not working properly, or corrupting some files. If you use new functions not available in 2003, the compatibility checker will keep advising you to save a copy in 2003 – which gets rather annoying after a while.
So, Should you Upgrade?
The million dollar question! According to a recent poll, in May 2009, 80% of Excel users were still using Excel 2003 or earlier! If you plan to look for work soon and you wonder which version of Excel future employers will want to see on your resume, you probably need to learn both versions. Having familiarity with both versions will certainly improve your marketability. For this reason, all Plum Solutions training course participants have the option to use either version of Excel during the course, and course materials are provided with instructions in both versions.
Reasons to Upgrade
- 2003 is out of date: The old Office platform was built on what are now antiquated technologies – Office 2007 (apparently) represents a clean break in platform design.
- It costs the same (if you are choosing between versions).
- New features such as new functions and increased capacity.
If neither of reasons is compelling enough for you, you may as well wait for the next generation. Office 2010 is expected to be released in the first half of 2010. (It still has the ribbon layout of course.) In my opinion the worst thing you can do is upgrade half of your team and leave the rest on the old version. This causes confusion, compatibility problems and can mean that you often end up with two versions of many files.
If you’re trying to decide whether your company should upgrade to Excel 2007, there isn’t any real harm in waiting. The main reason many companies upgrade is because they are working with clients or external parties using 2007, and it’s just easier if you are using the same version. They often also upgrade to obtain some of the new features, in particularly the improved file format which has much greater capacity.
