#REF! in Excel

You’ve created a formula in Excel, but it’s returning a #REF! error. What can you do? Let’s learn what the #REF! error means in Excel.

What is the #REF! Error in Excel?

You open a spreadsheet, to find your carefully-created formulas returning the unhelpful #REF! value. This is especially common in shared spreadsheets, where you might have multiple users of the file. But what does it mean?

#REF!, as you might have guessed, is short for #REFERENCE. That’s really a clue to the problem with your formula. This error occurs when a formula references a cell that no longer exists.

Consider this simple formula: =A1+A2+A3. It’s adding three numbers together. The result is 15, as you can see.

Now, delete row 2, which will delete cell A2. Your formula won’t change, so it will still reference the now-missing cell A2. Notice, instead of returning a numerical value, the cell now displays #REF!.

How to Resolve the #REF! Error in Excel

Now that you understand what the #REF! error tells you, you’re likely wondering how to correct it. This is especially true if you have a document full of them. It could take a long time to manually rebuild formulas, and there’s no reason to do so.

To correct #REF! errors, you need to remove #REF! from the formulas. The simplest way is to go to the Find & Select dropdown on the right side of Excel’s Home tab. Click on the dropdown, and choose Replace.

Under Find what: type in #REF!. Leave the Replace with: box blank. What you’re doing is telling Excel to delete any instance of #REF! that it locates, including in formulas. Click on Replace All. Your formulas will correct, to work only with the data that they can capture.

Don’t fret the next time you find a #REF! error in Excel. As you can see, it’s easy to understand and even easier to resolve quickly.