Cell linked to text-formatted cell shows formula not value

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q135506
In Microsoft Excel, if you enter a formula that links one cell to a cellthat is formatted with the Text number format, the cell that contains thelink is also formatted as text. If you then edit the formula in the linkedcell, the formula is displayed in the cell rather than the value that isreturned by the formula.
When you link cells in Microsoft Excel, the format of the original cell isapplied to the linked cell. Therefore, if you enter a formula that links onecell to a cell formatted as text, the cell with the formula is alsoformatted as text.
To display the calculated value rather than the formula, you must change the format of the cell containing the formula and re-enter the formula. To do this, follow these steps:

  1. Select the cell with the formula, and then click Cells on the Format menu.
  2. Click the Number tab. Select the format code that you want the cell to display, and then click OK. The cell still displays the formula.
  3. Select the cell with the formula, press F2, and then press ENTER. The cell displays the value.
  4. In the same cell, click Cells on the Format menu. The cell format category will be Text.
  5. Specify the category and format options for the cell and then click OK.
5.00c 8.00 97 XL97 XL7 XL5 XL

Article ID: 135506 - Last Review: 02/07/2007 00:28:47 - Revision: 1.4

Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Excel 95 Standard Edition, Microsoft Excel 5.0 Standard Edition

  • kbinfo KB135506