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, yet it is annoying. If you want, you can remove errors though the best option is to understand the error and correct it rather removing it. In this article, I’m going to explain, how to remove error 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.

Remove Error

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

Download to Practice

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. This error usually comes for either the cell is accidentally deleted or cut and pasted 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.

8 Methods to Remove Error in Excel

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

Using Go To Special to Remove Error

➤ 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
Finally, click OK.

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

Using Go To Special to Remove Error

Hence, all the Errors will be removed.

Read More: How to Remove Number Error in Excel (3 Ways)


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.

=IFERROR(D4/C4, "Value Not Found")

Using the IFERROR Function to Remove Error

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.

Using the IFERROR Function to Remove Error

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.

Using the IFERROR Function to Remove Error

Read More: How to Remove Everything After a Character in Excel (7 Methods)


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.

=IFNA(D4/C4,0)

Using the IFNA Function to Remove Error

Here, for the IFNA function I’ve taken the divide function as a value 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.

=IFNA(VLOOKUP(G4,B4:E12,4,FALSE),0)

Here, within IFNA, I’ve taken the VLOOKUP function as a value then used 0 as value_if_na. Now, as there exists no information regarding Coconut instead of showing #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.

Using the IFNA Function to Remove Error

Read More: How to Clear Formula in Excel (7+ Methods)


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.

=ISERR(D4/C4)

Using the ISERR Function to Remove Error

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

Press the ENTER key then, 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.

Using the ISERR Function to Remove Error

Read More: How to Remove Formulas in Excel: 7 Easy Ways


Similar Readings


5. Using the ISERROR Function to Remove Error

You can use the ISERROR 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.

=ISERROR(VLOOKUP(G4,B4:E12,4,FALSE))

Using the ISERROR Function to Remove Error

Here, the ISEORR function takes the whole formula or function as a value 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.

Using the ISERROR Function to Remove Error

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

Related Content: How to Remove Data Validation in Excel (5 Ways)


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.

=ISNA(D4/C4)

Using the ISNA Function to Remove Error

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.

=ISNA(VLOOKUP(G4,B4:E12,4,FALSE))

Using the ISNA Function to Remove Error

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 along with ISNA, the combination of IF and ISNA works the same as IFNA

Read More: How to Remove Conditional Formatting in Excel (3 Examples)


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 )

Using VBA to Remove Error

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

Using VBA to Remove Error

Here, I declared the Sub procedure Remove_Error
First, selected 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.

Using VBA to Remove Error

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, selected the value from H column.
Then, click on the Button.

Using VBA to Remove Error

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

Read More: How to Remove #DIV/0! Error in Excel (5 Methods)


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.

Using Ribbon to Remove Error

Then, select Options.

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

Using Ribbon to Remove Error

Now, if you do 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.

Practice Section to Remove Error

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

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo