XL: Formula Uses Formatting of First Cell in Range

 Last reviewed: February 2, 1998
Article ID: Q131332
The information in this article applies to:
  • Microsoft Excel for Windows, versions 4.0, 5.0, 5.0c
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a
  • Microsoft Excel 98 Macintosh Edition

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.

Microsoft Excel versions 7.0 and later

In Microsoft Excel versions 7.0 and later, the formatting in the first cell is also used by the AutoCalculate value displayed in the status bar. Note that in versions 7.0 and later, 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): 

   SUM=123-45-6799

Additional query words: 4.00 5.00 5.00a 5.00c 7.00 7.00a 97 98 XL98 XL97
XL7 XL5 XL4
Keywords : xlformat xlformula kbualink97
Version : WINDOWS:4.0,5.0,5.0c,7.0,97; MACINTOSH:4.0,5.0,98
Platform : MACINTOSH WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.