• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

#REF! in Excel

April 9, 2021 by Andrew Childress
#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.

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.

#ref in Excel

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

#ref! in Excel

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.

Ref error in Excel

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.

What is the ref error in Excel

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.

Category: Excel TutorialsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Add carriage return in ExcelCarriage Return in Excel
Next Post:Enable Macros in ExcelHow to enable Excel macros

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top