Excel #REF! Error: What It Means and How to Fix It

The Excel #REF! error occurs when a reference in Excel is invalid. This usually happens because sheets, columns, or rows have been removed. In this tutorial, you’ll learn what the #REF! error means and how to fix it. Because of how common this error is, having a good understanding of the error can make you a stronger Excel user.

By the end of this tutorial, you’ll have learned:

• What the #REF! error is and why it happens
• How to fix the #REF! error in Excel
• How to find cells impacted by the #REF! error

Understanding the Excel #REF! Error

Specifically, the error occurs when a formula references an item on a worksheet or workbook that doesn’t exist anymore. In most cases, the error occurs after the fact. By this, I mean that formula once worked, the workbook was subsequently modified, and the error occurred due to a reference being deleted.

The Excel #REF! Error Caused by References Being Deleted

Let’s take a look at how the Excel #REF! error is caused:

1. We’ll create a cell that calculates the sum of values from cells A2:A6.

2. We’ll then delete row 5, removing part of the range that’s referenced by Excel.

3. The cell that previously held our total now raises an #REF! error

We can see that as soon as the row was deleted that the error was raised. Intuitively, this makes sense as Excel can no longer find the values in the expected range. In many ways, Excel safeguards us from incorrectly calculating values based on changed references.

The Excel #REF! Error Caused by Relative References When Copying Data

Another cause of the #REF! error is copying a formula that contains relative references. Let’s take another look at our example scenario above:

1. We’ll create a cell that calculates the sum of values from cells A2 through A6 using the formula =A2+A3+A4+A5+A6.

2. We then copy the cell that contains our formula and paste it into another location.
3. Once we paste the formula into the cell, the #REF! error is raised.

In this case, Excel expects to paste a different formula. Because of this, it safeguards the user by raising the error, rather than attempting to change the range of values.

Now that you have a strong understanding of why the error occurs, let’s take a look at how we can fix the error.

How to Fix the Excel #REF! Error

Let’s take a look at how to fix the #REF! error raised by the two causes above:

1. #REF! error caused by deleted references
2. #REF! error caused by copying relative references

How to Fix the Excel #REF! Error Caused by Deleted References

The only way to fix the Excel #REF! error, once it has been raised, is to fix the reference in the formula itself. Recall from our example above that we had calculated the sum of values in cells from A2:A6. The error was raised when the row was deleted.

In order to resolve the #REF! error caused by this, we need to fix the range that’s contained in the formula. If we inspect the formula itself, we can see that the formula now looks like this:

=A2+A3+A4+#REF!+A5

We can see in the formula above that in the place of where A5 used to be, the #REF! value has been inserted. Because we deleted row 5, Excel can’t find the original reference.

In order to resolve this error, we need to modify the formula. In this case: this means rewriting it to include the correct values. If we still want to calculate the sum of values from A2 through A6, we can simply write:

=A2+A3+A4+A5+A6

This removes the error and calculates the correct sum.

How to Fix the Excel #REF! Error Caused by Copied Relative References

The other cause of the #REF! error is when copying cells containing a formula with relative references. Recall from our example that when we pasted the formula into a cell, that the #REF! error was raised.

This occurs because Excel attempts to modify the references to the adjusted reference. The error occurs when the relative values are outside of the possible range.

We can easily resolve this error by modifying the original formula to use absolute references. For example, if we modify our formula to be:

=SUM($A$2:$A$6)

By using absolute references, we can copy and paste the cell freely without raising an error.

How to Find Cells with the Excel #REF! Error

Now that you have developed a strong understanding of what causes the #REF! error in Excel and how you can fix it, let’s focus on how you can find cells that contain the #REF! error.

Find Cells with the #REF! Error Using Go To Special

Excel makes finding values simple by using the Go To command, which can be accessed using the F5 key. From there, you can select Special, which is also referred to as Go To Special.

From there, you can simply select the Errors selection and click on the OK button. This will highlight every cell that contains the error in the workbook.

Find Cells with the #REF! Error Using Find

Another way of finding every cell that contains the #REF! error in Excel is to use the Find command. In order to access this, simply use the keyboard shortcut CTRL + F, which will open the Find menu. Alternatively, you can select the Find command from the Edit menu, selecting Find under the Find tab.

When the Find menu opens, you can search for #REF and select Find All. Doing this, will open show a new menu of all the locations of where the error occurs in the workbook.

Understanding the #REF! Error in VLOOKUP

Another common occurrence for the #REF! error is in the VLOOKUP() function. Let’s see how the error occurs when using a VLOOKUP function.

In the example above, we have an array of values in cells A1:B7. Let’s see how the error can be caused:

1. In another cell, write the following formula: =VLOOKUP(E2,A1:B7, 3, FALSE)
2. When you hit Return, the #REF! error is caused

Let’s explore why this happens. Because our array only has two columns, entering that we want it to return a value from the third column is impossible.

So, how do we fix this error? We need to be mindful of the references we’re using. This means that we have two options:

1. We can modify the array to include the correct number of columns, or
2. We can modify the column index value to be included in the array

In the final section, below, you’ll learn how to handle the Excel #REF! error with the IFERROR() function.

Handling the Excel #REF! Error with IFERROR

If you want to handle or suppress the #REF! error in Excel, you can wrap the formula in the IFERROR() function. The function takes two arguments:

1. value= the value which may result in an error
2. value_if_error=, which clarifies what value to return if an error occurs

If we know that an error may occur and we want to prevent it from causing issues, we can use this function to handle the error. We need to be mindful, however, that we’re aware of an error having occurred.

If you want to use the IFERROR() function to catch an error, you can simply include your original function call in the IFERROR() function.

Conclusion

In this tutorial, you learned all about the Excel #REF! error. The error occurs when an existing reference cannot be found. You first learned what causes the error and how to fix it. Specifically, you learned how to resolve it when references were deleted and when relative references are copied incorrectly.

Then, you learned how to find cells that have the error. From there, you learned how to work with the error in Excel VLOOKUP functions. Finally, you learned how to handle the error using the Excel IFERROR function.

Additional Resources

To learn more about related topics, check out the resources below:

• Excel #NUM! Error: What It Means and How to Fix it
• Excel #DIV/0 Error: What It Means and How to Fix it
• How to Ignore #N/A Error in Excel SUMIF() (#DIV/0 etc.)