Search and replace # formula errors with 0 or blank cells 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.

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

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

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubbleSearch and replace # formulas errors with specific numbers

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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 bubbleSearch 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 bubbleSearch 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 includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

Step 1: Select the range that you want to change the error values.

Step 2: Click Kutools > More Tools > Error Condition Wizard…, see screenshot:

doc-remove-formula-errors-5

Step 3: In the Error Condition Wizard dialog box, select the error 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. Then specify the value you would like to use for replacing errors under Error display section. In this case, I want the blank cells to replace the formula errors. See screenshot:

doc-remove-formula-errors-6

Step 4: And then click OK, all the errors have been replaced with blank cells.

doc-remove-formula-errors-7

Kutools for Excel's Error Condition Wizard tools can add error condition to a formula or group of formulas, and display a custom message for formulas that produce errors, such as 0, blank cells, or any custom messages. Click to know more…


Related article:

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


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

0#rachelle2014-07-30 04:05
thank you sooo sooo much

really helped a LOT :lol:
Reply | Reply with quote | Quote

Add comment


Security code
Refresh