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?
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 that has been set for the execution.
Errors occur when we insert some formula in Excel and miss to add the required input in the expected forms, suppose if 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 in it, it will give an error. Every Excel function comes with its own terms and conditions, and if any of the conditions of the function are voided, then there are Excel errors. Every function has a syntax, and this 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 we understand the functions. These displayed errors tell us a lot of things. With the proper understanding of Excel errors, one can easily solve those errors.
Examples of Errors in Excel
- The “#Name?” error.
- The “#div/0!” error.
- The “#NULL!” error.
- The “N/A” error.
- The “#Value!” error.
- The “#NUM!” error.
Explanations of Errors in Excel
Excel errors shown in the above examples are of different types and occurs because of different reasons, and hence this becomes important to identify the main cause that is behind the error to quickly correct the error.
The error of “#Name?” is because the formula that is typed is incorrect. For example, entering =su(A1:A2) instead of =Sum(A1:A2) will give the error of “#Name?” and if we ask Excel to divide any number by zero, we will get the error of “DIV/0!”.
4.9 (8,697 ratings)
View Course
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 not valid or not supposed to be referred to. If we are using the function of sum, then Excel assumes that we will reference cells with a numeric entry in those cells. If we have given a reference of cells that have text, then we will get “#Value!”
“#NUM!” error happens when the displayed result is something that is not valid. Suppose if we have entered a function to multiply 999999999999 with 999999999999999, then the resulting number will be so long which will be not valid, and we will get the “#NUM!” error.
How to Correct Errors in Excel?
Errors in Excel is very simple and easy 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 being 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 that has an error function in it.
Step 2: Click on the little warning sign that is displayed on the left-hand side of the cell.
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 in case the formula is not typed correctly.
#DIV/0!: This error happens if a value is asked to divide by zero.
#REF!: If in case of missing reference, this error will happen.
#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.
######: This is not an error; this means that 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 in case the resulting value is not valid.
Method #2 – Masking the error using error handling functions.
Step 1: Go to the cell that has an error function in it.
Step 2: Click on the little warning sign that is displayed on the left-hand side of the cell.
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 are using nested formulas and if the formula has 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 resolved.
Things to Remember
- Not all the errors that are displayed are true errors. Some of them are false errors that are displayed only because of formatting issues. Such as the error sign “###” means that the width of columns needs to be increased.
- Errors can be only be solved if the required syntax of the function is followed.
- Every error that occurs has a different solution and needs different error handling functions.
- If in case we are not sure about what type of error has happened, we can use the inbuilt function of excel that is “= Error.type” to identify the error type.
- The most critical error of excel is “#REF”, as in this case, the reference of a cell cannot be identified, and such type of 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 so that we 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 –
