Show All

Hide All

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

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

Causes

• You may have deleted cells that were referred to by other formulas, or you may have pasted cells that you moved on top of cells that were referred to by other formulas.
• You may have used 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.

• You may have linked 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.

• You may have run a macro 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.

How do I copy an example?

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 , 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.

• Change the formulas, or restore the cells on the worksheet by clicking Undo on the Quick Access Toolbar immediately after you delete or paste the cells.
• Start the program that is called for by an Object Linking and Embedding (OLE) link.
• Make sure that you are using the correct Dynamic Data Exchange (DDE) topic.
• Check the function to see if an argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) refers to a cell or range of cells that is not valid. For example, 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.

• Can you make a cell = 0 if original function is creating a"#ref!"?
• REF# error on summing ranges