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 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. 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
 Press CTRL+C.
 Create a blank workbook or worksheet.
 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.
 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.

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