Formula Errors in Excel and Solutions

While applying a formula that results in an excel defined errors (#NA, #VALUE, #NAME etc.) is called excel formula errors. These errors are caught by excel and printed on the sheets. Reasons for these errors can be, unavailable values, incorrect types of arguments, division by 0, etc. They are easy to catch and fix.

Logical errors are not caught by Excel and they are most difficult to fix. Inconsistency in data, wrong data entry, human errors etc. are a common reasons behind these errors. They can be fixed too but they take time and effort. It is better to prepare your data perfectly before doing operation on them.

#NA error occurs in excel when a value is not found. It simply means NOT AVAILABLE. #NA error is often encountered with Excel VLOOKUP function.

In above image, we are getting #NA error when we look for “Divya” in the A column. This is because “Divya is not in the list.

If you are sure that the lookup value must exist in the lookup list then the first thing you should do is check the lookup value. Check if the lookup values are correctly spelled. If not then correct it.

The #VALUE occurs when the supplied argument is not of a supported type.

For example, if you try to add two texts using arithmetic plus operator (+), you will get a #VALUE error. The same thing will happen if you try to get a year of an invalid date format using the YEAR function.

to catch and do something else.

The ‘ref’ in #REF stands for reference. This error occurs when a formula refers to a location that does not exist. This happens when we delete cells from ranges to which a formula refers too.

In the below gif, the sum formula refers to A2 and B2. When I delete A2, the formula turns into a #REF error.

The best thing is to be careful before deleting cells in data. Make sure that no formula refers to that cell.

If you have #REF error already then trace then delete it from the formula.

For example, once you get a #REF error, your formula will look like this.

You can just remove the #REF! From the formula to get an error-free formula. If you want to do it in bulk then use find and replace feature.

Press CTRL+H to open find and replace. In find box, write #REF. Leave replace box empty. Hit Replace All button.

If you want to readjust the reference to a new cell then do it manually and replace the #REF! With that valid reference.

===

The #NAME occurs in excel when it can’t identify a text in a formula.

For example, if you misspell a function’s name, excel will show the #NAME error. If a formula refers to a name that does not exist on the sheet, it will show #NAME error.

In the above image, the cell B2 has formula =POWERS(A2,2). POWERS is not a valid function in excel, hence it returns a #NAME error.

In cell B3 we have =SUM(numbers). The SUM is a valid function of excel but “numbers”

To avoid #NAME error in excel always spell function names correctly. You can use excel suggestions to be sure that you are using a valid function. Whenever we type characters after the equals sign, excel show functions and named ranges on the sheet, starting from that character/s.

Scroll down to function name or range name in the suggestion list press tab to use that function.

As the name suggests, this error occurs when a formula results in division by zero. This error may also occur when you delete some value from a cell on which a division formula is dependent.

This can be easily solved by being careful with data and putting a check if a formula will result in #DIV/0! error. Here is a sample formula to do so.

We will have DIV/0 error only if the divisor is 0 or blank. Hence we check the divisor (B2), if its a zero then print A2 else divide A2 with B2. This will work for blank cells too.

This error occurs when a number can’t be displayed on the screen. The reason can be that the number is too small or too big to be displayed.

Another reason could be that a calculation can’t be performed with given number.

This will return 4. If you want to get the negative value then use the negative sign before the function. Then of course you can use error handling function of course.

On solving #NUM error we have a dedicated article. You can check it here.

This is a rare type of error. #NULL error caused by incorrect cell referencing. For example if you want to give reference of a range A2:A5 in SUM function, but by mistake you type A2 A5. This will generate #NULL error. As you can see in below image, the formula returns #NULL error.

If you replace space with column (:) then the #NULL error will be gone and you’ll get sum of the A2:A5. If you replace space with comma (,) , you will get sum of A2 and A5.

As we know that the #NULL error is caused by typo. To avoid it, try to select ranges using curser instead of typing it manually.

There will be times when you have to type range address from keyboard.

Always take care of the connecting symbol column (:) or comma (,) to avoid.

If you have existing #NULL error then check the references. Most probably you have missed column (:) or comma (,) between two cell references. Replace them with appropriate symbol and you are good to go.

Sometimes we think that this error is caused due to insufficient space to show a value, but this is not the case. In this case you can just extend the width of the cell to view the value in cell. I won’t call it an error.

Before doing arithmetic calculations in excel with time value keep these things in mind.

The minimum time value is 1/1/1900 00:00. You can’t have a valid date before this in excel 1 is equal to 24 hours (1 day) in excel. While subtracting hours, minutes and seconds, convert them into equivalent values. For example, to subtract 1 hour from 12:21 PM, you need to subtract 1/24 from it.

Now we know what common excel formulas are and why they occur. We also discussed what possible solution can be for each type of excel errors.

Sometimes in excel reports, we get errors and we couldn’t know from where the error is actually occuring. It gets hard to solve these kind of errors. To track down these errors excel provides error tracing functionality in formula tab.

Logical errors are tough to find and resolve. No massage shown by excel when you have a logical error in your function. Everything looks fine.

But only you know that there something is wrong there. For example, while getting percentage of a part of whole, you would divide part by total (=(part/total)*100). It should be always equal or less then 100%.

When you get more than 100%, you know something is wrong.

This was a simple logical error. But sometimes your data comes from several sources, in that case it gets hard to solve logical problems.

One way is to evaluate your formula.

In formula tab, evaluate formula option is available. Select you formula and click on it. Each step of your calculation will be show to you that leads to your final result. Here you can check where the problem has occurred or where the calculation has gone wrong.

You can also yous trace dependents and precedents to see on which references your formula depends and what formulas depends on a perticular cell.

So yeah guys, these were some common error types every excel user faces.