How to Hide Errors From Worksheet Using Conditional Formatting

Posted: April 25, 2014 by MaxFraudy in Excel
Tags: , , , ,

UntitledToday, I’m sharing a basic but nonetheless a neat trick to make your reports more presentable. I’ll take an example to illustrate my point.

I have Life Expectancy data for various countries for years 2011 and 2012.  The fourth column is calculated using data in B & C columns. The number of rows of data can change, so I have put formula in extra rows. The problem then is that the rows that don’t have data return #DIV! error in column D as below.

Hiding Errors - Formula errorsThe calculated column D is protected but I don’t want my user to get a feeling that he’s done something wrong. What can I do to hide these errors?
One simple way to get rid of this problem is to use conditional formatting.

Go to Home tab -> Styles group -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to formatNew Rule

Type in this formula

  =ISERROR(D1)

and format it so that the font color is same is fill color.

How this works is very simple – The formula evaluates to TRUE whenever the cell formula returns an error, thus applying the formatting.

Download Hiding Errors from worksheet file that I have used.

 

You may also like:

Advertisements
Comments

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s