EDUCBA

Home Excel Excel Resources Excel Tips Errors in Excel

Errors in Excel

By Madhuri ThakurMadhuri Thakur

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

Errors in Excel (Table of Contents)

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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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.

Errors

Types of Errors in Excel (Examples)

1 – “#Name?” error.

Errors Example 1

2 – “#div/0!” error.

Errors Example 2

3 – “#NULL!” error.

Errors Example 3

4 – “N/A” error.

Errors Example 4

5 – “#Value!” error.

Errors Example 5

6 – “#NUM!” error.

Errors Example 6

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.

You can download this Errors Excel Template here – Errors Excel Template

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.

Correct Errors Method 1

Step 2: Click on the little warning sign displayed on the cell’s left-hand side.

Correct Errors Method 1-1

Step 3: Check what error happened.

Invalid Name Error 1-2

Step 4: Try to get help from the inbuilt data.

Help on this Error 1-3

Step 5: After checking what kind of error has happened, correct the error from the source, and the error will be solved.

SUM Formula 1-4

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.

Correct Errors Method 2

Step 2: Click on the little warning sign on the cell’s left side.

Correct Errors Method 2-1

Step 3: Select the option of “Edit in formula bar.”

Correct Errors Method 2-2

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.

IFERROR Formula 2-3

By adding IFERROR Function before the actual formula then, it displays the values instead of showing an error.

Adding IFERROR Function 2-4

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.

Method 3

Step 1: Click on the cell that has an error.

Correct Errors Method 3-1

From the ribbon, click on the “Formulas” option.

Formulas option 3-2

Step 2: Click on Evaluate formula option.

Evaluate formula option 3-3

Step 3: Click on Evaluate.

Click Evaluate 3-4

Step 4: Keep clicking on evaluate formula until we get the function that is causing the error.

Correct Errors Method 3-5

Step 5: Correct the formula that is creating an error.

Correct Errors Method 3-6

After correcting the formula, we get the correct answer.

Correct Errors Method 3-7

Drag the formula by using Ctrl + D.

Correct Errors Method 3-8

Step 6:The error will be fixed.

Method 3-9

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.

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 –

  1. IFERROR Excel Function
  2. Excel VALUE Function
  3. WEEKDAY Function Excel
  4. Compare Dates in Excel
7 Shares
Share
Tweet
Share