Conditional Formatting Vs Formulas - Preference?

[Shiremaid]

Hope I'm posting this in the right forum...

I am creating a database and I want cells that are picking up information from other cells to be "invisible" when they are empty (i.e. don't want "0" or any of the formula errors to be visible to users) without using Macros or Groups to hide rows (SharePoint doesn't like Macros and Protected Sheets don't like Groups)

I know two ways of doing this:
Conditional Formatting: If the record ID cell is blank, make all text in that section White.
Formulas: Use IF and IFERROR formulas to leave the cell blank if the record ID cell is blank.

I was wondering what method other people prefer and why.
I'm already using IFERROR in several places to make sure that metrics formulas don't come back with errors so I'm leaning towards that for this project.

 

[Joe4]

Personally, I never like to have formulas return errors (hidden or not), so I would personally go the formula route.
Also, if you ever need to extend your range, I think that it is a little easier to do with formulas than with Conditional Formatting (a lot of people run into trouble copying Conditional Formatting).

 

[mole999]

I prefer formulas that evaluate to nothing, rather than applying a layer of formatting that has to be applied, and I guess using up memory, I also believe CF is volatile, where the formula may not be

 

[Shiremaid]

Glad I seem to be going in the right direction.
I had been using CF until I took a formulas class and CF definitely creates a lot of background junk. Every time you move something, it makes a duplicate of the CF rules which makes it really difficult if you need to change something.

 

[J.Ty.]

Personally, I never like to have formulas return errors (hidden or not), so I would personally go the formula route.
Also, if you ever need to extend your range, I think that it is a little easier to do with formulas than with Conditional Formatting (a lot of people run into trouble copying Conditional Formatting).

Aside from the present topic, one should remember that suppressing errors with IFERROR or the like is a little bit dangerous. Error values have a purpose: they communicate that something went wrong and tell (to some extent) what and why.

A typical error one wants to hide is #N/A. However, if the true value turns out to be

• #DIV/0! (due to a mistake in arithmetical operations),
• #REF! (due to deleting cells to which the present formula refers or filling in a wrong direction, perhaps too far)
• #NAME? (e.g., removal of a named range or UDF),

the results still look pefectly nice, as if nothing wrong has happened. You then believe that everything is OK and trust your results, while you have real problems inside and the results are far from being trustworthy.

J.Ty.

 

[Joe4]

Aside from the present topic, one should remember that suppressing errors with IFERROR or the like is a little bit dangerous. Error values have a purpose: they communicate that something went wrong and tell (to some extent) what and why.

A typical error one wants to hide is #N/A. However, if the true value turns out to be

• #DIV/0! (due to a mistake in arithmetical operations),
• #REF! (due to deleting cells to which the present formula refers or filling in a wrong direction, perhaps too far)
• #NAME? (e.g., removal of a named range or UDF),

the results still look pefectly nice, as if nothing wrong has happened. You then believe that everything is OK and trust your results, while you have real problems inside and the results are far from being trustworthy.

True, but sometimes you know exactly why the error is happening. Just like when we create Error Handling in VBA code.

Whenever possible, though, it is better to use more specific error checks, like using ISNA for those #N/A errors instead of IFERROR, as it will zero in on that particular error instead of the all-sweeping IFERROR.

Or, for a division by zero error, check the denominator for zero instead of using IFERROR.

 

[J.Ty.]

I have just noted that Excel 2016 has IFNA function, which is a perfect replacement for IFERROR, but hides only #N/A - exactly along the lines of our discussion.

J.Ty.

 

[Joe4]

I have just noted that Excel 2016 has IFNA function, which is a perfect replacement for IFERROR, but hides only #N/A - exactly along the lines of our discussion.

That is awesome! It looks like it was introduced with Excel 2013 (Excel IFNA Function).
I can't wait to get it (but unfortunately will probably have to)!