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
Get FREE Advanced Excel Exercises with Solutions!
