Microsoft KB Archive/214280

= XL2000: Cell That Contains Formula Assumes Formatting of First Cell in Reference Range =

Article ID: 214280

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214280





SYMPTOMS
In Microsoft Excel 2000, when you type a formula that refers to a cell or range of cells, the cell that contains the formula assumes the same format as the first cell in the range that the formula refers to.



CAUSE
The number format of the first cell in the range that the formula refers to is applied to the cell that contains the formula. This behavior also occurs when you click the AutoSum button to sum a range of cells. For example, when you type the following data in your worksheet   A1: $45.00 A2: 3 A3: 2 A4: =SUM(A1:A3) the value $50.00 appears in cell A4 because of the currency number formatting applied to cell A1.

NOTE: The formatting is not dynamic; that is, when you change the number formatting for a cell that is referenced in an existing formula, the formatting of the cell that contains the formula is not changed.

Microsoft Excel Versions 7.0 and Later
In Excel 7.0 and later, the formatting in the first cell is also used by the AutoCalculate value displayed on the status bar.

NOTE: In Excel 7.0 and later, this behavior is more visible than in earlier versions of Excel because of the special number formats: Social Security, Phone Number, and Zip Code. For example, if your worksheet contains the following values   A1: 123456789 A2: 1 A3: 2 A4: 3 A5: 4 and then the Social Security number format is applied to cell A1, your worksheet appears as:   A1: 123-45-6789 A2: 1 A3: 2 A4: 3 A5: 4 When you select the range A1:A5, the status bar displays the following value (assuming that the SUM function is selected for the AutoCalculate feature):

SUM=123-45-6799

Additional query words: XL2000

Keywords: kbprb KB214280

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.