Excel: replace formulas error messages with zero, blank cells, or text (customize formula error messages)

Sometimes the formula may produce error message in Excel. For example, in this formula =A1/B1, if B1 is empty or contains 0, the formula will display a #DIV/0 error. Is there any way to make those error messages clearly readable with some customizing text or others? Kutools for Excel’s Error Condition Wizard can quickly add error condition to a formula or group of formulas to display the formulas that produce errors with the 0(zero), a custom message, blank cells, cell reference, etc.

Click "Kutools">>"More Tools">>"Error Condition Wizard". See screenshots (Fig. 1)

Fig. 1: Error Condition Wizard

Usage:

Step 1：Use the to select the range that you want to modify, and click “Next”; See screenshot (Fig.2).

Notes:

The range must contain formula cells.
If the “Use the Excel 2007/2010 IFERROR function” option is checked, you can not specify the type of errors in Step 2.
If the “Use the Excel 2007/2010 IFERROR function” option is checked, your workbook will not be compatible with versions prior to Excel 2007.

Fig. 2: Error Condition Wizard - Step 1

Step 2：Specify the type of error that you want to trap, and click “Next”; (See Fig.3)

Fig. 3: Error Condition Wizard - Step 2

Step 3：Specify what you would like to display instead of errors, and click “Finish”; (See Fig.4)

The following types of content will display instead of errors:

Blank (empty cell): Empty cells replace the errors.
Zero (0): Digital 0 replace the errors.
A message (text): There appears a text input box, and all text in the box will replace the errors.
Contents of a Named cell: The content of a named cell replaces the errors.
A cell reference: An absolute reference replaces the errors.
Another formula: Enter the formula in the input field to replace the errors.

Fig. 4: Error Condition Wizard - Step 3

Notes:

You can press Undo (Ctrl+Z) to restore this operation immediately.
The selected range can include formula cells and non-formula cells, but the non-formula cells will be automatically ignored in the formatting.