Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
An #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 we will explain in detail below.
#REF Excel Error Example
Below is an example of how you may inadvertently create a #REF Excel error. To learn more, watch CFI’s free Excel course and follow the video instruction.
The first image shows three numbers being added together (5, 54, and 16). In column D we show the formula adding cells D3, D4, and D5 together, to get 75.
The next image shows what happens if row 4 is deleted. When the entire row is deleted, the formula that referenced cell D4 is no longer able to locate that cell, and it produces a #REF error in the spreadsheet.
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 appears, 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.
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 and thus fix the problem.
As the above screenshot shows, after typing the error message you are experiencing in the “Find what” filed, leave “Replace with” blank and press “Replace All” in the bottom left corner. This will cause the reference errors to be deleted from your formulas.
More Excel Resources from CFI
We hope this has been a helpful CFI guide on Excel errors. If you want to be a master of Excel, please check out all our Excel Resources to learn the most important formulas, functions, and shortcuts.
Additional CFI guides and resources you may find useful include:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.