#REF Excel

How to find and fix #REF Excel errors.

#REF Excel errors

A #REF! error (the “ref” stands for reference) is the message Excel displays when a formula references a cell that no longer exists, usually caused by deleting cells that a formula is referring to.  Every good financial analyst knows how to find and fix #REF Excel errors, which will explain in detail below.

 

#REF example

Below is an example of how you may inadvertently create a #REF Excel error.  To learn more, watch our free Excel course.

The first image shows three numbers being added together.  In column D we show the formula adding cells D3, D4, and D5 together.

 

Before #REF error

 

The next image shows what happens if row 4 is deleted.  When the entire row is deleted, the formula that references cell D4 is no longer able to locate that cell, and it produces a #REF! error in the spreadsheet.

 

#REF Excel Error

 

Learn more about #REF Excel errors in our free Excel training course.

 

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis. 

Browse all of CFI’s Excel courses to take your career to the next level!

How to find #REF errors

Method #1

A fast way to find all #REF Excel errors is to press F5 (Go To) and then click on Special, which for short is referred to as Go To Special.

When the Go To Special menu appear, select Formulas, and then check only the box that says Errors.  Click OK and that will automatically take you to every cell that has a #REF! error in it.

Method #2

Another method is to press Ctrl + F (known as the Excel find function) and then type “#REF!” in the Find field and click Find All.  This will highlight every cell with the error in it.

 

How to fix #REF Excel errors

The best method is to press Ctrl + F (known as the find function) and then select the tab that says Replace.  Type “#REF!” in the Find field and leave the Replace field empty, then press Replace All.  This will remove any #REF Excel errors from formulas.

 

More Excel resources from CFI

We hope this has been a helpful guide on #REF Excel errors.  If you want to a master of Excel please check out all our Excel Resources to learn all the most important formulas, functions, and shortcuts.

Additional guides and resources you may find useful include: