Excel’s #Name? Error

The name error in Excel indicates that the named reference does not exist.  Excel allows you to name cells and ranges and use those names in formula.  This is especially useful if you want to refer to cells on another sheet or you need to create an absolute reference (by default a named reference is absolute).

Mistyping a Named Reference

If you mistype a name the #NAME? appears.  One way of avoiding this is to use the F3 function key instead of typing the name.  F3 will show a list of names in your workbook within a dialog box.  Use the F3 key as and when you need to type the named reference within your formula.

name error 1

Another way to avoid mistyping is to select the name in the list of functions and names that appear when you start typing a formula.  You have to know the first letters of the name for this to work for you.

name error 2

Not Storing a Name

A very common reason for the #NAME? error to appear is that the named reference was never created. If you are creating the name by typing into the name box always make sure you press the ENTER key on your keyboard to confirm.  If you don’t, the name will not be stored.

name error 3

Mistyping Formula

If you mistype formula you might also get the #NAME! error.  For example the following versions of  the formula =SUM(A1:A5) would cause the error:

=SUM  (no range, so Excel looks for a name call SUM

=SU(A1:A5)  (the function name has been misspelled)

=SUM(A1A5) (the colon has been omitted between the two cell references)

Not Referring to Text in Formula Properly

When you refer to text in a formula, say in an IF statement, the text must be enclosed in double quotation marks or the #NAME? will appear.

For example


would cause the error, but



Posted by Chester Tugwell

Leave a reply