SHOPPING CART0 item(s)$0.00 AUD + GST

Should you Upgrade to Excel 2007?

19 November, 2009

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.

Comments

First, thanks for the good coverage.

One contradicting comment:

The ribbon style of menus are presented in the writing like this style is better than the previous one with respect to productivity. I don't believe that this is true! The previous one was MUCH more customizable. Actually ribbon is not customizable at all, you have to memorise all the placings of the buttons. You can not even customize the sizes of the buttons in the ribbon, you have to use them as they were optimized for the "average" user by Microsoft.

Regards

- Ahmet

Submitted by Ahmet Kara (not verified) on Thu, 19/11/2009 - 19:47.

I've been working with 2007 commercially since the end of March and have had it installed on my own machines since it was released and I still hate the front end, but Microsoft are not going back on this.  In 2010 the ribbons are still there so using that as a basis not to upgrade is not an option.  Not unless you are happy with using 2003 forever!  There are however some really useful features - many of which you have already mentioned.  SUMIFS and AVERAGEIFS are particularly useful.  SUMIF allowed you to build a sum based on a single criteria while SUMIFS and the rest of the IFS formulas allow you to use multiple criteria.  So while SUMIF could easily give you the total sales for Region A, it couldn't easily give you the sales for region A, Person 1, Day 1.  This gets around the need for some horrible array formulas.

We have had a number of issues with the styles and random formatting issues which have caused us a number of problems - one of which we have Microsoft working on a fix for.

Submitted by Tony English (not verified) on Thu, 19/11/2009 - 19:52.

Thanks for your comments, guys.

@Ahmet, I do agree with you on the ribbons.  I do find that I end up using the mouse more with 2007 than the old version, which is not good ergonomically.  The quick access Toolbar is handy - but again - accessible by the mouse.

@Tony, Thanks, your comments are useful for those trying to make the decision on whether to upgrade.  Yes, SUMIFS in particular, is one of my favourite 2007 formulas as it gets rid of the need for messy work arounds.

Submitted by danielle on Thu, 19/11/2009 - 20:51.

Many thanks Danielle for the comparative analysis. I've just upgraded mine and reading this was very useful!

Submitted by Luis Filipe Pereira (not verified) on Thu, 19/11/2009 - 21:33.

About customizing the ribbon: the ribbon is not customizable , yes.

But, you can - for sure and I do it - customize the little bar above all,  the so called quick acess toolbar.
I think this is a great way to make the transition while you get used to the new huge toolbars...

I mean, you put there the buttons you use the most and later you get used to the other buttons positions.
Other great tip would be the right-click.

But, in general, I consider 2 points about 2007: a) one day we'll have to use it; b) it has much more data capacity and many more functions to help the user. The problem is the big change in user interface.

Submitted by Geraldo Pires de Alvarenga (not verified) on Thu, 19/11/2009 - 22:27.

Hi Danielle,

Just to let you know the function EOMONTH is actually available in 2003. Funnily enough it is actually one of the formulas where have arisen when created in 2003 and subsequently viewed in 2007. A similar compatibilty issue can also occur with the NETWORKDAYS function.

Thanks

Submitted by Mark H (not verified) on Thu, 19/11/2009 - 22:42.

1. The front end is so much harder to use productively. You cannot readily customize the ribbon. The ribbon displays many fewer buttons at a time. The button you want is usually on a tab that isn't active, so you have to hunt for it. Many of the new dialogs were not designed with productivity in mind. The result is many more miles of moust travel each day, and hundreds more mouse clicks.

2. Charting is not more professional than before, just the default colors are less dismal. It's harder to find what you need, and slower to edit and format the charts (see point 1). In fact, all of the fancy colors and effects make it easier than ever to make a bad chart.

3. The only colleague of mine who likes the ribbon doesn't use Excel in battle anymore, he just writes books about it. None of my clients were pleased with the switch. One even called me, irate about the lack of usability. A few have reinstalled 2003.

It can be said that I'm so familiar with the old version that I can't see the benefits of the new. Not so. I really tried to like it, I started using it in 2005, and I have to use it frequently now to support my clients. I have spent a lot of effort characterizing the interface to design customizations (which are next to impossible in the user interface) and to find workarounds for cumbersome interactions.

Of course, the ribbon is here to stay. We'll have to adapt, and learn new productivity tricks, or rely on third party developers to come up with add-ins that bring back some of the old usability.

Excel 2010 looks promising. It still uses the ribbon, of course, and most of the inefficient dialogs have not been changed. But they have enabled customizations of the ribbon by the user;they have worked hard to make charts nearly as fast to update as in 2003 (2007 had severe performance problems if you chart had more than a few hundred points); and they have added chart recording functionality back into the macro recorder, which 2007 had lost.

If you're thinking of updating, wait a few months until Office 2010 is available. In fact, wait a year, and they'll have Office 2010 SP1 available, which will have ironed out early release bugs.

Submitted by Jon Peltier (not verified) on Thu, 19/11/2009 - 23:22.

Interesting article.

For all of you interested in "mastering" the Excel 2007 Ribbon,

just have a look at http://www.simple-excel.com/excel-ribbons.htm

Submitted by Herve Hanuise (not verified) on Fri, 20/11/2009 - 04:04.

Ribbon and productivity:

this all depends on the level of the user. If you are new to Excel it is more productive (and promotes use of features because the wording of items as opposed to icons makes people remember about things they have heard of). Someone who learns now and carries on will probably be more or as productive up to intermediate level

Where it falls down is for intermediate to advanced users and those who already know the old interface well.I agree this needs addressing (eg the ability to add a couple of personal ribbons that are more customisable).

(based on training and supporting 80+ Excel users)

It is generally easier to use. Having written 3 applications myself using Ribbon design, these were able to be released with a lot less guidance and documentation that previously so I believe it certainly does benefit novices.

 

Other benefits:

File size. When working with very large files (including macros etc) the files are a lot smaller in the the new format (and are still compatible with XP and 2003 if users get the free add-in.

On multi-core machines it calculates much faster, especially for those like me that use Quad core - as I work with large complex data in Excel I reckon this is saving me around 5hrs per month (previous versions only calculated on one processor).

Deduplication (mentioned in original article) is great and I think not promoted enough - the abilit to delete but also to conditionally format. Also saving me hours per year.

Down sides:

Ribbon for advanced users.

The charting is not always better, wrapping of axis labels is not nearly as good and we have had problems with going back to manually reformatting charts previously produced with automation and correct straight away.

Loss of the colour pallet, bittersweet in that can produce more colour but also have to enter RGB values all the time (we have a specific corporate branding that fixes the RGB values we must use).

Compatibility - although this is education mainly, we have had very few issue with this.

A few other minor issues like when copying to word as a graphic the grid lines appear now (not borders but the normal grid) which they did not before and we now have to remember to turn off each time, but not really a major issue.

 

 

Submitted by Carl P (not verified) on Fri, 20/11/2009 - 04:44.

To Ahmet's comment...your ship has come in with Office 2010, my friend...you can customize buttons and toolbars in Office 2010, just like we could in 2003 and previous.

Carl P, I agree with about 99% of what you said...very well put. I use Excel 2007 on an i7 processor, and it FLIES. It's fantastic.

Customizing colors in 2007 I find easier, because I prefer the precision of entering in RGB values...I guess that makes me a geek. (Oh, yeah, right, that and the Microsoft certifications...never mind.)

Last but not least, I have to use this opportunity to expose John Peltier for what he is:
A LEGEND. One of the greatest who ever lived in Excel. You need chart macros, he's the guy. Listen to what he says.

Submitted by Geoff Lilley (not verified) on Fri, 20/11/2009 - 09:23.

@Mark H Thanks, yes you are right!

You also might want to check out the Point Haired Dilbert's review of 2010 Beta (released the other day) at http://chandoo.org/wp/2009/11/18/download-excel-2010-beta/

Submitted by danielle on Sat, 21/11/2009 - 15:17.

SUMIFS, COUNTIFS, AVERAGEIFS - Reason enough to upgrade!!

Submitted by John McCarthy (not verified) on Tue, 24/11/2009 - 19:52.

My workplace upgraded to Office 2007 a year or two ago, and I'd say that I only know where to look for 50-75% of my usual tools, and I know I'm missing other improvements. The problem is, this is the "kitchen sink" that's being thrown at us, and as Excel has gotten updated, it's gotten more complicated. One thing I do like about Office 2007 is the ability to customize what's above the ribbon. For instance, I frequently close one workbook to open another, and it's nice to have something that makes sense to click on vs. an X in the upper right corner. On the other hand, at home I still have the 2003 version. I've only remotely thought about updating it, but since I heard that Office 2010 is on the way, I'm reluctant to. I have noticed that the file sizes are smaller, but capacity is not a serious issue for me yet. I'll have to check out the SUMIFs that were mentioned!

Submitted by Brian Doennebrink (not verified) on Thu, 26/11/2009 - 14:57.

Post new comment

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.