Correct a #REF! error

This error occurs when a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) is not valid.

Symptom

Microsoft Excel displays #REF! in one or more cells on a worksheet.

Causes

  • Cells may have been deleted that were referred to by other formulas, or cells may have been pasted on top of other cells that were referred to by other formulas.
  • There may be an Object Linking and Embedding (OLE) link to a program that is not running.

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

  • There may be a link to a Dynamic Data Exchange (DDE) topic (a group or category of data in the server part of a client/server application), such as "system," that is not available.

 Note   DDE is an established protocol for exchanging data between Microsoft Windows-based programs.

  • There may be a macro in the workbook that enters a function on the worksheet that returns a #REF! error.

Example

Copy the example data to a blank worksheet, and then delete column D (the entire column). The formulas, which were originally in column E, shift to column D and they all display a #REF! error. If you select cell D2, Excel displays the formula =SUM(B2,C2,#REF!) in the formula bar.

ShowHow do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.

Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.


 
1
2
3
4
5
A B C D E
Region 2006 Sales 2007 Sales 2008 Sales Formula
East 22700 24200 11000 =SUM(B2,C2,D2)
North 17400 17800 10200 =SUM(B3,C3,D3)
South 18600 18500 9600 =SUM(B4,C4,D4)
West 21800 23300 10300 =SUM(B5,C5,D5)

An error is displayed because the formulas in column E referred to column D and, because column D was deleted, the formula is no longer valid. Instead of repairing the formulas to refer to a different cell — which may not be what you want anyway — Excel displays this error to prompt you to correct the formulas yourself so that you don't get unexpected results. In this case, you repair the formulas by removing ",#REF!" from the formula in D2, and then dragging the formula down to the cells below.

Resolution

  • Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error Button image, click Show Calculation Steps if it appears, and then click the resolution that is appropriate for your data.

 Tip   Review the following resolutions to help determine which option to click.

 
 
Applies to:
Excel 2010