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


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:


Share your thoughts/feedback

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s