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*Here, you can see several errors, they are intentionally brought in.

**Table of Contents**hide

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

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

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

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.

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

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.

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

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.

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

**Similar Readings**

**How to Remove a Header in Excel (4 Methods)****Remove Data Validation Restrictions in Excel (3 Ways)****How to Remove Partial Data from Multiple Cells in Excel (6 Ways)****Remove Timestamps from Date in Excel (4 Easy Ways)****How to Remove Compatibility Mode in Excel (2 Easy Ways)**

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

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.

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

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

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

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 errors checking rules you don’t want to see in your dataset.

Finally, click **OK**.

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.

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

**Data clean-up techniques in Excel: Fixing trailing minus signs****How to Remove Leading Zeros in Excel (7 Easy Ways + VBA)****Remove Password from Excel (3 Simple Ways)****How to Remove Strikethrough in Excel (3 Ways)****Remove Carriage Returns in Excel: 3 Easy Ways****How to Delete Empty Rows and Columns in Excel VBA (4 Methods)**