You see the #REF! error in your Excel worksheet after deleting rows or columns. This error appears because formulas still reference cells that no longer exist. The #REF! error indicates a broken cell reference. This article explains why this happens and shows you how to find and repair all affected formulas.
Key Takeaways: Fixing the #REF! Error
- Go To Special > Formulas > Errors: Selects all cells containing #REF! and other errors for quick review and editing.
- Find and Replace with Ctrl+H: Replaces all #REF! error values with a blank or a zero to clean up your sheet temporarily.
- Trace Dependents arrows: Shows which formulas are feeding into a cell, helping you identify the source of a broken reference.
Why Deleting Data Causes the #REF! Error
Excel formulas use cell addresses like A1 or ranges like B2:B10 to perform calculations. When you delete an entire row or column, any cell that was part of that row or column is removed from the sheet. If a formula elsewhere points to one of those deleted cells, Excel cannot find the reference. It displays #REF! as a placeholder to tell you the link is broken.
This error most often occurs with functions like VLOOKUP, SUM, and INDEX that use explicit cell ranges. Structured references in Excel Tables usually adjust automatically and avoid this problem. The error can cascade, where one #REF! causes another formula that depends on it to also show #REF!.
How Excel Handles Deleted References
Excel tries to adjust formulas when you insert or delete cells within a referenced range. For example, if you delete row 5 from a sum range A1:A10, Excel updates the formula to sum A1:A9. However, if you delete a cell that is the *sole* reference in a formula, such as a VLOOKUP looking for a value in column D and you delete column D, the reference cannot be adjusted and becomes #REF!.
Steps to Find and Fix #REF! Errors
Follow these methods to locate and correct every broken formula in your workbook.
Method 1: Use Go To Special to Select All Errors
- Select your data range or a single cell
Click on any cell within the range you want to check. To search the entire sheet, click just one cell. - Open the Go To Special dialog
Press the F5 key on your keyboard. Then click the Special button at the bottom left of the dialog box. - Select Formulas and check Errors
In the Go To Special dialog, choose the Formulas option. Then, ensure only the Errors checkbox is selected. Click OK. - Review and edit the selected cells
Excel will select every cell containing an error, including #REF!. The formula bar will show the broken formula for the active cell. Edit the formula to point to a valid cell reference.
Method 2: Use Find and Replace to Remove #REF! Values
This method does not fix the underlying formula but cleans the display. It is useful for creating a clean copy for reporting.
- Open the Find and Replace dialog
Press Ctrl+H on your keyboard to open the Find and Replace window. - Enter the find and replace values
In the Find what field, type #REF!. In the Replace with field, leave it blank to show empty cells, or type 0 or “N/A”. - Execute the replace
Click Replace All. A message will tell you how many replacements were made. The #REF! text will be removed, but the formulas remain broken.
Method 3: Audit Formulas with Trace Dependents
- Select a cell showing #REF!
Click on a cell containing the #REF! error. - Open the Formula Auditing tools
Go to the Formulas tab on the ribbon. In the Formula Auditing group, click Trace Dependents. - Follow the blue arrows
Blue arrows will appear, pointing from your selected cell to any formulas that use its result. This helps you see which calculations are affected by the broken link. - Remove arrows
Click Remove Arrows in the Formula Auditing group to clear the arrows from the sheet.
If Your Formulas Still Show #REF! After Repair
Sometimes a simple edit does not resolve the error. Here are specific scenarios and their solutions.
#REF! Error in an Array Formula
Older array formulas entered with Ctrl+Shift+Enter can break if the source data size changes. Select the cell with the error, press F2 to edit, and confirm the formula with Ctrl+Shift+Enter again. For newer dynamic array functions like FILTER or SORT, ensure the range argument points to existing columns.
Named Range References a Deleted Area
Go to Formulas > Name Manager. Look for any names where the Refers To field shows #REF!. Edit the name to point to a valid cell range or delete the name if it is no longer needed.
Linked Data from a Closed Workbook
A formula like ='[Budget.xlsx]Sheet1′!$A$1 will show #REF! if the Budget.xlsx file is moved, renamed, or deleted. Open the source workbook or update the formula link via Data > Queries & Connections > Edit Links.
Preventing #REF! vs. Fixing It: Key Differences
| Item | Preventing #REF! Errors | Fixing #REF! Errors |
|---|---|---|
| Primary Goal | Avoid the error before it happens | Repair formulas after the error appears |
| Best Practice | Use Excel Tables and structured references | Use the Go To Special feature to find errors |
| Time Investment | More time upfront during setup | Time spent reactively searching and editing |
| Data Integrity | Formulas adjust automatically, preserving logic | Risk of manually entering incorrect cell references |
| Recommended For | New sheets and complex models | Legacy files and one-time cleanup tasks |
You can now find and fix #REF! errors caused by deleted rows. Use Go To Special to quickly select every error cell in your sheet. For future sheets, build formulas using entire column references like A:A or structured Table references to prevent this issue. A final tip is to use the IFERROR function to display a custom message like “Data Missing” instead of the #REF! error.