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.
An example may be easy to understand, and supposing we will search and replace # formula errors in the following table:
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.
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:
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.
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:
Step 5: And then click OK, all # formula errors have been selected, see screenshot:
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.
Of course, you can delete all of error cells with pressing the Delete key, leaving the blank cells.
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:
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:
Step 4: And then click OK, all the errors have been replaced with blank cells.
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…