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.

**Table of Contents**Expand

## 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.

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

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.

`=IFNA(D4/C4,0)`

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.

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

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.

`=ISERR(D4/C4)`

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.

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

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.

`=ISNA(D4/C4)`

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))`

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**

- How to Correct a Spill (#SPILL!) Error in Excel
- [Fixed!] ‘There Isn’t Enough Memory’ Error in Excel
- [Fixed] Excel Print Error Not Enough Memory
- How to Find Reference Errors in Excel
- [Fixed]: Can’t Find Project or Library Error in Excel
- [Fixed!] Excel Found a Problem with One or More Formula References in This Worksheet

**<< Go Back To Errors in Excel | Learn Excel**