9 Excel Error in Formula and How to correct it

Do you feel agitated when you are engrossed in setting up formulas in your EXCEL and suddenly you see jargon of excel error like #NAME, #REF appearing in your excel spreadsheet? [This post on Excel tips and tricks is just a 5 min read]

Here’s a list of top 9 mistakes that one makes while entering an Excel formula + tips on how to avoid them:

1. Excel Error – #NAME? 

A famous phrase says, “What’s in the name!” – well, that’s not always true. Because if it’s #NAME – then it could be a problem.

NAME-Excel-Error

You may have noticed, #NAME? error several times and this can appear for various reasons. The most common being misspelt formula [In the above example the formula SUM is misspelt as SUMS].
Here are few more common reasons which one could look out and avoid this error:

  •  A formula that doesn’t exist.
  • Misspelt references and ranges.
  • Text entered without enclosing double quotation marks.
  • Range defined without colon (:)

2. Excel Error – #REF!

This type of error occurs basically when one makes an invalid cell reference. Let’s take an example. Say we have 3 columns which have few values.

Dataset-for-Excel-Error

Now when I delete column E, then the range “E1:E3” referred in the formula is no longer valid and will produce the #REF! error.

REF-Excel-Error

Few more common reasons which one could look out and avoid this error:
• Copy/paste cells above the cells referred by formula, thus making an invalid cell reference.
• Using OLE (Object Linking and Embedding) link to a program that’s no longer valid or not running.

Advanced Excel training program to boost your MIS productivity

3. Excel Error – #DIV/0!

A simple mathematical theory says any number when divided by zero produces an infinity. EXCEL does this by producing #DIV/0! error. Thus, in EXCEL whenever the formula attempts to divide by a zero this error would appear.

Div0-Excel-Error

In the above example, we are attempting to divide C1 (46) by D1 (0) and hence the excel error.

The other common reason which one could look out to avoid this kind of an error:
• There could be a chance that you have left divisor cell empty then the same error would appear. For example, in the below scenario, the divisor (D1) was empty and hence the error.

Excel-Error-Div0

4. Excel Error – #N/A

There are several reasons for this error to occur. But the most generic one is passing an unavailable value to the formula. As the below example shows, that an inappropriate value ‘Z’ is being passed to VLOOKUP function and hence the error, whereas for the same case if you pass an appropriate value i.e. ‘A’ or ‘B’ or ‘C’ to VLOOKUP function, you get respective values instead of #N/A error.

There are several reasons for this error to occur. But the most generic one is passing an unavailable value to the formula

The other reasons which one could look out to avoid this kind of an error:
• Missing data.
• Inappropriate lookup value being passed to VLOOKUP, HLOOKUP or MATCH function.
• Use of N/A or NA as a part of data.

5. Excel Error – #NULL!

This type of error occurs when you try to provide a range that does not intersect each other. In the below example, we have specified two different ranges to the formula. But since these two ranges are independent of each other and have no intersection, it results in #NULL! Error.

NULL-Excel-Error

Given the same scenario, if the range specified were SUM (B1: B3 A1: C1), it would have resulted in a value. This is due to a fact that an intersection is happening between two ranges at the cell B1. Given below is an example of a valid range with an intersection point. The same has been missing in the above example and hence the NULL error.

Excel-Error-On-NULL

The other common reason which one could look out to avoid this kind of an error:
• When the cell ranges are defined improperly like missing colons(: ) or space instead of mathematical operators.

Try our Free online Excel course to speed up your work

6. Excel Error – #NUM!

The general trend for #NUM! error to occur is when an EXCEL comes across a large value.

Excel-Error-On-NUM

In the above example, the calculation is too large for EXCEL to handle and hence it returns #NUM! error.
The other common reason which one could look out to avoid this kind of an error:
• Passing wrong data/data type to a function. Like SQRT (4) function returns 2 but using the same function for negative values doesn’t make much sense and EXCEL, in this case, will return #NUM! error.

7. Excel Error – #VALUE!

This error occurs when the variables specified to a function are of wrong types.

Excel-Error-On-VALUE

In the above example, two values are being multiplied while one being numeric (‘2’ in cell B1), the other being a char (‘B’ in cell C1). This results in a mismatch of data type and hence the #VALUE! error.
The other common reason which one could look out to avoid this kind of an error:
• Use of mathematical formula like SUM, MUL etc… which takes input as a number but chars or strings are being passed to it.

Learn about formula auditing in Excel, Solve problem areas in excel formula

8. Excel Error – ###### 

This error occurs when your EXCEL column is not wide enough to accommodate a large value. For example, if a long number like 123123456456789789 is set to occupy a small cell then it would show up as #### error.
Widening the cell in can eradicate the error in above case but there are few more scenarios where this error can appear:
• Result doesn’t fit in the cell.
• If a negative number has been formatted to date or time then it will also produce this error. The reason being date and time are always supposed to be positive.

9. Excel Error – Circular Reference

This error is rarest of all the errors to occur but can get you tangled in the web. This basically occurs when you define a result on a cell, which is also a part of one of your formula. For example, in perfect conditions, I would plan to add values from range A2 to A6 with its total at cell A7. This would work just like any other normal formula function.
But what if I specify the result at cell A6 instead of A7. A6 being part of SUM range itself. Then I get into circular references and this error might lead to wrong results.

Circular-reference-Excel-Error

The best way to overcome this to assign the results to a different cell and try to keep values and results as different as possible.

The Green Triangle:

If you see a green triangle appearing on the top-left corner of the resultant cell, it’s an indication that something has gone wrong with the formula. If you are not sure of the error, simply click the error cell. An ! mark with a drop-down arrow will appear.

Green-Triangle-Excel-Error

This will point out the nature of the error and will also give you further options for evaluation.
If you are still not sure or having a difficult time locating an error, you can use Trace Error or Circular Reference option [navigate to FORMULAS tab -> Error Checking dropdown -> Trace Error | Circular Reference option].

Trace-Error-in-Formula-Excel

This will point out the error and the cell and you can then determine as to what went wrong.

With this, I think I have listed out the 9 basic formula entry mistakes that people generally make and tips to how and where these mistakes could occur and how to avoid such mistakes. And above all, even if you get any of these errors do not forget the Green Triangle.

Do you face formula errors or you have alternate solutions which I have missed? Comment now!

Leave a Reply