Every now and then, we bump into errors in the Excel datasheet. Whenever we input something wrong in the formula or function, Excel lets us know that with a message starting with Hashtag (#). In this article, you’ll get to know about the Errors in Excel, their Meaning, and the methods to remove the error.

To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company.

## Errors in Excel and Their Meaning: 15 Different Errors

There can be different types of errors in Excel. Some of them are Formula Errors. When we type the wrong function name in the formula or inputs the wrong reference data in the argument, Excel shows the cell value which starts with #. This kind of error is Formula Error. Other errors are File Errors. When the Excel file is corrupted or incompatible with the Excel version, this error occurs. Therefore, follow along with the article to know in detail.

### Formula Errors in Excel and Their Meaning

#### 1. ##### Error

The most common error in Excel is the **#####** error. But, this is not a formula-based error. This happens due to the width of the column being too small and thus, cannot show the entire cell values.

In the following dataset, column **D** has the **##### **error.

**Solution:**

Expand the column width by dragging the boundary on the right side and you’ll get to see the values properly.

#### 2. #DIV/0 Error

#DIV/0 error shows up as we divide any number or cell value by 0.

Here, in cell **E5**, as we attempt to divide **D5 **by 0, it shows **#DIV/0!**.

**Solution:**

Divide **D5 **by any value other than 0, the error won’t occur.

#### 3. #NAME? Error

Another formula error we encounter in Excel is the #NAME? Error. If we make a spelling mistake in writing the function name, Excel shows this error. Moreover, this problem happens whenever Excel isn’t able to recognize the text in the formula. In this case, we’ll try to find the **Maximum Net Sales** from the range **D5:D10**.

In the below dataset, Excel can’t recognize the formula in cell **E5**.

**Solution:**

Instead of **MA **in the formula, type **MAX, **and you’ll get the accurate value.

#### 4. #N/A! Error

The meaning of this error is ‘**Not Available**’. This error arises when the formula or a function in the formula fails to find the referenced data. It mostly occurs with lookup functions.

In the given dataset, we’ll try to look for the cell **F5 **data in the range **B5:D10**. But, **#NA! **error occurs. If we notice carefully, we’ll see that the **F5 **cell value doesn’t exist in the range.

**Solution:**

Input the reference data correctly. In this example, type Wilham in cell **F5**. Hence, it’ll return the **Net Sales **amount of **Wilham**.

#### 5. #REF! Error

The **#REF!** **Error **is also a common error that arises when we accidentally delete any row or column that we have referenced in the formula. It can also arise when we copy and paste formulas with relative references to a different location.

In this case, we add the cells **D5**, **D6, **and **D7 **in cell **E5**.

But, as we delete the 7th row, the **#REF! Error **occurs in cell **E5**.

**Solution:**

To solve this problem, type the formula again for cells **D5 **and **D6**. As a result, you’ll get the precise value.

**Read More: **How to Fix #REF! Error in Excel

#### 6. #VALUE! Error

When a value isn’t a valid type, or when we use a wrong type of function argument, this **#VALUE! Error **occurs.

In this example, we try to add cells **D5 **and **C5 **to cell **E5**. But, as the data type is not the same for the two cells, the error arises.

**Solution:**

To avoid this error, input the same type of data type in the formula. Here, instead of **C5**, type **D6 **and it’ll return a result.

#### 7. #NUM! Error

This error arises when the formula contains invalid numeric data in that operation and the calculations become impossible.

The below dataset attempts to find the square root of cell **D6**. But, **D6 **is a negative number and we know, it’s not possible to compute the square root of a negative number. Thus, the** #NUM! Error **shows up.

**Solution:**

Remove the minus from cell **D6 **value and it’ll immediately return the square root.

#### 8. #NULL! Error

#NULL! error occurs when we place a **Space **instead of a **Comma **or a **Colon **in the function argument.

In the following dataset, we use the **SUM** function to add the values in the range **D5:D6**. Since we input a space instead of a colon between **D5 **and **D10**, the **#NULL!** **Error **occurs.

**Solution:**

Place **Colon **between **D5 **and **D10 **and you’ll get the sum result.

#### 9. Circular Reference Error

When we reference the same cell in which we are writing the formula, the **Circular Reference Error **occurs. This gives out a wrong calculation value.

Here, in cell **E5**, we write a formula but **E5 **is also a reference data in the argument. As soon as we press **Enter**, ‘**–**’ shows up.

**Solution:**

Remove **E5 **from the formula and the error will disappear.

#### 10. #SPILL! Error

This error arises whenever a formula gives out a spill range that runs into a cell that already contains a value.

In this example, we’ll use the **UNIQUE **function to pull out the unique names in column **B **into a spill range starting in cell **E5**. But, **E7 **is already preoccupied with the given dataset. Thus the **#SPILL!** **Error **shows up.

**Solution:**

Delete the preoccupied cell value in **E7**. consequently, the formula will return the unique names.

#### 11. #CALC! Error

If a formula runs into a calculation error with an array, the **#CALC!** **Error **arises.

Here, in this **FILTER** function, it asks for **A **from the range **C5:C10** which doesn’t exist. Hence, the error shows up.

**Solution:**

Replace** A** with **AC **and it’ll return the result.

### File Errors in Excel and Their Meaning

#### 1. “The file is corrupt and cannot be opened” Error

If you upgrade your Excel, it’s very likely that you’ll see this error message. But, this is not something to worry about. Hence, change the settings given below to solve the problem.

**STEPS:**

- Firstly, click the
**File**tab and select the**Options**in Excel. - Then, select
**Trust Center**from the**Excel Options**window. - After that, select
**Trust Center Settings**.

- A new window will pop out.
- Finally, in the
**Protected View**, uncheck all the options like it’s shown below and press**OK**.

#### 2. **“**Excel cannot open the file ‘(filename)’.xlsx” Error

If the file you are trying to open is not compatible with the Excel version and if it is corrupted or damaged, this error message shows up. Therefore, follow the steps below to fix the issue.

**STEPS:**

- In the beginning, open the Excel and select the tab
**File**. - Then, select
**Export**, and then select**Change File Type**. - At last, change the file format and save the new file.

#### 3. **“**The document caused a serious error the last time it was opened**“** Error

If the Excel file is included in the list of disabled files, it’ll cause serious errors. But, you can fix this error message with the steps given below.

**STEPS:**

- First, In Excel, select the
**File**tab. - Then, select
**Options**. - After that, in the
**Add-ins**tab, select**COM Add-Ins**in the**Manage box.** - Subsequently, click
**Go**.

- A dialog box will pop out, and there, uncheck all the boxes in the
**Add-ins available**section. - Lastly, press
**OK**.

- Now, restart the application to finish the setup.

#### 4. “There was a problem sending the command to the program” Error

This error message arises when the process running in the Excel file doesn’t let the Excel close. So, follow the steps to fix the problem.

**STEPS:**

- Firstly, select the
**File**tab and select the**Options**in Excel. - Then, select
**Advanced**. - After that, in the
**General**section, uncheck the**Ignore other applications that use Dynamic Data Exchange****(DDE)**box. - Eventually, press
**OK**.

## Functions to Check Excel Errors

### 1. ISERROR Function

We can use the **ISERROR** function to check if there would be any error in our applied function.

In the below dataset, cell **E5 **contains a formula.

**STEPS:**

- At first, in cell
**E5**, type the formula:

`=IF(ISERROR(C5+D5),"Error",C5+D5)`

- Then, press
**Enter**and it’ll return the**Error**.

The **IF** function finds out whether a certain condition is met or not. In this example, the condition is the **ISERROR** function. If the condition is satisfied, it’ll return an Error. The **ISERROR** function checks to see if there is an error in **C5+D5**. Since **C5 **is a text, the formula won’t work and it’ll return an Error.

### 2. AGGREGATE Function

The **AGGREGATE** function calculates ignoring any error values.

In this example, we want to find the sum of the range **D5:D10**. Now, if we use the **SUM** function in cell **E5**, it’ll return the error as we have an error value in cell **D6**.

But, we can use the **AGGREGATE** function to ignore the error value in **D6**.

**STEPS:**

- Firstly, in cell
**E7**, type the formula:

`=AGGREGATE(9,7,D5:D10)`

- Then, press
**Enter**and it’ll return the result.

**NOTE: **9 is the function number for **SUM**, 7 is the option to Ignore hidden rows and error values and **D5:D10** is the range.

**Download Practice Workbook**

To practice by yourself, download the following workbook.

## Conclusion

Henceforth, you will be able to understand the Meaning of Errors in Excel and also solve them with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

## Errors in Excel: Knowledge Hub

**<< Go Back To Learn Excel**