fbpx
Search
Close this search box.

Hiding Error Messages – Quick Tip

Share

Facebook
Twitter
LinkedIn

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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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