#REF! errors are one of the most frustrating things that can happen in an Excel workbook. You open a file, or make a change to a row or column, and suddenly cells that worked perfectly are showing #REF! with no obvious way to fix it.

This guide covers the common causes of #REF! errors, how to fix each one, and practical steps to prevent them in the future.

What a #REF! Error Actually Means

#REF! is short for reference. Excel is telling you that a formula is pointing to a cell, range, or sheet that no longer exists. The formula itself is still there, but its target has disappeared.

The most common causes are:

• Deleting a row or column that contains a cell referenced by another formula
• Deleting a worksheet that a formula refers to
• Pasting over a cell that another formula references
• Breaking a link to an external workbook
• Moving cells in a way that disrupts a range reference

Each cause has a slightly different fix.

Fix 1: Deleted Row or Column

This is the most common cause. You delete a row to clean up your spreadsheet, and suddenly a SUM formula in another cell shows #REF!.

Example:

Cell F10 contains =SUM(F2:F9). You right-click on row 5 and delete it. The formula now shows =SUM(#REF!).

The formula lost its original range reference because a specific cell within that range was deleted, and Excel cannot resolve the adjusted range.

Fix: Edit the formula cell and replace the #REF! part with the correct range. In this example, change =SUM(#REF!) back to =SUM(F2:F8) (the adjusted range after deletion).

If you have many such errors, use Find and Replace. Press Ctrl+H, find #REF! and replace with nothing. This clears all the broken references. Then re-enter the correct ranges for each formula.

Fix 2: Deleted Worksheet

If a formula references another sheet and you delete that sheet, every formula pointing to it will show #REF!.

Example:

Cell A1 on Sheet1 contains =Sheet2!B5. If you delete Sheet2, the formula shows =#REF!.

Fix: There is no way to recover the data from the deleted sheet unless you restore a previous version of the file. However, if the data has been moved to a different sheet, replace the reference manually:

=NewSheet!B5

To prevent this, Excel warns you before deleting a sheet that contains formula references. Pay attention to that warning.

Fix 3: Broken External Workbook Link

If your workbook pulls data from another file and that file is moved, renamed, or deleted, the links break and produce #REF! errors.

Example:

='C:\Data\[Budget.xlsx]Sheet1'!$A$1 shows #REF! when Budget.xlsx is deleted or renamed.

Fix: Go to Data > Edit Links. You will see a list of all external references. You can update the link to point to the new file location, or break the link entirely and replace the values with what is currently cached.

If the source file no longer exists, break the link. Excel will keep the last available values as static data. This is better than leaving broken formulas throughout your workbook.

Fix 4: Copy and Paste Over Existing References

When you copy a cell that other formulas reference and paste over it, the referring formulas lose their target.

Example:

Cell C10 contains =B5. You copy cell A1 and paste it over B5. C10 now shows #REF! because B5 no longer exists as a formula target.

Fix: Paste values only (Ctrl+Shift+V or right-click > Paste Values) instead of pasting over cells that other formulas depend on. If the error has already occurred, undo with Ctrl+Z, then re-paste using Paste Values.

Fix 5: Circular References That Cascade Into #REF!

When Excel encounters a circular reference (a formula that refers back to its own cell), it sometimes resolves the chain in a way that produces #REF!

Example:

A circular reference chain that Excel cannot resolve iteratively will show a message when you open or edit the file. In some cases, Excel breaks the circular chain by converting intermediate references to #REF!.

Fix: Check for circular references by going to Formulas > Error Checking > Circular References. Excel will highlight the first circular reference it finds. Break the loop by redesigning the formula to avoid referencing its own cell.

How to Find All #REF! Errors in a Large Workbook

When a workbook has hundreds of formulas, manually scanning for #REF! errors is impractical. Use these methods instead:

Method 1: Go To Special

• Press F5 (Go To)
• Click Special
• Select Formulas, then uncheck all boxes except Errors
• Excel will highlight every cell containing an error, including #REF!

Method 2: Find and Replace

• Press Ctrl+H
• Find: #REF!
• Replace with: nothing (to clear all references)
• Click Replace All

Use Method 1 to find and review each error one by one. Use Method 2 only when you plan to re-enter all formulas from scratch, because it strips the broken references entirely.

Preventing #REF! Errors

Prevention is better than repair. Here are practical steps to avoid #REF! errors in your workbooks:

Use Excel Tables. When you convert a range to a table (Ctrl+T), formulas that reference table columns adjust automatically when rows or columns are added or deleted.

Hide instead of delete. If you need to remove a row or column temporarily, hide it rather than deleting it. Hidden rows still contribute to formulas. Once you confirm no formulas break, you can delete safely.

Check dependents before deleting. Select the cell or range you plan to delete, then go to Formulas > Trace Dependents. Excel draws arrows to every cell that depends on the selected range. If arrows appear, your formulas will break.

Use INDIRECT for flexible references. The INDIRECT function creates a reference from text, which means it will not break when rows or columns are deleted. =INDIRECT("A1") always points to A1 regardless of what happens to other cells.

Version your workbooks. Save a version before making structural changes. OneDrive and SharePoint keep version history automatically. Some users create a naming convention like model-v1.xlsx, model-v2.xlsx before major edits.

Frequently Asked Questions

What does #REF! mean in Excel?

#REF! stands for 'reference' and means Excel cannot find the cell, range, or sheet that a formula is pointing to. This happens when you delete a cell, row, or column that a formula depends on, when you delete a worksheet, or when a workbook link is broken.

How do I fix a #REF! error in Excel?

Find the cell showing #REF!, look at its formula, and identify which part of the reference is broken. Replace the #REF! part with the correct cell reference. If you have many #REF! errors, use Find and Replace to replace all instances of '#REF!' with nothing, then re-enter the correct references.

Can I undo a #REF! error?

If you just made the change that caused the error, press Ctrl+Z to undo immediately. If you saved and closed the file, undo is not available. Your best option is to restore a previous version if your file is saved in OneDrive or SharePoint with version history.

Why does deleting a row sometimes cause #REF! errors?

When you delete a row that contains cells referenced by formulas elsewhere, those formulas lose their target. For example, if cell A10 has the formula =SUM(A1:A5) and you delete row 3, the formula becomes =SUM(#REF!), which Excel cannot resolve.

How do I prevent #REF! errors when deleting data?

Before deleting rows or columns, check if any formulas reference that range. Use Trace Dependents (Formulas tab, Trace Dependents) to see which cells rely on the range you are deleting. Consider hiding rows instead of deleting them, or convert your data to an Excel Table which adjusts references automatically.

Can I get professional help fixing Excel errors in Sydney?

Yes. If you have a complex workbook with many #REF! errors or spreadsheet errors that are difficult to trace, an Excel consultant can audit and repair the workbook. This is often faster than manually tracing each broken reference, especially in models built by someone else.

When to Get Professional Help

If you are dealing with a complex workbook with interconnected formulas, a few #REF! errors can be time-consuming to trace and fix manually. The cost of an hour of professional Excel consulting is often less than the time it takes to unpick a broken model yourself.

For spreadsheet errors in complex workbooks, a consultant can audit the file, identify all broken references, repair the formulas, and add structural safeguards to prevent recurrence. This is particularly valuable for financial models, budgeting tools, and reporting dashboards that multiple people rely on.

The Excel for Project Management guide has a common errors section that covers other formula issues beyond #REF!, including #VALUE! and #DIV/0!. For a broader overview of building reliable Excel workbooks, see Excel data analysis for business decisions.

Further Reading

For a complete overview of this topic, see the Interactive Business Dashboards in Excel: A Step-by-Step Guide.