Article ID: 191160
Article Last Modified on 9/11/2002
APPLIES TO
- Microsoft Excel 98 for Macintosh
This article was previously published under Q191160
SYMPTOMS
In Microsoft Excel, when you enter a formula that refers to a cell or range
of cells, the number format applied to the cell that contains the formula
is the same as the formatting applied to the first cell in the range that
the formula refers to.
CAUSE
This behavior is by design of Microsoft Excel. The number format used in
the first cell in the range that you are referencing is applied to the cell
that contains the formula. This behavior also occurs when you use the
AutoSum button to sum a range of cells. For example, when you enter the
following on 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 that 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.
The formatting in the first cell is also used by the AutoCalculate value
displayed in the status bar. Note that in Excel 98, this behavior is more
visible than in earlier versions of Microsoft Excel because of the special
number formats: Social Security, Phone Number, and Zip Code. For example,
if your worksheet contains the following values
A1: 123-45-6789
A2: 1
A3: 2
A4: 3
A5: 4
where the Social Security number format is applied to cell A1, 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):
Additional query words: XL98
Keywords: kbprb KB191160