Sign in

Correct a #REF! error

This error occurs when a cell reference is not valid.


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


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


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?

  1. Select the example in this article.

    Important: Do not select the row or column headers.

    selecting an example from help in Excel 2013 for Windows

    Selecting an example from Help

  2. Press CTRL+C.

  3. In Excel, create a blank workbook or worksheet.

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

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












2006 Sales

2007 Sales

2008 Sales






















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.


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

  • Change the formulas, or restore the cells on the worksheet by clicking Undo Button image 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.

Was this information helpful?

How can we improve it?

How can we improve it?

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!