Errors in Excel (Table of Contents)
Errors in Excel
Just like any other software Excel often produces errors, however, the errors that are produced by Excel are often the errors of the user in inserting the data or asking the 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 function to add two cells then Excel expect that the cells would have numbers. If either of the cells has text in it, it would give an error. Every Excel function comes with its own terms and conditions and if any of the condition of the function is voided then there exist 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 about the 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
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!”.
Similarly the “#NULL!” happens if the user enters 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. If we are using the function of sum then Excel assumes that we will give reference of cells that have a numeric entry in those cells. If we have given 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 error is being corrected from the source but in second option error exists, however, the error gets masked with some other value by 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 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 result length is more than the width of the column.
#VALUE!: This happens when a value of the two cells are 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 error.
By adding IFERROR Function before the actual formula then it displays the values instead of showing error.
Method #3 – Evaluating the Formula.
If in case we are using nested formulas and if the formula has an error then the complete formula will end up giving an error.
This is the Image which 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 About Errors in Excel
- 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 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 function.
- 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 case of nested excel formulas, 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 –
