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


Download Practice Workbook

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


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 for the following factors:

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

6 Solutions: Fix #REF! Error in Excel

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 then see 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.

Read More: Excel Error: The Number in This Cell is Formatted as Text (7 Fixes)


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

Have a look 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: REF Error in Excel (9 Suitable Examples)


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

I have added another years’ sales to my dataset. Now if I cut it and paste it to Column C then see at 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-

Related Content: Reasons and Corrections of NAME Error in Excel (10 Examples)


Similar Readings


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 upper than the source cell then the formula changes its references along 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.

Read More: Errors in Excel and Their Meaning (15 Different Errors)


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 for finding 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.

Related Content: [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 workbooks 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 and keep the Replace with box empty.
  • Finally, press Replace All.

Then Excel will remove all the errors.


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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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