How to hide formula errors in Excel

Errors may be inevitable in worksheets containing complex formulas. Thankfully, you do not need to investigate and correct each of them. It is OK to hide errors you expect and not to correct them to improve the look and feel of your worksheet. In this guide, we’re going to show you how to hide formula errors in Excel.

Download Workbook

Using formulas to hide errors

Our first tip is a well-known formula auditing approach in Excel. Excel provides several error-auditing functions including IFERROR which allows you to return any other value in its second argument if the formula in the first argument returns an error. You can simply return an empty string ("") to hide any error message from the formula in the first argument.

Following is a list of other error-related functions in Excel:

IFNA
ERROR.TYPE
ISERROR
ISERR

Using conditional formatting to hide

You can always hide any cell content by choosing the same font and background colors. Errors are not the exceptions. The trick is to make the coloring dynamic. You may already know that the Conditional Formatting feature allows you to alter cell(s) formatting according to its own or other cell values. However, you may not know that you can specifically target errors.

Select the range that you anticipate seeing an error.
Follow Home > Conditional Formatting (Styles) > New Rule in the Ribbon to open New Formatting Rule dialog.
Click Format only cells that contain item in the Rule list.
Select Errors item in Format only cells with dropdown.
Click Format button to open Cell Format dialog.
Activate Font tab.
Click Color selector to see the colors.
Select the same color with background.
Click OK buttons to apply the formatting.
Voila!

Hiding errors in the printed document

Errors in the worksheet may not bother you, or you want to see them to keep them in check. However, they look worse in a printed document. Thankfully, there is a feature to hide errors only in printed documents.

You need to open the Page Setup dialog first. Use the link in the Print (Ctrl + P) section or the tiny arrow in Page Layout > Page Setup.
Activate Sheet tab.
Select a suitable option in Cell errors as Obviously <blank> option will be the most suitable for hiding.
Click OK to apply.

Hiding formula error indicators

This tip does not address the errors directly but the error indicators in cells. As you know, Excel displays a little triangle at the top-left corner by default when a cell contains an error value.

Although these indicators may be helpful for beginner users, they become annoying rapidly. Especially if you are OK to display error messages like #N/A which indicates an unmatched condition in a lookup formula. You can hide them entirely or alter their color and scenarios they become visible in Excel Options.