How to Fix the Excel Errors:
#VALUE#REF, and #NAME (Easily)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

If you have spent much time working with formulas in Microsoft Excel, you have run into a few errors.

They all have a specific meaning to help you as the user understand what the problem is.

We are going to look at a few of the more common formula errors and explain what causes them and how to handle them.

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

The #REF error

The #REF error occurs in a formula with an invalid cell reference.

This happens when you delete a range that contains an explicit cell reference in the formula.

It can also happen when you paste another value over it.

For example, if we have a SUM formula with explicit cell references, it is vulnerable to the #REF error.

sum-ref-error

Notice in the previous image two different methods to sum the values in cells B3, C3, and D3. The first method uses explicit cell references while method 2 uses a range reference.

Kasper Langmann, Co-founder of Spreadsheeto

Now we will delete column C.

ref-error-sum

Our formula in method 1 now returns a #REF error.

The SUM formula in method 2 adapts to the missing value and recalculates based on the new cell range.

Note the formula in method 1 indicates the #REF error for the explicit cell reference that we deleted.

This comparison demonstrates why you should avoid explicit cell references. Yet, in some types of formulas, there is not another option.

The #REF error can occur in VLOOKUP formulas as well.

In the following example, we have a table that we have created a lookup for sales by quarter for a given SKU.

vlookup-setup

If we take a closer look at the actual VLOOKUP formula, we can see the problem.

vlookup-formula

The table_array argument in the VLOOKUP formula is 5 columns (B2:F15). However, the col_index_num argument is 6.

Since col_index_num cannot be a value greater than 5 in this case, this results in the #REF error.

There are two ways to fix this.

Either adjust the table_array argument or change the col_index_num argument.

This can be a pitfall of VLOOKUP formulas. Use caution when you delete columns from a range that serves as the table_array argument.

Kasper Langmann, Co-founder of Spreadsheeto

The #VALUE error

A variety of causes can lead to the #VALUE error. It depends on the kind of formula you have.

We will look at one of the simpler scenarios that cause this error.

If adding individual cells together the long way, we will get the #VALUE error if any of the values is the wrong data type.

But if we create a SUM formula using the range of cells instead, the formula will ignore any wrong data types.

sum-value-error

The #NAME error

The main reason you would ever see the #NAME error is because of a mistyped formula name.

Consider the following example.

formula-typo

In this example, the user it attempting to use the COUNTIF function. Note the misspelling, “COUUNTIF.”

When the user presses enter, they do not get the result they were expecting.

name-error

In this case, the fix is correcting the spelling of the formula name.

Kasper Langmann, Co-founder of Spreadsheeto
name-error-fix

Circular references

Another common error you may experience in Excel is the circular reference. The cause of this error is a formula is trying to calculate itself.

Imagine you have a few values and you want to calculate the total. You create a simple SUM formula.

But you select the cell of your formula within the sum range.

self-sum

When you hit enter, you get a pop-up message you have never seen before.

circular-error

If you click OK and do not correct your sum range reference, a zero value results.

circular-error-zero

If for any reason you cannot trace your error, there is a method to help you.

Kasper Langmann, Co-founder of Spreadsheeto

Go to the Formula Auditing Group on the Formulas tab.

Click the down arrow to the right of Error Checking and hover over Circular References.

This will tell you which cell contains the error.

Conclusion

We have touched on a handful of the more common errors users will encounter in Excel.

Hopefully, this clarifies the causes of these errors so you can better identify, avoid, and correct them.