Quickly replace formulas error messages with zero, blank cells, or text

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 >> Error Condition Wizard. See screenshot:

Usage:

1. Apply this utility and in the Error Condition Wizard dialog box, do the following options:

• A: Click button to select the range that you want to replace the error formulas.
• B: Click the Error type drop down list to choose the error formula type that you want to replace. (you can select any error value, only the #N/A error value or any error value except #N/A)
• C: Specify what you would like to display instead of errors, in this example, I will replace the errors by 0. See screenshot:

2. Then click OK, all of the errors have been replaced by 0. See screenshots:

Tips: you can also replace the errors with the following messages:

• Nothing (a blank cell): Empty cells replace the errors.
• A message (text): Type a value into the text box, and the value in the box will replace the errors.
• A cell reference: An absolute reference replaces the errors.

Notes:

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

Convert Errors to other value demo