What are #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 analystThe Analyst Trifecta® GuideThe ultimate guide on how to be a world-class financial analyst. Do you want to be a world-class financial analyst? Are you looking to follow industry-leading best practices and stand out from the crowd? Our process, called The Analyst Trifecta® consists of analytics, presentation & soft skills 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.
Learn more about #REF Excel errors in our free Excel training course.
How to Find #REF Excel 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 SpecialGo To SpecialGo To Special in Excel is an important function for financial modeling spreadsheets. The F5 key opens Go To, select Special Shortcuts for Excel formulas, allowing you to quickly select all cells that meet certain criteria..
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 functionFind and Replace in ExcelLearn how to search in Excel - this step by step tutorial will teach how to Find and Replace in Excel spreadsheets using Ctrl + F shortcut. Excel find and replace allows you to quickly search all cells and formulas in a spreadsheet for all instances that match your search criteria. This guide will cover how to) 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 ResourcesExcel ResourcesLearn Excel online with 100's of free Excel tutorials, resources, guides, cheat sheets, and more! CFI's Excel resources are the best way to learn Excel on your own terms. These guides and articles teach spreadsheet formulas, shortcuts, and functions step-by-step with screenshots, templates, lessons, guides, and more to learn all the most important formulas, functions, and shortcuts.
Additional CFI guides and resources you may find useful include:
- Excel for DummiesExcel for BeginnersThis Excel for beginners guide teaches you everything you need to know about Excel spreadsheets and formulas to perform financial analysis. Watch the Video and learn everything a beginner needs to know from what is Excel, to why do we use, and what are the most important keyboard shortcuts, functions, and formulas
- Excel modeling best practicesExcel Modeling Best PracticesThe following excel modeling best practices allow the user to provide the cleanest and most user-friendly modeling experience. Microsoft Excel is an extremely robust tool. Learning to become an Excel power user is almost mandatory for those in the fields of investment banking, corporate finance, and private equity.
- Excel formulas cheat sheetExcel Waterfall Chart TemplateDownload our free Excel Waterfall Chart Template .xls file and follow our instructions to create your own cash flow waterfall chart in Excel. If you’re working in Excel 2013 or earlier versions, please follow the instructions below as a workaround to build your own chart using the stacked column chart feature
- Financial modeling guideFree Financial Modeling GuideThis financial modeling guide covers Excel tips and best practices on assumptions, drivers, forecasting, linking the three statements, DCF analysis, Excel modeling and much more. Designed to be the best free modeling guide for analysts by using examples and step by step instructions. Investment banking, FP&A, research
- All Excel articlesKnowledgeCFI self-study guides are a great way to improve technical knowledge of finance, accounting, financial modeling, valuation, trading, economics, and more.
