If there’s one thing that makes me tremble…it’s seeing a #REF! error in my excel file.
But those aren’t the only errors you may come across…
excel has countless other error messages that will literally scream at you once triggered.
Today we’re going to cover what they each mean, and how you can resolve them
This may be the easiest error that you’ll come across in excel…
it simply means that the data cannot be displayed, because the column is too narrow.
To resolve, simple extend the column
This is a really popular error, and relates to when you have a range in a formula that can no longer be found…
IE it was deleted
To prevent something like this in the future, you can utilize the trace precedents button
This excel error is easy to follow…
it relates to when you try and divide something by 0
Simple trace the reference in the formula, and revise the denominator from 0.
This one is similar to #REF!…
only it’s in the context of lookups, and specifically whenever a match can’t be found (as opposed to a reference being deleted)
To resolve, double check the lookup value in your formula to ensure it exists in your lookup range
This error is also fairly easy to understand from the message it shows..
it’s whenever you have a typo in your formula, or named range.
To resolve, double check the spelling and ensure you’re using the right naming
This error means - you idiot!
Just kidding…that was a joke from my college professor :)
Anyways…this one is whenever you have an incorrect character in a formula or reference
This can be triggered when using certain formulas that are more sensitive to the range over other formulas…for example, when you use arrays in a sumproduct.
To resolve, refer back to the range where the data is contained, and remove any incorrect characters
This error will show whenever you either have an incorrect data type of number format in a formula, or if excel can’t perform an excel calculation.
A popular example here is when you try to take the square root of a negative number in excel.
Similarly, you may come across this error when using IRR or RATE and no result can be found.
To resolve the error here, you can enable iterative calculations, just like you would with circular references.
You may come across this error whenever you include a space in a formula instead of a : or a , between 2 arguments.
I haven’t come across this one too often in my experience, but the resolution is simple - replace the space with a colon or comma
Here’s another one that you may not have come across…
it’s caused when an unspecified calculation error is found within an array.
Array functions are those that return an array or results rather than a specific result. In this case, there’s no answer to return, so you’ll get this error.
To resolve, double check your formula and ensure that your search value can be found
This last one is pretty straight forward, and is reserved for when you try to utilize a spill function.
More specifically, it’s when your spill function is blocked by a value, and the function can’t SPILL into other cells.
To resolve, simply remove the values that are blocking the spill function.
Although these errors can be scary, it’s my hope that with this information you are infinitely more equipped with resolving them.