Reference errors and troubleshooting in Excel - Step-by-Step Guide

Introduction

Reference errors happen when a formula points to a cell or range that Excel cannot find. This feature helps you understand why these errors appear and how to fix them so your formulas work correctly.

When a formula shows #REF! instead of a number or text

When you delete a cell or column that a formula uses and want to fix the formula

When copying formulas and references change unexpectedly

When you want to check if a formula refers to the correct cells

When troubleshooting broken links in formulas after moving or renaming sheets

Steps

Step 1: Click

- cell showing the #REF! error

The formula bar displays the formula with the broken reference

💡 Look carefully at the formula to see which part has #REF!

Step 2: Select

- the formula bar

You can edit the formula to fix or replace the broken reference

💡 Replace #REF! with the correct cell or range address

Step 3: Click

- Formulas tab

Formula auditing tools become visible

💡 Use these tools to trace and understand references

Step 4: Click

- Trace Precedents button in the Formula Auditing group

Arrows appear showing which cells feed into the formula

💡 This helps find missing or incorrect references

Step 5: Click

- Error Checking button in the Formula Auditing group

Excel shows a dialog explaining the error and suggests fixes

💡 Follow the suggestions to correct the formula

Step 6: Right-click

- the cell with the error

A menu appears with options including 'Show Calculation Steps' or 'Edit Formula'

💡 Use these options to step through and fix the formula

Before vs After

Before

Cell C2 shows #REF! error because the referenced column B was deleted

After

Cell C2 shows the correct sum after updating the formula to reference column A instead

Settings Reference

Error Checking Rules

📍 Formulas tab > Error Checking > Error Checking Options

Controls which types of formula errors Excel checks automatically

Default: Enable background error checking

Formula Calculation Mode

📍 Formulas tab > Calculation group > Calculation Options

Determines when Excel recalculates formulas, which can affect error detection

Default: Automatic

Common Mistakes

Deleting cells or columns without updating formulas

This causes formulas to lose their references and show #REF! errors

Update or adjust formulas before deleting referenced cells or use Insert/Delete commands carefully

Copying formulas without fixing relative references

References shift and may point to wrong cells, causing errors

Use absolute references (with $ signs) to keep references fixed when copying

Ignoring Excel’s error checking prompts

You miss helpful hints that can quickly fix reference errors

Use the Error Checking tool and follow its suggestions

Summary

Reference errors happen when formulas point to missing or deleted cells.

Use Excel’s formula auditing tools to find and fix broken references.

Always check and update formulas when changing or moving data to avoid errors.