In the office, Excel is a way of life… not just a tool


Replace IFERROR() with IF(ISERROR())

This tip relates to the technique of trapping and handling errors in a formula. Prior to Excel 2007 (e.g. Excel 2003, 2002, 2000), this was accomplished using a combination of the IF and ISERROR functions to test for an error in a formula, e.g. =IF(ISERROR(MyFormula),ReturnSomethingElse,MyFormula). This meant having to write your formula twice (think of your longest formula!).

In Excel 2007, trapping and handling errors in a formula was made easier with the introduction of the IFERROR function, which removed the need to specify your formula twice, e.g. =IFERROR(MyFormula,ReturnSomethingElse).

With organisations upgrading to Office (Excel) 2007 at different times, compatibility can become an issue. Although installing the Microsoft Office Compatibility Pack will enable users of previous versions of Excel to open an Excel 2007 file, you can't rely on this being installed. Also, you may want to convert an Excel 2007 file to a previous version for people to use / modify; In Excel 2007, simply 'saving as' an older version (e.g. saving an 'xlsx' as an 'xls' file) will often bring up a load of compatibility warning messages, which aren't fixed automatically.

Through my need to develop in all versions of Excel, I developed a handy procedure (ReplaceIFERRORCellFormulas) that will convert a range of selected cells that contain IFERROR formulas to instead use a combination of the IF and ISERROR functions. There's also a procedure to check all Named Ranges (ReplaceIFERRORNamedRangeFormulas), in case you use them to store formulas.

Version: 1.0
7.5 KiB
Comments (13) Trackbacks (2)
  1. you’re a lifesaver!

    thank you so much!!

  2. One major difference, IFERROR only returns a true value. IF(ISERROR) returns a true, false value which can be more helpful when you want to do something only if no error is present.

  3. Dude,
    Great job.
    It was really a wonderful time saver.

  4. Thanks a lot for the “little” modules. Really useful.



  5. Thank you so much.

    I have a huge Excel sheet where there are a couple of hundred different formulas included in the function if(iserror(someformula),0,someformula).

    Do you know of a macro to remove all if(iserror()) functions and keeping the formulas intact?

    Thank you for your help and advice.

  6. Thank you sooo much!!!

  7. Man…. I owe you a beer honestly……… you are the best!!!!!!!

  8. Most Helpful. Huge time saver. Thank you for building & sharing!!

  9. Thanks a million. saved me loads of time!

  10. Just what I needed to make my Excel 2010 file work correctly in Open Office. Excellent time-saver. Thanks!

  11. This is great! Thanks a ton

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.