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

Recommended Productivity Software

Office Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).

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

Hint


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?


Is your problem solved?

Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

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 More than 120 powerful advanced functions which designed for Excel:

  • 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