Hiding #DIV/0! or #N/A

An old but simple trick to hide error values.

Create a Conditional Format. Say you are in cell B2, use Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> enter the formula =ISERROR(B2) , click Format, format the text to white.

Now any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NULL!) in cell B2 will show as white text on a white background. Still there, but you can't see it.

Because the formula uses a relative address, you can use the format painter to copy the conditional format anywhere you want. Good for a pivot table range where calculated fields may show unwanted #DIV/0! or #N/A.

Also, you can use most Information functions to hide or show only what you want users to see. For instance ISNUMBER or ISTEXT.