Suppress & Hide Formula Cell Value Error in Excel

»»»Suppress & Hide Formula Cell Value Error in Excel
When using many formulas in the workbook of Microsoft Excel, the spreadsheet may display error for formulas that do not return proper values or results.

For example, the cell displays #N/A for value not available error, #DIV/0! for divide by zero error, #VALUE! for error in value, #REF! for invalid cell reference, #NULL! for cell references that are separated by space and etc.

Sometimes, these errors are expected. In these cases, you may want to hide the all those errors from the worksheets to make the things tidy and presentable.

To hide the errors when viewing the spreadsheet in Excel, we can make use of IFERROR function. IFERROR returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.

Syntax
IFERROR(value, value_if_error)

IFERROR can evaluate error types of #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.

Example
Cell A1: 10
Cell B1: 0

The formula IFERROR(A1/B1, “Error in calculation”) will return “Error in calculation” instead of #DIV/0!. The formula IFERROR(A1/B1, 0) will return zero, while the formula IFERROR(A1/B1, “”) will return empty cell.

Other formulas can be nested inside the IFERROR function, for example, IFERROR(INDIRECT(“Sheet2!A1”), “”). This is useful to suppress any error messages that may be caused by any formulas.

If you only want to hide the error message when printing the sheet or workbook, you can choose to just hide the errors for the printout, and leave them visible in the spreadsheet.

To hide the error messages for Excel printout, click or tap on Page Setup in the Print menu. Go to the Sheet tab. Under the drop down menu of Cell errors as, select <blank> if you want to hide the error completely.

Hide Cell Errors When Print Excel Spreadsheet

Alternatively, you may choose to display all errors as — or #N/A.

Hit OK when done, and now you can print your Excel spreadsheet without any errors.

By | 2017-01-20T00:57:40+00:00 January 20th, 2017|Categories: Office|Tags: |0 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.

Connect with LK through Tech Journey on Facebook, Twitter or Google+.

Pin It on Pinterest

Share This

Share This

Share this post with your friends!