ExtendOffice - Professional Add-ins and Tools for Microsoft Office
fackbook twitter

How to replace # formula errors with 0, blank or certain text in Excel?

You may often meet some formula errors in workbooks, such as #DIV/0, #Value!, #REF, #N/A, #NUM!, #NAME?, #NULL. And here we will show you some useful methods to search and replace these # formula errors with the number of zero or blank cells in Microsoft Excel. An example may be easy to understand, and supposing we will search and replace # formula errors in the following table:

doc-remove-formula-errors-1


Search and replace # formulas errors with specific numbers

Search and replace # formula errors with 0 or blank cells with Go to command

Search and replace # formula errors with 0 or blank cells with Kutools for Excel


Replace all error values in selected range with blank, zero or certain text in Excel

Kutools for Excel's Error Condition Wizard utility helps you easily replace all error values or a certain kind of error value such as #N/A with blank, zero, or certain text as you need in Excel. See screenshot:

doc replace error1


arrow blue right bubble Search and replace # formulas errors with specific numbers


Normally we can use Microsoft Excel's ERROR.TYPE function to convert any kinds of # formula errors to specific numbers.
In our example above, just enter = ERROR.TYPE(A3) in a blank cell, and you will get the number of 2. And different # formula error will be converted to different numbers:

No.

# Errors

Formulas

Converted to

1

#NULL!

=ERROR.TYPE(#NULL!)

1

2

#DIV/0!

=ERROR.TYPE(#DIV/0!)

2

3

#VALUE!

=ERROR.TYPE(#VALUE!)

3

4

#REF!

=ERROR.TYPE(#REF!)

4

5

#NAME?

=ERROR.TYPE(#NAME?)

5

6

#NUM!

=ERROR.TYPE(#NUM!)

6

7

#N/A

=ERROR.TYPE(#N/A)

7

8

#GETTING_DATA

=ERROR.TYPE(#GETTING_DATA)

8

9

others

=ERROR.TYPE(1)

#N/A

Of course, you can drag the Fill handle to convert other formula errors to numbers across ranges. However, these numbers are too many to remember and apply clearly.


arrow blue right bubble Search and replace # formula errors with 0 or blank cells with Go to command

This way is able to convert all # formula errors in a selection with 0, blank or any other values easily with Microsoft Excel's Go To command.

Step 1: Select the range that you will work with.

Step 2: Press the F5 key to open the Go To dialog box.

Step 3: Click the Special button, and it opens the Go to Special dialog box.

Step 4: In the Go to Special dialog box, only check the Formula option and Errors option, see screenshot:

doc-remove-formula-errors-2

Step 5: And then click OK, all # formula errors have been selected, see screenshot:

doc-remove-formula-errors-3

Step 6: Now just enter 0 or any other value that you need to replace the errors, and press Ctrl + Enter keys. Then you will get all selected error cells are filled with 0.

doc-remove-formula-errors-4

Of course, you can delete all of error cells with pressing the Delete key, leaving the blank cells.


arrow blue right bubble Search and replace # formula errors with 0 or blank cells with Kutools for Excel

If you have Kutools for Excel installed, its Error Condition Wizard tool will make your work easy to replace all kinds of formula errors with 0, blank cells, or any custom messages.

Kutools for Excel : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days. 

1: Select the range with the error values that you want to replace with zero, blank or text as you need, then click Kutools > More > Error Condition Wizard. See screenshot:

doc replace error1

2. In the Error Condition Wizard dialog box, please do as follows:

doc replace error1


(1) In the error types section, select an error type as you need such as Any error value, Only the #N/A error value or Any error value except #N/A. Here I select the Any error value option.

(2) In the Error display section, if you want to replace all error values with blanks, please check the Nothing (a blank cell) option.

For replacing all error values with zero or a certain text, please select the A message (text) option, enter number 0 or the certain text you need into the blank box.

(3) Click the OK button.

And then you can see all error values in selected range are replaced with blanks, zeros, or certain text as you specified above immediately. See screenshots:

Replace all error values with blank

doc replace error1

Replace all error values with zero

doc replace error1

Replace all error values with certain text

doc replace error1

If this utility is helpful, you can download and try the free trial now!

Related article:

How to change #DIV/0! error to the readable message in excel?


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Add comment


Security code
Refresh