How to Remove Error in Excel (8 Methods)

In a worksheet, while working with functions there exist possibilities of appearing errors as all data are not well organized. Though it is so common, it is annoying. If you want, you can remove errors though the best option is to understand the error and correct it rather than remove it. In this article, I’m going to explain, how to remove errors in Excel.

For the explanation, I’m going to use a sample dataset of a store that represents product information. The dataset has 4 columns which are Product, Amount, Total Price, and Price/Kg.

Here, you can see several errors, they are intentionally brought in.

Different Types of Error in Excel

Before diving into the errors removing procedures, I want to explain why various errors occur.

➤#VALUE!
This error occurs when the wrong type of function or operand has been used. When we use the wrong data type for a function or formula like adding two different types of data.

➤#DIV/0!
Division by zero. When you want to divide two values and the divisor is zero then this error arises.

➤#NUM!
Invalid numeric value. When a number for any argument of a function is found invalid, this error arises

➤#NAME!
Whenever Excel is not able to recognize the text in the formula or misspell the function name then this error pops up.

➤#NULL!
When cell reference is not specified then this error occurs.

➤#N/A
Can’t find referenced data. When the source data and the lookup value are not of the same data type. This error mostly occurs with the VLOOKUP, HLOOKUP, LOOKUP, HLOOKUP, MATCH functions, etc.

➤#REF!
When the referenced cell is not valid within an argument then #REF! error is shown. This error usually comes for either the cell is accidentally deleted or cut and the referenced cell in different locations.

➤#NULL!
Cell references are separated incorrectly. You can get this error when you do not use the space character properly.

Read More: Errors in Excel and Their Meaning

How to Remove Error in Excel: 8 Methods

1. Using Go To Special to Remove Error

If you want to remove the annoying errors, then you can use the Go To Special.

Let’s see how you can use this feature.
First, open the Home tab >> go to Editing group >> from Find & Select >> select Go To Special

➤ A dialog box of Go To Special will pop up.
From there select the type of your choice to remove.

➤ I selected Formulas then Errors in the dialog box.

➤ Finally, click OK.

Now, you will see all the Formulas that contain errors are selected. Now, press the DELETE key to remove the Errors.

Hence, all the errors will be removed.

2. Using the IFERROR Function

There are some functions by using which you can remove the error and place some text or you can keep it blank (anything you prefer).

Here, I will use the IFERROR function to remove errors.
First, select a cell with a formula where the error exists.
➤ I selected the E4 cell.
Then, type the following formula with the existing one.

Here, the IFERROR function will consider the divide formula as a value and will trigger the formula first. I used a text Value Not Found as value_if_error. Now, if the division encounters any error, then rather than showing it will show the text I have used.

Then, press the ENTER key.

Now, you can use this function to wrap up the formulas for the rest of the cells of errors.

In case you have errors like #NAME! then you should check the formula you typed because this error occurs when the formula is misspelled.

In case you have errors like #NULL! then you should check how you referenced the cells in the formula.

3. Using the IFNA Function to Remove Error

You can use the IFNA function only for the #N/A! error, it doesn’t work with any other errors.

Let’s see the use of the function,
First, select a cell with a formula where the error exists.
➤ I selected the E4 cell.
Then, type the following formula with the existing one.

Here, for the IFNA function, I’ve taken the divide function as a value and then used 0 as value_if_na so that it shows 0 instead of an error.

Press the ENTER key.

Now, still exists the error because the error is #VALUE error. We know the IFNA function can’t handle other errors except for the #N/A error.

Again, select a cell where #N/A error exists.
➤ I selected the H4 cell.
Then, type the following formula with the existing one.

Here, within IFNA, I’ve taken the VLOOKUP function as a value and then used 0 as value_if_na. Now, as there exists no information regarding Coconut instead of showing a #N/A error it will show 0.

Now press the ENTER key then you will see that the error is removed from that selected cell.

4. Using the ISERR Function

You also can use the ISERR function to remove all types of errors except the #N/A error. It doesn’t handle #N/A errors.

Let’s jump into the procedure,
First, select a cell with a formula where the error exists.
➤ I selected the E4 cell.
Then, type the following formula with the existing one.

Here, the ISERR function takes the whole formula or function as a value and then returns TRUE for Error and FALSE for Correct values.

Press the ENTER key, it will remove the error with TRUE or FALSE.
You also can use the –ISERR function if you want (1, 0) instead of TRUE or FALSE.

Here, I used the exact formula for the #N/A error but it won’t work because the ISERR function can’t handle the #N/A error.

5. Using the ISERROR Function to Remove Error

You can use the error function to remove errors. The ISERROR function shows TRUE for Error otherwise shows FALSE where it considers all types of errors.

To use this function, select a cell with a formula where the error exists.
➤ I selected the H4 cell.
Then, type the following formula with the existing one.

Here, the ISEORR function takes the whole formula or function as a value and then returns TRUE for Error and FALSE for correct values. It considers all types of errors.

In the end, press the ENTER key. Then, it will remove the error with TRUE.

Now, you can use this function to wrap up the formulas for the rest of the cells of errors.

If you don’t want TRUE or FALSE as the output, you can combine IF along with ISERROR, the combination of IF and ISERROR works the same as IFERROR. 

6. Using the ISNA Function

In case you only have #N/A! error you can use the ISNA function because it doesn’t work with any other errors.

To use this function,
First, select a cell with a formula where the error exists.
➤ I selected the E4 cell.
Then, type the following formula with the existing one.

Here, the ISNA function takes the used formula as a value then it tests the value to find out whether it is a #N/A error or not. In the ISNA function, TRUE means Error, and FALSE means Correct.

Press the ENTER key.

As the selected cell has a #VALUE error the ISNA function won’t consider it an error as it only knows one error which is the #N/A error.

Again, select a cell where #N/A error exists.
➤ I selected the H4 cell.
Then, type the following formula with the existing one.

Here, within the ISNA function, I’ve taken the VLOOKUP function as a value. Now, as there exists no information regarding Coconut instead of showing #N/A errors it will show TRUE.

Press the ENTER key then you will see that the error is removed from that selected cell.

If you don’t want TRUE or FALSE as the output, you can combine IF with ISNA, the combination of IF and ISNA works the same as IFNA. 

7. Using VBA to Remove Error

To remove the error in Excel you can also use the VBA.

To use the VBA editor,
First, open the Developer tab >> select Visual Basic (Keyboard Shortcut ALT + F11 )

Next, it will open a new window of Microsoft Visual Basic for Applications.
From there, open Insert >> select Module

A Module will open then type the following code in the opened Module.

Sub Remove_Error()
On Error Resume Next
Columns("E").SpecialCells(xlFormulas, xlErrors).ClearContents
Columns("H").SpecialCells(xlFormulas, xlErrors).ClearContents
On Error GoTo 0
End Sub

Here, I declared the Sub procedure Remove_Error
First, select the column from where I want to remove errors( you can use the column of your choice or need)
➤I’ve taken the E and H columns.
Then, used the ClearContents method to remove the errors from the selected column.

Save the code and go back to the worksheet.
Now, open the Developer tab >> from Insert >> select Button

Now, assign the Macro in the Button.
➤ From the Macro name select the Remove_Error also select the workbook within Macros in.

Finally, click OK.

Now, select the column cells from where you want to remove the errors.
I selected column C.

➤Then, to Run the Macro click on the Button.

Thus, the error from the selected cells will be removed.

Again, select the value from the H column.
Then, click on the Button.

Therefore, the error from the selected cells will be removed.

8. Using Ribbon to Remove Error

There is an option to remove errors by using the Ribbon but it is not the solution.

To use the ribbon,
First open File.

Then, select Options.

A new window will pop up.
Now, select Formulas >> then Uncheck all those error-checking rules you don’t want to see in your dataset.
Finally, click OK.

Now, if you make any mistakes while typing functions or working with formulas it won’t arise any background error.

Practice Section

I’ve provided a practice sheet in the workbook to practice these explained ways. You can download it from the above link.

Download to Practice

Conclusion

In this article, I’ve explained 8 different types of ways to remove error in Excel. I hope all these different ways will help you to remove the errors. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

Related Articles

<< Go Back To Errors in Excel | Learn Excel