errors in excel

What is an error in Excel?

An error in Excel occurs when a formula or function gives an unexpected result or an error message. Excel contains many built-in error values that indicate a mistake with a formula or function's syntax, reference, or calculation. These errors show as a hash symbol # followed by an error code, such as #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, or ######. Each error number denotes a certain type of issue.

Why does an Error occur?

Errors in Excel can occur for numerous reasons, including:-

• Syntax errors: When the formula or function is not correctly written, Excel will return an error message. For example, a missing bracket or comma can cause a syntax error.
• Reference errors: When the formula or function refers to an invalid cell, range, or worksheet, Excel will produce a reference error. For example, if a formula references a cell that has been deleted or relocated, Excel will generate a #REF! error.
• Calculation errors: When the formula or function attempts to divide by zero or conduct an improper mathematical operation, Excel will return a calculation error. For example, if a formula divides a number by zero, Excel will produce a #DIV/0! error.
• Data errors: When the data in the formula or function is not correct, Excel will return a data error. For example, if a formula tries to perform a mathematical operation on text input, Excel will throw a #VALUE! error.

Types of Error in Excel

These are the various types of errors in Excel:-

1) #DIV/0 Error

• The #DIV/0! error occurs when a `formula attempts to divide a number by zero.
• This error can occur when you create a formula that references a cell that contains zero or is blank.
• For example, if you divide a number by a cell that contains zero or is blank, Excel will return a #DIV/0! error. Example

• = A1/B1
• If B1 contains zero, Excel will return #DIV/0! error. How to resolve the error? = IF(B1=0, "N/A", A1/B1)
• This formula will check if B1 is equal to zero. If it is, then it will return "N/A." Otherwise, it will perform the division and return the result.

2) #N/A Error:

• The #N/A error occurs when a formula or function cannot find the value it is looking for.
• This error can occur when you use a lookup function, such as VLOOKUP or HLOOKUP, and the lookup value is not present in the lookup range. Example

• = VLOOKUP(A1, B1:C10, 2, FALSE)
• If the value in cell A1 does not exist in the range B1, Excel will return #N/A How to resolve the error?
• = IFERROR(VLOOKUP(A1, B1:C10, 2, FALSE), "Not Found")
• This formula will perform the VLOOKUP function and check if it returns an error.
• If it does, it will return "Not Found" instead of the error message.

3) #NAME? Error

• The #NAME? The error occurs when Excel cannot recognize a formula or function.
• This error can occur when you misspell a function name or use an undefined name in a formula.
• To resolve this error, you can check the spelling of the function or name and correct any errors.

Example

• Here we misspelled the Sum function
• So it showed the #NAME? error.

How to resolve the error?

• SUM(A1:A3)
• This function uses the correct function name so the error is resolved.

4) #NULL! Error

• When incorrect cell references are supplied, this error is frequently shown.
• This error occurs when the space character is not used correctly.
• The "intersect operator," represented by the space character, specifies the ranges that cross at any cell.
• This error can also occur when you use a space instead of a colon to reference a range.

Example

• If you use a space instead of a colon between A1 and A3, Excel will return #NULL! error.

How to resolve the error?

• = SUM(A1:A3)
• This formula uses a colon to reference the range A1 and sum the values in the range.

5) #NUM! Error

• The #NUM! error occurs when a formula or function returns a value that is too large or too small to be displayed in Excel.
• This error can occur when you use a function that exceeds the maximum or minimum limit of Excel's calculation.
• This error can also occur when negative values are given to a function that takes positive values like the sqrt function.

Example

• Here, we can see that we provided a negative value to the sqrt() function.

• Here the power() function returns a value that is too large for the cell.

How to resolve the error?

• By providing appropriate values in functions that require positive values only
• By breaking down the complex calculations so that cell overflow does not happen.

6) #REF! Error

• When a cell reference in an Excel worksheet is invalid, the error message #REF! is displayed.
• This mistake happens when a cell that is used in a formula or function is deleted, moved, or renamed.
• Formulas and functions in Excel are calculated using cell references, if the cell reference is invalid, Excel is unable to conduct the computation and returns the #REF! error.

Example

• Here, we deleted the cell at D2 which was referenced in the formula
• This causes the error to show up

How to resolve the error?

• To fix this error we should take the following steps
  • Check the formula for errors
  • Find the invalid references
  • Update the references

7) #VALUE! Error

• The #VALUE! error occurs when a formula or function refers to data that is not valid.
• This error can occur when you use a function that requires numeric data, but the data is text or non-numeric.

Example

• Here we try to add cells A1 and B1, but the formula expects numeric data type and we have received Text data in B1 which causes the error to pop up.

How to resolve the error?

• = IF(ISNUMBER(A1), A1, 0) + IF(ISNUMBER(B1), B1, 0)
• This formula uses the IF function to check if A1 and B1 contain numeric data. If they do, the formula adds the values. If they don't, the formula returns 0.

8) ###### Error

• The ###### error occurs when a cell contains a value that is too wide to fit within the column width.
• This error can occur when you enter a date or time value that is too long to fit within the cell.

Example

• Here, we have selected the data to be viewed as 4 March 2023 which doesn't fit in the cell and causes the error to pop up.

How to resolve the error?

• We can adjust the width of the column to avoid this error
• We can select the date format to view as 4/3/2023.

9) Circular Reference Error

• The Circular Reference error occurs when a formula refers to itself, either directly or indirectly.
• This error can occur when you create a formula that includes the cell where the formula is located, which causes Excel to get stuck in an infinite loop.

Example

• Here we can see that the formula is in cell D1 and the formula is referencing D1 as a parameter.
• This causes the Circular Reference Error.

How to resolve the Error?

• Change the formula so that this referencing does not take place.
• Shift the formula to another cell, or reference the result of that cell in another cell as a parameter.

Function to Deal with Excel Errors

Several functions in Excel might assist you in dealing with various types of errors in Excel in your formulae and functions. Here are a few examples:-

1. IFERROR Function: You can swap out error values for a given value using the IFERROR function. Here's an example:-
   • =IFERROR(VLOOKUP(A1, B1:C10, 2, FALSE), "Not Found")
   • The VLOOKUP function is used in this formula to search for a value within a range.
   • The formula gives "Not Found" rather than an error message if the value cannot be found.
2. ISERROR Function: This function enables you to determine whether a value represents an error. Here's an example:
   • =IF(ISERROR(A1/B1), "Error", A1/B1)
   • This formula divides cell A1's value by cell B1's value.
   • The formula returns "Error" if the division is incorrect. If not, the division's outcome is returned by the formula.
3. ERROR.TYPE Function: This function helps you to identify the type of error in a cell. Here's an example:
   • IF(ERROR.TYPE(A1)=3, "Name Error," A1 + B1)
   • The mistake type in cell A1 is verified using this formula.
   • The formula returns "Name Error" if the error type is #NAME? Otherwise, the formula adds the values in cells A1 and B1.