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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

6. Excel Error – #NUM!

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

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.

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.

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 referencesand this error might lead to wrong results.

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.

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].

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!

About Us

We offer instructional videos, practice files, and a personalized learning dashboard that empower an individual to master use cases of the software at their own pace anytime anywhere. We work with 20+ industry experts who double up as award-winning instructors for our courses.