Buy Now
ref-error

REF Excel Error Message: How to Fix it

Jul 26, 2022

Perhaps you need to head back to a worksheet you used to work on to gather some important information. You pull the excel workbook up on your laptop and notice that a couple of the formulas are now showing 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 refers 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.

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.

The #REF Excel Error - How to Find and Fix #REF Errors in Excel | Excelchat

Simply put, once a cell reference or column reference has gone, the element you are referring to within your formula does not exist anymore.

To resolve this, you can either replace all the cells you deleted to create a valid reference, or you can 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 valid once more, 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 options we have outlined. Unfortunately, there are some situations where it is a little trickier.

For example, what if you deleted a worksheet and ended up 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 fixing them, you can utilize 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.

Excel formula: How to fix the #REF! error | Exceljet

Trap the #REF! Error

A #REF error in excel doesn't necessarily require you to use 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 very handy.

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?

What is Apple Numbers For Mac: Everything You Need to Know

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras sed sapien quam. Sed dapibus est id enim facilisis, at posuere turpis adipiscing. Quisque sit amet dui dui.

Call To Action

Stay connected with news and updates!

Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.

We hate SPAM. We will never sell your information, for any reason.