Errors in Excel and Their Meaning (15 Different Errors)

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


Download Practice Workbook

To practice by yourself, download the following workbook.


15 Different Errors in Excel and Their Meaning

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.

Formula Errors in Excel and Their Meaning

Solution:

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

Read More: REF Error in Excel (9 Suitable Examples)


2. #DIV/0 Error

This 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!.

Formula Errors in Excel and Their Meaning

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.

Formula Errors in Excel and Their Meaning

Solution:

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

Read More: Reasons and Corrections of NAME Error in Excel (10 Examples)


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.

Formula Errors in Excel and Their Meaning

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

Formula Errors in Excel and Their Meaning

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

Formula Errors in Excel and Their Meaning

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


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 in cell E5. But, as the data type is not the same for the two cells, the error arises.

Formula Errors in Excel and Their Meaning

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.

Read More: VALUE Error in Excel: 7 Reasons with Solutions


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.

Formula Errors in Excel and Their Meaning

Solution:

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

Related Content: Excel Error: The Number in This Cell is Formatted as Text (7 Fixes)


8. #NULL! Error

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

Formula Errors in Excel and Their Meaning

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.

Formula Errors in Excel and Their Meaning

Solution:

Remove E5 from the formula and the error will disappear.

Related Content: How to Remove Value Error in Excel (4 Quick Methods)


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.

Formula Errors in Excel and Their Meaning

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.

Formula Errors in Excel and Their Meaning

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 much 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 there select Change File Type.
  • At last, change the file format and save the new file.

File Errors in Excel and Their Meaning


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.

File Errors in Excel and Their Meaning

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

Functions to Check Excel Errors

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.


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.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo