How to Fix #REF! Error in Excel (6 Solutions)

Get FREE Advanced Excel Exercises with Solutions!

While working in Excel, facing the #REF! error is one of the most common errors. But no worries, we are here to rescue you from those errors. This article will explain to you the reasons for the error and will provide you with 6 quick ways to fix the #REF! error in Excel.


What Is #REF! Error in Excel?

The #REF! error stands for the wrong reference. You will get this error when the cell reference is no longer valid or does not exist.

It can happen because of the following factors:

  • If you delete a Row, column, or sheet.
  • If you give incorrect or invalid cell references in a formula.

Read More: Errors in Excel and Their Meaning


How to Fix #REF! Error in Excel: 6 Effective Solutions

Let’s get introduced to our dataset first. It represents some salespersons’ sales in different years.


1. Fix #REF! Error with Correct Cell Reference in Excel

Here I have used a formula to sum up the total sales for every salesperson.

Fix #REF! with Correct Cell Reference in Excel

But when I deleted Column C and then saw what happened, created the #REF! error. Because the references from the column are missing, that’s why it is showing the #REF! error.

Fix #REF! with Correct Cell Reference in Excel

Solution:

  • Press Ctrl+Z to undo your operation or write the formula again with corrections.

2. Fix Tab Reference to Remove #REF! Error in Excel

Have a look at that, I have used another sheet reference to calculate the total sales. The sheet is named ‘2019’.

Fix Tab Reference to Remove #REF! Error in Excel

Let’s delete the sheet and see what happens.

Fix Tab Reference to Remove #REF! Error in Excel

It created #REF! error because that sheet is missing.

Fix Tab Reference to Remove #REF! Error in Excel

Solution:

  • You can’t recover a sheet after deletion. So close the Excel file if you haven’t saved it yet and reopen it.
  • Or erase the reference which you used from the deleted sheet.

Read More: How to Find Reference Errors in Excel


3. Fix Cutting and Pasting Issues to Solve #REF! Error in Excel

I have added another year’s sales to my dataset. Now if I cut it and paste it into Column C then see in the next image what happened.

Fix Cutting and Pasting Issues to Delete #REF! Error in Excel

It’s showing #REF! error.

Fix Cutting and Pasting Issues to Delete #REF! Error in Excel

Solution:

  • Undo the operation by pressing Ctrl+Z.
  • Or copy instead of cutting to fix the #REF! error in Excel.

Output after copying-


4. Fix Relative Reference to Solve #REF! Error in Excel

In Cell E6 I have added an extra amount by using the cell reference G1. It’s working, no problem here.

Fix Relative Reference to Delete #REF! Error in Excel

But when I copied Cell E6 to G5 then #REF! Error occurred. It happens because if you copy a formula to a cell higher than the source cell then the formula changes its references upward too. But there is no cell above G1 that’s why the error occurred.

Fix Relative Reference to Delete #REF! Error in Excel

Solution:

  • Use absolute reference for that kind of cell and then copy the formula to fix the #REF! error in Excel.

Fix Relative Reference to Delete #REF! Error in Excel

Now it worked, the error is gone.


5. Fix VLOOKUP Invalid Reference to Erase #REF! Error in Excel

Here, we’ll explain the #REF! error with the VLOOKUP function. I have used the VLOOKUP function to find the sales for Aron but it is showing #REF! error.

Fix VLOOKUP Invalid Reference to Erase #REF! Error in Excel

The reason is I have placed the wrong column index number- 5. But my array has only 4 columns.

Fix VLOOKUP Invalid Reference to Erase #REF! Error in Excel

Solution:

  • Give the correct column index number- 2,3, or 4. I put 4.

Read More: [Fixed!] Excel Found a Problem with One or More Formula References in This Worksheet


6. Fix INDIRECT Invalid Reference to Erase #REF! Error in Excel

Here I have used the INDIRECT function and cell references from another workbook sheet named ‘Sales’.

Fix INDIRECT Invalid Reference to Erase #REF! Error in Excel

Now let’s close that workbook.

Fix INDIRECT Invalid Reference to Erase #REF! Error in Excel

Then do another operation, like I have calculated the sum total for every year. Soon after you will see that the previous formula caused #REF! error.

Solution:

  • Reopen that workbook to fix the #REF! error in Excel.

How to Clear Multiple #REF! Errors

In column E there is multiple #REF! Errors. If you want to remove them at a time then use the Find and Replace tool.

How to Clear Multiple #REF! Errors

Steps:

  • Select the column and press Ctrl+H and the Find and Replace dialog box will appear.
  • Then type #REF! In the Find what box keep the Replace with box empty.
  • Finally, press Replace All.

Then Excel will remove all the errors.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Conclusion

I hope the procedures described above will be good enough to fix the #REF! error in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo