Correct a #REF! error
This error occurs when a cell reference 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 Windowsbased 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.
How do I copy an example?

Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help

Press CTRL+C.

In Excel, create a blank workbook or worksheet.

In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.

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.


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