**How to Fix the Excel Errors:**

#VALUE, #REF, and #NAME (Easily)

#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.

**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.

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.

Now we will **delete column C**.

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.

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

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.

**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.

**The #NAME error**

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

Consider the following example.

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.

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

**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.

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

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

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

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.