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. With a proper understanding of Excel errors, one can quickly solve those 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 shown in the above examples are of different types and occur for other reasons. Hence, this becomes important to identify the main cause behind the error to correct the error quickly.
The error of “#Name?” 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 displayed on the cell’s left-hand 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 in case 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 on the little warning sign on the cell’s left side.
Step 3: Select the option of “Edit in 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.
By adding IFERROR Function before the actual formula then, it displays the values instead of showing an error.
Method #3 – Evaluating the Formula
If we use nested formulas with an error, then 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 that is 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 the errors that are displayed are true errors. Some of them are false errors displayed only because of formatting issues. Such as, the error sign “###” means that the width of columns needs to be increased.
- Errors can only be resolved if the function’s required syntax is followed.
- Every error that occurs has a different solution and needs different error-handling functions.
- If we are unsure about what type of error has happened, we can use the inbuilt function of excel, which is “= Error.type” to identify the error type.
- The most critical error of excel is “#REF,” In this case, the reference of a cell cannot be identified, and such errors should be avoided only by working more carefully.
- 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.
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 –