If you've been using Excel 2003 for several months or years, you will probably have recently become aware of increasing numbers of people or organisations using Excel 2007. You may even have been sent Excel 2007 workbooks that you can't open with your own version of Excel. Whatever your current awareness of Excel 2007, it is likely that you will, at some point in the future, consider taking the plunge and switching to Excel 2007.
But what are the main benefits of switching, and how much effort will be involved?
One of the main developments in Excel 2007 is that it allows more columns and rows. For many users this may not be an issue - after all, the 65,536 rows and 256 columns provided by Excel 2003 allows you to handle a large amount of data. However, it isn't so unusual for users to want to handle HUGE amounts of data! Therefore Excel 2007's ability to handle 1,048,576 rows and 16,384 columns of data is a great advantage for some users.
Another major improvement in Excel 2007 is Conditional formatting. I have frequently been asked, by colleagues using Excel 2003, about applying more than 3 conditional formats. Up until recently, I have always had to explain that this is a limitation of Excel 2003, but now, in Excel 2007, a user can specify as many conditions as they like, each with a different format.
Conditional formatting in Excel 2007 can even be made to work for cells that satisfy more than one condition. For example, if you specify cells having values ≤ 10 to have bold text and cells having values ≥ 10 to have red text, you will find that text in cells containing values exactly equal to 10 will be formatted as bold AND red.
Also, Excel 2007 offers additional types of conditional formatting. If your bosses (like mine) like to see figures illustrated with pretty charts and lots of colour, they will love the spreadsheets you produce with Excel's new Data Bars, Color Scales & Icon Sets! These features apply colour or symbols to a range of cells, depending upon each cell's value in relation to the rest of the cells in the selected range. As an example, the image on the right shows conditional formatting Data Bars, Color Scales & Icon Sets, applied to 3 columns, each containing the numbers 1 - 10.
As a final bonus, Excel 2007 also now enables conditional formatting to be used with pivot tables.
At first, the interface to Excel 2007 conditional formatting can appear to be a bit complicated, but the time spent familiarising yourself with this functionality is well worth the investment!
In Excel 2003, Filtering and Sorting could be performed by cell values only. However, in Excel 2007, you can now filter and sort by colour. This can be either the font colour or the cell background colour.
In my opinion, one of the most useful improvements in Excel 2007 is the level of nesting that can be performed. Excel 2003 only allows 7 levels of nesting of functions, while Excel 2007 allows up to 64. Surely, even the most dedicated Excel user cannot possibly need more nesting levels than that!
In Excel 2003, if a cell contained a long formula or text string, when the cell was selected, the view of the formula bar would block some of your spreadsheet. This was slightly inconvenient. However, Microsoft have addressed this problem in Excel 2007, by introducing a resizeable formula bar, which extends in line with your cell contents. This is shown in the image above.
Excel 2007 contains a number of new built-in functions to help you to make your spreadsheet slicker and more efficient. These include the IFERROR Function, the SUMIFS function, and the new statistical AVERAGEIF, AVERAGEIFS, and COUNTIFS functions.
My work has frequently been slowed down, when applying functions to large datasets in Excel 2003. I have often pasted a formula down every row of a spreadsheet, and then had to sit back and wait for Excel to calculate the formula results. However, this has improved in in Excel 2007, as memory management has been increased from 1 gigabyte to 2 gigabytes and complex calculations are now faster.
There have been some issues with the updating of charts in Excel 2007, which is slower than in Excel 2003. Also, the recording of macros involving charts sometimes doesn't work! For me personally, this isn't a great issue. However, if you frequently perform chart updates, or like to record macros involving charts, you should test drive Excel 2007 before committing to it. A free trial can be downloaded from the Microsoft Office Online website (opens in a new window).
The main user interface has been restructured in Excel 2007, into what Microsoft describe as a "results-oriented" interface. However, if you have been using Excel 2003 for several years, and know exactly where to find the tools and functionality that you regularly use, there will almost certainly be some frustration to begin with. My advice is ... Persevere! ... it really is worth the effort!