Excel Formula Errors
When dealing with formulas you will get error messages time to time. They may seem similar to you at first glance. But actually every formula error gives you valuable information about what is wrong with your formula.
Lets have a look of what are these error messages and what do they tell us, therefore what do we need to do to correct these errors.
When you see this error message in any cell, it means cell value is longer than the cell width. So you need to arrange how your value is displayed in that cell to get rid of this error. This can be done by:
- Increasing cell width for the cell with error
- Formatting your value to get a shorter display
- Decreasing font size untill value fits in the cell
When you see this error message, it means that you are using wrong type of data for your formula or selecting a range for a function that operates with a single cell. For example if you put a range of cells into a EXACT() function you will get a #VALUE error. To correct this error you need to check your formula to see what went wrong. Select the cell with error than click on the “!” symbol that pops up to the left side. From here click Show Calculation Steps and you will see where the problem is.
This error message indicates that your formula is trying to divide something to zero. Simple as it is. So if there is a problem in the formula, you just need to correct it. Or your formula may be ok but you tend may tend to have zeros as values time to time. In this situation you either going to ignore these error messages or use an iferror function to manage them.
When you see this error message most likely there is a typing error in your formula. It may be a misspelled function name, text without bracelets, etc. So just check your formula to see what is the problematic part. If you can’t locate the problem at a glance, use Show calculation Steps approach mentioned above.
This error message indicates that you are try to use a non-existent value in your formula. It is most likely to happen with lookup formulas. When your formula is looking up for a value but can’t find it, it will result a #N/A error. You can get this error also by using non-existent values as conditions in formulas. How to get rid of them? You need to either add that value to your lookup range or just use an iferror kind of approach to get past it.
This error message indicates that one of the values that is used in your formula is no longer existing. You will most likely get this error message right after you edit something in your workbook like pasting some formulas, deleting sheets,etc. Because the change you made deleted one of the values in your formula. You can use Show calculation Steps approach mentioned above to pinpoint which value you are missing, than take necessary corrective actions.
This error message is likely displayed when result of your formula is either too large or too small for Excel to represent. You may also see this error when you use a non-numeric argument in a function that is looking for a value. You may correct this error by using smaller numbers.
You get this error message when you were specifying multiple ranges in a formula and forgot to put “;” in between. So if your formula looks like =SUM(A1:A5 B1:B5) you will get #NULL error. If you correct it like =SUM(A1:A5;B1:B5) #NULL error will disappear.
There is an exception though. When you forget ; in your multi range formulas but your ranges are intersecting, you will not get an error. Instead excel will make the calculation “only for intersecting values. For example, =SUM(A1:A5 A3:A8) will result as “A3+A4+A5”.
When circular reference occurs, it will not be presented as an error message. Instead excel will display a message (Circular Reference Warning). This means your formula is set up to use it’s own result to give result. Normally this would create an endless loop, but excel simply gives zero as result.
An example of this situation is inputting =A1+B1 formula into cell A1. In this case formula will need a value from A1 to sum with value of B1. Yet value of cell A1 is the result of this formula. This is a clear circular reference.
Note: Sometimes excel will detect inconsistencies in your formulas located in neighboring cells. It will mark them for you to notice and check. This notifications doesn’t necessarily point to errors. But they shouldn’t be discarded since they point to possible errors.
Handling Excel Formula Errors
Excel also gives you options to choose for how do you want errors to be handled in your workbook. To get to this screen navigate to file tab in ribbon. Select options. Then select Formulas. In the lower half of this screen you will find these options.
(File -> Options -> Formulas)
You can enable/disable error checking as a whole or partially from this screen as you see fit.
You can also set how errors displayed on printed worksheets. For more information Displaying Errors and Comments.