REF Excel Error Message: How to Fix it
Jul 26, 2022
Perhaps you must return to a worksheet you used to work on to gather vital information. Pull the Excel workbook up on your laptop and notice that some formulas show a #REF error.
This article will discuss what causes this error message and how you can resolve it. 
What is #REF? Error in Excel?
A #REF! error occurs when one or more aspects of your formula refer to invalid references.
Why Does the REF Error in Excel Appear?
A #REF error in Excel shows up when you have referenced an aspect of your workbook or sheet that is no longer there. It might've been in a deleted cell or reference range, but whatever it may be, this message will appear.
You could also see a #REF error in Microsoft Excel if you copy a formula with relative references over to another sheet where the column reference is deemed invalid or there are incorrect range references in general.
Read more: How to fix a 'Reference Isn't Valid' Excel error'
Reference Errors Examples
Here are some of the most common examples of when you'll get served a #REF error in Excel. While there are many examples of when you will see a REF error in Excel, these solutions should allow you to eradicate an invalid cell reference and other issues in Microsoft Excel.
The reference has gotten removed.
When a cell, column, worksheet, or row is removed that is referenced within a workbook, the #REF error will appear. 
But, once a cell reference or column reference has gone, the element you are referring to within your formula no longer exists.
To resolve this, you can either replace all the cells you deleted to create a valid reference or amend which cells your formula refers to, ensuring the newly referenced workbook is up to date with the correct value.
Copying a formula with relative cell references
When you have copied a formula from one cell to another, and a #REF error appears, it is likely due to relative references.
That is because Microsoft Excel automatically assumes that by pasting a formula in another column, you also want to refer to corresponding data from that column, and your valid references will be ignored.
To make your reference range more valid, undo the paste action, and add a dollar right to each cell reference.
Other solutions for #REF! Error
In most instances, you can resolve the #REF error in Excel using the outlined options. Unfortunately, there are some situations where it is a little trickier.
For example, what if you deleted a worksheet with multiple #REF errors? Unfortunately, the only way to fix these incorrect range references is to replace the information you deleted.
Clear the #REF! Error from the Worksheet
If you want to clear any #REF errors in Excel from your worksheet rather than fix them, you can use the Find and Replace dialog box to remove any #REF errors.
Hold down CTRL + H and input #REF! Within the Find field. Leave your Replace field blank and select Replace All. 
Trap the #REF! Error
A #REF error in Excel doesn't necessarily require using the IFERROR function to find the error. That said, in some instances, you might want to use it. For example, if you get a #REF error message from adding dynamic references via the INDIRECT function, the IFERROR function can be convenient. 
Related Articles:
How to Insert Line of Best Fit in Google Spreadsheets
Microsoft Excel is Waiting for Another Application to Complete an OLE Action - What Does This Mean?
Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
