Skip to main content
Office
Basket
Sign in
How to correct a #REF! error

# How to correct a #REF! error

The #REF! error shows when a formula refers to a cell that’s not valid . This happens most often when cells that were referenced by formulas get deleted, or pasted over.

## Example - #REF! error caused by deleting a column

The following example uses the formula =SUM(B2,C2,D2) in column E.

If you were to delete column B, C or D it would cause a #REF! error. In this case we'll delete column C (2007 Sales), and the formula now reads =SUM(B2,#REF!,C2). When you use explicit cell references like this (where you reference each cell individually, separated by a comma) and delete a referenced row or column, Excel can’t resolve it, so it returns the #REF! error. This is the primary reason why using explicit cell references in functions is not recommended.

Solution

• If you accidentally deleted rows or columns, you can immediately click the Undo button on the Quick Access Toolbar (or press CTRL+Z) to restore them.

• Adjust the formula so that it uses a range reference instead of individual cells, like =SUM(B2:D2). Now you could delete any column within the sum range and Excel will automatically adjust the formula. You could also use =SUM(B2:B5) for a sum of rows.

## Example - VLOOKUP with incorrect range references

In the following example, =VLOOKUP(A8,A2:D5,5,FALSE) will return a #REF! error because it’s looking for a value to return from column 5, but the reference range is A:D, which is only 4 columns.

Solution

Adjust the range to be larger, or reduce the column lookup value to match the reference range. =VLOOKUP(A8,A2:E5,5,FALSE) would be a valid reference range, as would =VLOOKUP(A8,A2:D5,4,FALSE).

## Example - INDEX with incorrect row or column reference

In this example, the formula =INDEX(B2:E5,5,5) returns a #REF! error because the INDEX range is 4 rows by 4 columns, but the formula is asking to return what’s in the 5th row and 5th column.

Solution

Adjust the row or column references so they're inside the INDEX lookup range. =INDEX(B2:E5,4,4) would return a valid result.

## Example – Referencing a closed workbook with INDIRECT

In the following example, an INDIRECT function is attempting to reference a workbook that’s closed, causing a #REF! error.

Solution

Open the referenced workbook. You'll encounter the same error if you reference a closed workbook with a dynamic array function.

## OLE Issues

If you have used an Object Linking and Embedding (OLE) link that is returning a #REF! error, then start the program that the link is calling.

Note: OLE is a technology that you can use to share information between programs.

## DDE Issues

If you have used a Dynamic Data Exchange (DDE) topic that is returning a #REF! error, first check to make sure you’re referencing the correct topic. If you're still receiving a #REF! error, check your Trust Center Settings for external content as outlined in Block or unblock external content in Office documents.﻿

Note: Dynamic Data Exchange (DDE)is an established protocol for exchanging data between Microsoft Windows-based programs.

## Macro Issues

If a macro enters a function on the worksheet that refers to a cell above the function, and the cell that contains the function is in row 1, the function will return #REF! because there are no cells above row 1. Check the function to see if an argument refers to a cell or range of cells that is not valid. This may require editing the macro in the Visual Basic Editor (VBE) to take that situation into account.

## Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

## See Also

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

×