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
16 Responses to “Hiding Error Messages – Quick Tip”
Its an awesome tip..I never knew this..thanks
Welcome Hui,
Many thanks for this tip, hope to see many more in the weeks to come
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 🙂
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
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.
Oh yes, that is a cool tip. I had no idea that option was buried in there.
Great tip--I had no idea that was buried in there. Thanks!
Vowww...U r too good HUI...hope to c so much more awesomeness...
This is gr8 Hui.. I look 4wrd to much more..
This website is amazing.
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!
Thanks Hui, I never knew about that little trick either. I am also a big fan of =IFERROR()
Related: PivotTables Options, For error values, show: "Ooops!"
@Gerdami.. Ooops! is a good trick 🙂
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.
Fantastic and valuable tip