Hiding Error Messages – Quick Tip

Posted on October 28th, 2010 in Excel Howtos - 16 comments

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.

Page Setup - Sheet Options

How:

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

Written by Hui...
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

16 Responses to “Hiding Error Messages – Quick Tip”

  1. Sachin says:

    Its an awesome tip..I never knew this..thanks

  2. oldchippy says:

    Welcome Hui,

    Many thanks for this tip, hope to see many more in the weeks to come

  3. Chandoo says:

    Welcome Hui... I am really honored to have you as a guest author on this site. Thanks for starting with a quick and useful tip. We look forward to many more insightful articles from you 🙂

  4. Tom says:

    Cool tip, thanks! Some might find it useful to know that you can also write your formula to show either a blank or a #N/A not only when you print, but when you are viewing the sheet (after all, x/0 is not equal to 0).

    Blank = ISERROR(A1/A2,"")
    #NA = ISERROR(A1/A2,NA())

    Thanks for the post, Hui!

    Tom

  5. OmarF says:

    Of course, in 2007, I had to scramble to find the Page Setup menu!

    Go to Page Layout, and click on the little blemish to the right of the words "Page Setup", then go the the Sheets tab. Or just click on Print Titles. That will take you directly there.

  6. OmarF says:

    Oh yes, that is a cool tip. I had no idea that option was buried in there.

  7. Michael Pennington says:

    Great tip--I had no idea that was buried in there. Thanks!

  8. DV says:

    Vowww...U r too good HUI...hope to c so much more awesomeness...

  9. kJo says:

    This is gr8 Hui.. I look 4wrd to much more..

  10. Johnny says:

    This website is amazing.

  11. Twee says:

    Hi Hui,

    So glad you are posting for Chandoo. I've always appreciated your Ninja Excel skills on the forum so am glad you are posting more. Looking forward to more of your tips!

  12. Chris says:

    Thanks Hui, I never knew about that little trick either. I am also a big fan of =IFERROR()

  13. gerdami says:

    Related: PivotTables Options, For error values, show: "Ooops!"

  14. Chandoo says:

    @Gerdami.. Ooops! is a good trick 🙂

  15. Suki says:

    Hi Hui, We already know of your excellent Excel skills, and your persistence in helping, so good choice of guest author, Chandoo, and warm welcome, Hui. Look forward to your continuing unfoldment of Excel to all of us.

  16. ALI RAZA says:

    Fantastic and valuable tip

Leave a Reply