There are three common reasons why you may encounter the #NAME? error in Excel:
1. You mistyped a formula.
2. You left out a colon in a range reference.
3. You left out quotation marks for text values.
The following examples show how to fix each error in practice.
Example 1: You Mistyped a Formula
Suppose we attempt to calculate the average value in column B using the following formula:
=AVERAG(B2:B15) We will receive a #NAME? error since we mistyped AVERAGE in the formula:
To correct this error, we simply need to make sure we spell the formula correctly:
=AVERAGE(B2:B15) This time we’re able to successfully calculate the average value in column B without an error:
Example 2: You Left Out a Colon in a Range Reference
Suppose we attempt to count the number of teams with a value of “Thunder” in column A using the following formula:
=COUNTIF(A2A15, "Thunder")
We will receive a #NAME? error since we left out a colon in the range reference:
To correct this error, we simply need to make sure we include a colon in the range reference:
=COUNTIF(A2:A15, "Thunder") This time we’re able to successfully count the number of teams with a value of “Thunder” in column A without an error:
Example 3: You Left Out Quotation Marks for Text Values
Suppose we attempt to concatenate the values in column A and column B using the following formula:
=CONCAT(A2, scored, B2)
We will receive a #NAME? error since we left out quotation marks around “scored” in the formula:
To correct this error, we simply need to make sure we include quotation marks around “scored” in the formula:
=CONCAT(A2, " scored ", B2)
This time we’re able to successfully concatenate the values in column A and column B without an error:
Additional Resources
The following tutorials explain how to resolve other common errors in Excel:
How to Ignore #N/A Values When Using Formulas in Excel
How to Replace #N/A Values in Excel
How to Ignore #DIV/0! When Using Division in Excel