How are you currently dealing with compatibility issues of Excel 2003 / 2007?

Fimodo speaks to five financial modelling experts to understand how they have (and continue to) deal with compatability issues.

rickard-warnelid

Rickard Warnelid
Corality
www.corality.com

The issue of non-compatibility of Excel 2003 / 2007 is not generally a problem internally within financial modelling teams, unless of course different team members have different versions. It is only first when external communication using the financial model or Excel spreadsheet is required that the problem crystallises.

Dual installations of Excel 2003 and Excel 2007 to solve compatibility issues

At Corality’s financial model audit team we are currently using the work-around of dual installations, i.e. all financial modelling team members run both versions. In theory you can get around the issue using the Excel Compatibility Pack but the technical issues with that solution have to many drawbacks to make it feasible

  • Frequent corruption of Excel workbooks when converting between versions
  • Potential loss of data, logic and functionality when converting from Excel 2007 to Excel 2007
  • Graphical misrepresentation between the versions, i.e. a financial modeller may build something that is looking fantastic in Excel 2007 but after conversion looks pretty average.
  • Direct lack of compatibility like in the example of Conditional Formatting developed in Excel 2003 does not updating correctly in Excel 2007. How Microsoft stuffed this one up is quite surprising as it looks like a pretty straight-forward conversion to me!

Lower productivity in Excel 2007 using the Ribbon

For people who like keyboard shortcuts the Ribbon creates a hurdle to overcome before a financial modeller can get back to full development speed after transitioning to Excel 2007. One of the more common frustrations is that Excel 2007 does not have the native functionality to add a drop-down in the Menus (because there are no ‘menus’….) to quickly access Excel Styles using [Alt + ’].

Fortunately one of our Analysts, Bing Chien, is an absolute Excel/VBA/XML wizard so he has coded an internal Add-in which does adds a quicker access shortcut to the Styles functionality. Using the Ribbon to access Styles is just to slow and the advantage of Styles is significantly diminished without this functionality.

Does Corality build new financial models in Excel 2003 or Excel 2007

When developing new financial models for clients there is no consistently right answer to which version to use. At Corality’s financial modelling team we currently use the approach to develop new financial models in the Excel version used by the client. This way the risk of corrupted workbooks and lost data is minimized. Unfortunately many companies have not performed a consistent across-the-board update to Excel 2007 but have upgraded on desktop/laptop at the time which further complicates the issue. Often we see the Managing Director or the CFO have brand new sleek laptops with Excel 2007, and everyone else on Excel 2003… Which Excel version should you choose then for a new financial model!?

nick-crawley

Nick Crawley
Managing Director, Navigator Project Finance
www.navigatorpf.com

We’ve been here before…

This is indeed an issue, but it’s not a new one. We have had the same issues when financial models were just as likely to be built in Lotus 1-2-3 and needed to be opened in Excel (and vice versa) – this was a much bigger issue than the one we currently are facing. We saw the same issue when Excel transitioned from 95 to 2000. The latest issue is only temporary and is being navigated in the same way – this time I am finding that companies are adopting 2007 much more readily. I think this is because it was marketed better and as a quantum shift rather than an upgrade…it is also now 2010 so its not really that new anymore.

The best approach?

We need to think about the user and other likely users not us as model builders. The big issues are that Excel 2007 has been prettied up and so if the enhanced formatting is used or the extra columns / rows then there will be compatibility issues – so if you are building in 2007 but it is not 100% certain that a user will have no choice but to open in 2003 at some stage then don’t use the additional features – keeping it simple and having a ‘back to basics’ approach – which is good anyway do mitigate this issue and mean we have very few issues with 2007.

Day to day I find that most issues are solved simply by asking some obvious questions:

  • What do you (the client) use, 2003 or 2007?
  • Will anybody need the model that has not got access to 2007?
  • Do you need any of the enhanced features? (my guess is probably not)
  • Do we need the additional computational ability of 2007 (increasingly so actually)

There are various compatibility packs and guides available online but my best advice is if you are unsure then don’t use 2007 just yet. If you are faced with a model that requires more brute force than 2003 can deliver then use 2007 after speaking to the likely users. The additional power was a pleasant surprise, it allows us to model more flexible (but still simple) systems and do more in a single workbook before getting the subtle but important ‘calculate’ message which is not allowed in Navigator models.

This is currently working very well for us and I suspect by mid 2010 enough people would have migrated that it won’t be an issue – just in time for the next version!

john-stroud

John Stroud
Partner, Digital Advisory
www.digitadvisory.com

Usually I find swearing at the screen and stomping around the office helps.

I work with a duel load of Excel, developing in 2003 then testing in 2007. The main issues are now well documented and can be avoided during development. I think it’s important to know what compatibility is required before development commences as this can save time (& $’s). When issues do crop up there is usually a work around. However when it comes to manipulating the UI (user interface), 2003 and 2007 are completely different, thanks MS! The setting up of custom toolbars requires coding in XML rather than VBA which takes a bit of getting used to.

I agree with the consensus that the UI or Ribbon in 2007 is a less efficient way to achieve the same results. It can be customised or rolled back to a 2003 setup but a personal bug bear is the lack of floating toolbars.

Despite the lambasting Excel 2007 has received, under the hood it’s definitely a better product. By utilising more memory (1Gb to 2Gb), having an unlimited dependency and multithreading calculations, the calculations speeds of large models are significantly improved.

simon-selkrig

Simon Selkrig
Financial Modelling Professional

It’s a matter of adjusting and using the ’07 version through practice, practice, practice

Still adjusting my own customised Excel functions and macros in the ’07 version.

Overall ’07 is much better and has many great improvements, but obviously the menu ribbon is a great challenge to adjust to, and I know many people don’t like it at all.

When I have a problem, I google useful Excel sites like MrExcel.com to overcome these issues

One major issue I encountered in ’07 was that certain Excel add-ins created in Excel 03 require the VBA converters .dll files (VBACV10.dll & VBACV10D.dll) which must be copied into a users’ following location: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6

I have been using it for more than 2 year, although there have been some improvements, I had bad experiences with saving financial models as Excel ’07 Macro-enabled formatted files, which crashed and corrupted complex Excel financial models I was working on. Further you can’t assume if everyone is using ’07, hence it is always wiser to save files as ’03 compatible.

danielle

Danielle Stein Fairhurst
Principal Consultant, Plum Solutions
www.plumsolutions.com.au

The upgrade to Excel 2007 was probably one of the biggest things to happen to my world in a long time! In fact, clients often ask me about the benefits of upgrading to Excel 2007 and whether they should bother yet. The uptake amongst businesses has been painfully slow, and it causes quite a lot of hassle, especially as many organisations seem to be upgrading their users gradually, rather than all at once. This means they have users on different versions which causes a bit of confusion, and many people don’t seem to realise the basic rules of dealing with two different versions. (i.e. If you want a 2003 user to be able to use your model, save it as a .xls file, don’t use new formulas such as SUMIFS, IFERROR etc, don’t use new colours in macros and so on.)

I think any good modeller should be “bi-lingual” in terms of which version they use. I run both, although my preference is 2007, mostly because of the increased capacity, handy new formulas, and improved graphics. I’ve checked out the beta version of Excel 2010 and there are some nice improvements, such as being able to customise your own ribbons which is great and will improve productivity. The File menu has also made a comeback in place of the Office button. Considering the uptake of 2007 was so slow, I find it difficult to imagine that many clients will be using 2010 in the near future. Of course some people will simply leap-frog 2007 and go straight to 2010.

When I run training courses in Excel, I give participants the choice of whether they’d like to use Excel 2003 or 2007 during the course and all of the written materials contain instructions for both versions. I find that it’s a mix of about half and half usually, although I do encourage those who’ve never used Excel 2007 before to give it a go. The first time a heavy-duty Excel user tries out 2007, they invariably find it incredibly slow and frustrating, so I recommend that they test it out in a training environment as this will help them later on when / if they do upgrade. Probably one of the most frustrating things though is when I show people some of the new tools, like Data Bars in Conditional Formatting, Remove Duplicates, etc. they won’t be able to use them when they get back to work unless they upgrade!

Recent posts by Fimodo

Comments for “How are you currently dealing with compatibility issues of Excel 2003 / 2007?”

  1. Jon Peltier says:

    I use 2003 for my own use and for development of workbooks and coded solutions for clients. Naturally many clients have now downgraded to 2007, so I test everything in 2007, and make the requisite adjustments prior to delivery.

    Many coded solutions should actually be opened, saved, and closed in Excel 2000 to ensure compatibility, because a number of recent upgrades to 2003 and 2007 seem to cause issues with each other.

    I keep 2007 on a separate machine (virtualization is a godsend) to avoid cross talk between versions.

  2. Luke says:

    I work in a US investment bank and the IT department doens’t allow installations of both Excel 2003 and 2007 so I am stuck with Excel 2007 for all financial modelling tasks. The amount of frustration this is causing is unbelievable and my life is jam-packed with corrupted workbooks and confused and irritated clients. If only they would let me, and everyone else involved in financial modelling, go back to Excel 2003!!

  3. Gavin Townshend says:

    On several occassion I have seen the compatability problem of formulae using Analysis Toolpak functions being converted to #REFs (or is it #NA?).

    I heard a suggestion for a potential solution to this problem, so now I am trying to reproduce this problem in a brand new spreadsheet with only one or two test formulae. But so far I can’t reproduce the problem, so can’t test the potential solution.

    Can anyone help me reproduce the problem in a newly created simple spreadsheet (rather than discuss potential solutions or workarounds)?

  4. Adrian Miric says:

    With regards the =#n/a issue it is a serious issue with regards spreadsheets built to be used in Excel 2003 and Excel 2007. Unfortunately it is not possible to replicate it, so presumably this is why it still exists.

    We have tried to explain it at http://www.auditexcel.co.za/NA-Error-Excel-2003-to-Excel-2007.html but all comments and feedback welcome.We believe this could cause serious loss of money at certain clients.

  5. John Graham says:

    I develop models using Excel 2003 and test them in Excel 2004 & Excel 2007 (but not Excel 2008 since it does not have VBA).

    The critical compatibility issues we have run into are:
    (1) loss of functions in the Analysis Toolpak
    (2) having too many formats

    Work arounds we use to avoid the issues:
    (1) a VBA module to implement the Toolpak functions as user defined functions, eg globally replace EOMONTH by EOMONTH_vba, EDATE by EDATE_vba, etc.
    (2) systematic removal of styles & conditional formatting

  6. J.Makinen says:

    Guite simple rule works around with this annoying compatibility issue.

    If you use 2007, then save the files always in 2007 format, not in 2003 (unless you need to downgrade it to previous Excel vrs. Should this be required, just work in ‘2007’ format until you need to e.g. send to someone else who you does not have 2007). This may sound quite naive advice, but I’ve tried so many different ways to sort this out and none of them has provided 100% working solution except this very simple principle.

    The compability problem can happen even with very simple spreadsheets (replacing ‘toolpak’ function with #NAs, lost of data etc). Sometimes just only thing to trigger the problem is to open the file and resave it with other name (why this should cause an issue, I don’t have a clue).

    The compatibility is the only major problem with Excel 2007 as far as I’m aware.

Comment on this Article