Hiding Error Messages – Quick Tip

Hello all

Chandoo has graciously, some may say stupidly, given me access to post on Chandoo.org.

I have been a reader of Chandoo.org for about 2 years and have spent most of my time contributing to the Forums where I have just posted my 950th post.

I have written a few small posts which Chandoo has used, and I wrote a major post on Monte Carlo Simulation and Data Tables:

http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/

Which was well recieved.

I will be starting to post about once per week and will be introducing a series of real life problems and how they can be tackled using Excel.

Hiding Error Messages

I like to leave certain error messages in place because they can show you what your data is doing, but they look horrible when you print out reports.

One way around this is to use functions like =Iserr or =Iserror to trap the error and display something else

Eg: A formula =A1/A2 will divide A1 by A2 and give you an answer,

but if A2 is 0 you will get a Divide Zero Error #DIV/0!

To fix that you can use the =IFERROR Function =IFERROR(A1/A2,0) which will now give you a zero if A2 is zero

But if you don’t mind seeing, or want to see, the errors on screen, but don’t want to print them out you can have Excel hide the error messages at Print time.

How:

Goto the Page Setup menu
On the Sheet Tab use the Cell errors as:
and select <Blank>, “–“ or #N/A as appropriate