What Do The Formula Error Messages Mean In Excel?

Wed 23rd March 2011

Have you ever wondered what all these Excel error messages #DIV/0!, #VALUE!, #NAME?, #REF!, #NUM!, or #N/A mean? Read this article to discover the meaning behind the message

Have you ever typed a formula into Excel and then been faced with an error message? Most of us have been faced with this situation at one time or another. Sometimes the error code makes sense but at other times it may not be too meaningful. There are only a limited number of these error messages and this article describes what these different error messages mean with examples of how the errors are caused.

Excel formula errors start with a hash symbol followed by some text which depends on the particular error. The error #DIV/0 is probably the only one which is immediately recognisable - you cannot divide by zero, but the other messages such as #NAME? or #REF! are not so clear. We'll describe the six main error messages next.

#DIV/0!
You'll see this error if you try to divide a number by zero, or divide a number by an empty cell. For example if you type =20/0 you'll see this error message, or if you type in the formula =D2/D3 and D3 contains a zero or is empty, then you'll again see this error message.

#VALUE!
This is probably the most commonly generated error. It occurs if you use text instead of a number in certain formula. For example =D2*D3 would give this error if either cell contained text. You could create the same error message by typing the formula =4*"shop" because you are telling Excel to multiply a number by text. You'll see this error if the cell containing text is one of the formula elements. For example suppose cell D2 contains the word Shop and cell D3 contains the number 5. Then if you type =D2+D3 into cell D4 you'll see the error. However if the cell containing text is in a specified range of cells, Excel will regard the cell as having a value 0 and the error will not show. So if you type sum(D2:D3) into cell D4 you see the result as 5.

#NAME?
This message occurs if you enter an incorrect function name, or range name or cell reference. So for example if you mistype "SUM" as "SIM" and type in a function as =SIM(D4:D8) then you'll see this error message, because there is no such function. Similarly if you create one or more range names and then use one in a formula, but misspell it, then the same error message will appear, as the range name you typed does not exist. If you do type in functions frequently, a good tip is to always type a function name in lower case. Provided you've spelt it correctly Excel will auto convert it to upper case when recognized. In addition, in Excel 2003, after you type in the open bracket, then the function name, and then the open bracket symbol, the yellow help text appears, which tells you the function name is spelt correctly and you're on the correct path to use that function. In Excel 2007/2010, after typing the equals symbol and you start typing the function name, a pop down appears listing all the function names starting with what you typed. This also confirms that what you are typing is correct, and so helps avoid this error message appearing.

#REF!
Excel creates this message if a cell reference in a formula has been deleted. For example if your formula is D2+D3 and you later delete row 3, you'll see this error message. This because Excel cannot determine which cell reference to use after the row is deleted. This message will trigger if the deleted cell is one of the components in a formula, but will not trigger if the cell is part of a range, in a similar way to the triggering of the #NAME! error message. So good practice in Excel is not to delete rows or columns, but instead do a cut and paste.

#NUM!
This error occurs if there is a problem with a number in a formula or function. For example this can be caused by a calculation resulting in a number too high or too small for Excel to cope with. Some functions iterate and go through lots of calculations to determine a final answer and these answers can be sometimes out of range. This can also occur if the wrong data type is used in a function which requires numerical data.

#N/A
This error message can indicate other more obscure situations, but commonly occurs in lookup functions where a data match cannot be found. So if you use the lookup value AA3 to look in the index column of a stock list, and there is no such value, this error message will result.

#######
This is not strictly an error message but we'll include this anyway. This occurs if a calculation results in a number is too wide to be displayed. For example a calculation resulting in the value £100,000,000 will show as ##########. To cure this, you need to autofit the column size or manually resize it.

So hopefully you've found this article on Excel error codes of interest. there are six kinds of formula error messages in Excel, and knowing what kind of situation can cause the error is often the first step to take to resolve the error. Interested in finding out more about Excel? You might like to consider attending one of the many training courses available and really expand your Excel skills.

Author is a freelance copywriter. For more information on excel training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1556-what-do-formula-error-messages-mean-in-excel.html