Updated August 16, 2023
Introduction to Errors in Excel
Like any other software, Excel often produces errors; however, Excel’s errors are often the user’s errors in inserting the data or asking Excel to do something that cannot be done. So when we see that Excel is giving an error, we must correct the error instead of using the error handling functions to mask the error.
Excel errors are not just errors; they are also a source of information about what is wrong with the function or the command set for the execution.
Errors in Excel (Table of Contents)
- Introduction to Errors in Excel
- Examples of Errors in Excel
- Explanations of Errors in Excel
- How to Correct Errors in Excel?
Errors occur when we insert some formula in Excel and miss adding the required input in the expected forms, suppose we have inserted a function to add two cells, then Excel expects that the cells would have numbers. If either of the cells has text, it will give an error. Every Excel function comes with its terms and conditions, and if any of the requirements of the function are voided, then there are Excel errors. Every function has a syntax, which must be properly complied with, and if any deviation is observed in entering the syntax, then there will be an Excel error.
Understanding Excel errors are important same as understanding the functions. These displayed errors tell us a lot of things. One can quickly solve those errors with a proper understanding of Excel errors.
Types of Errors in Excel (Examples)
1 – “#Name?” error.
2 – “#div/0!” error.
3 – “#NULL!” error.
4 – “N/A” error.
5 – “#Value!” error.
6 – “#NUM!” error.
Explanations of Errors in Excel
Excel errors in the above examples are different and occur for other reasons. Hence, this becomes important to identify the main cause behind the error to correct the error quickly.
The “#Name?” error is because the typed formula is incorrect. For example, entering =su(A1:A2) instead of =Sum(A1:A2) will give the error “#Name?” and if we ask Excel to divide any number by zero, we will get the error of “DIV/0!”.
Similarly, the “#NULL!” happens if the user enters an incorrect range operator-like space instead of using “:.”
The error of “#N/A” appears if we have any lookup function to search for any value that does not exist in the data. Hence “#N/A” is returned.
“#VALUE!” happens because of giving a reference that is invalid or not supposed to be referred to. If we use the sum function, then Excel assumes that we will reference cells with a numeric entry in those cells. If we have given a reference of cells with text, we will get “#Value!”
The “#NUM!” error happens when the displayed result is something that is not valid. Suppose we have entered a function to multiply 999999999999 with 999999999999999, then the resulting number will be so long that it will not be accurate, and we will get the “#NUM!” error.
How to Correct Errors in Excel?
Errors in Excel are elementary to correct. Let’s understand how to Correct Errors with some Examples.
Excel errors can be corrected in three ways, as below.
- By identifying the cause of an error and correcting that.
- By masking the error with error handling functions to display a result.
In the first method, the error is corrected from the source, but in the second option, the error exists; however, the error is masked with some other value using error handling functions.
- By evaluating the formula.
Method #1- Identifying the cause of the error and correcting that
Step 1: Go to the cell with an error function.
Step 2: Click on the little warning sign on the cell’s left side.
Step 3: Check what error happened.
Step 4: Try to get help from the inbuilt data.
Step 5: After checking what kind of error has happened, correct the error from the source, and the error will be solved.
Correcting errors when we see the below error codes.
#NAME?: This error happens if the formula is incorrectly typed.
#DIV/0!: This error happens if a value is asked to divide by zero.
#REF!: This error will happen in case of missing reference.
#NULL!: This happens if we have used invalid range delimiters.
#N/A: When the searched value is not in the data from where it is searched.
###### error: This is not an error; the resulting length is more than the width of the column.
#VALUE!: This happens when the value of the two cells is not the same, and we operate some mathematical functions.
#NUM!: This happens if the resulting value is invalid.
Method #2 – Masking the error using error handling functions
Step 1: Go to the cell with an error function.
Step 2: Click the warning sign on the cell’s left side.
Step 3: Select “Edit in the formula bar.”
Step 4: Now choose an error handling function such as “IFERROR” and insert that before the actual formula, and this function will display some value instead of showing an error.
Adding IFERROR Function before the formula displays the values instead of an error.
Method #3 – Evaluating the Formula
If we use nested formulas with an error, the complete formula will give an error.
This is the Image that contains errors.
Step 1: Click on the cell that has an error.
From the ribbon, click on the “Formulas” option.
Step 2: Click on Evaluate formula option.
Step 3: Click on Evaluate.
Step 4: Keep clicking on evaluate formula until we get the function causing the error.
Step 5: Correct the formula that is creating an error.
After correcting the formula, we get the correct answer.
Drag the formula by using Ctrl + D.
Step 6:The error will be fixed.
Things to Remember
- Not all displayed errors are genuine errors. Some errors may appear due to formatting issues, resulting in false error notifications. Such as, the error sign “###” means that the width of columns needs to be increased.
- Resolving errors is only possible by adhering to the required syntax of the function.
- Every error that occurs has a different solution and needs other error-handling functions.
- If we are unsure what type of error has happened, we can use the inbuilt function of Excel, “= Error.type,” to identify the error type.
- The “#REF” error is the most critical in Excel. This error occurs when Excel cannot identify the reference of a cell. It is crucial to work with greater care and attention to prevent these errors.
- To solve errors in the case of nested Excel formulas, we prefer to enter one function at a time to know what function has an error.
Recommended Articles
This has been a guide to Errors in Excel. Here we discuss types of errors and how to correct Errors in Excel with examples and downloadable Excel templates. You may also look at these useful functions in Excel –
