Are you ready to get back to basics today with some refreshment of the meaning of the Excel Formula errors that you get sometimes in your worksheets?.  It’s time to review, and if you are new here then maybe learn just what those pesky (or helpful, depending on how you want to look at them) errors actually mean.  Let’s talk each error through with an easy to follow example.

Understanding Formula Errors.

Writing Excel formulas can be east and it can be a bit more challenging.  It just depends on your level experience and what you are trying to achieve.  Sometimes Excel will return an error rather than the value or result you were expecting.  Excel is actually being rather helpful with its error signalling.  it will display 1 of 7 possible errors.  Once you know what the errors mean, or indicate you are on your way to knowing how to fix your problem.

Here are the seven errors that Excel will display. Each has an easy to follow example with it to clarify the issue.

The Seven Errors In Excel.

#VALUE. This just means that you are using the incorrect data type. Here is an example.

#NUM!. Typically this is due to the result of an invalid argument in a mathematical or trig function.  If you enter a negative rather than an expected positive number or when a formula or function contains numeric values that aren’t valid. Below I have attempted to find the square root of the number -2.  This throws an error as the expected value for a square root is a positive number.

#REF. If you get this error, then this means your formula contains an invalid cell reference.  In my experience, this is due to deleting of a row or column to which the formula referred to.  It could also mean that the formula is referring to a cell that does not exist.  For example A1048577.  In the example below I have deleted Column E which has affected my formula.

Nearly There…Just A few more Errors To Cover!

#DIV/0!.  This error means that your formula is trying to divide a value by zero.  There is no way mathematically to do this.  So an error is returned.  This error is also returned if you try to divide by an empty cell.  The example below shows the value in C3(10) being divided by the value in D2 which is blank.

#NAME?.  This is really straightforward.  It means Excel does not recognise a name you have used in formula as being valid.  This could one or more than one objects that are incorrect.  It could range from a formula or function or sheet name or a mistyped cell reference.  In the example VLOOKUP formula below I have mistyped the last argument in the function.  FALS instead of FALSE.

#N/A.  This error means that Excel is unable to return a legitimate result.   A good example of this is if a VLOOKUP formula cannot return an exact match, then #N/A is returned.  In the example below I have tried to find a value for the month of Apr.  No match can be found.

#NULL!. Simply explained this means that the formula uses an intersection of two ranges that do not intersect. Or when the two or more cell references are separated incorrectly or unintentionally by a space in a formula.  You can see below where there is a space inserted in the range of cells in the formula.

What’s Next?

Do you want more Excel formula fun.  How about some VBA and Macro Fun as well?.

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Formula Friday series. Click on the link below

How To Excel At Excel – Formula Friday Blog Posts.