fbpx

Hiding Error Messages – Quick Tip

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
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

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
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

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

100 Excel Formulas List

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

20 Excel Templates

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

Weighted average formula in excel

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

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