Microsoft Knowledge Base
XL: Character Format of Numbers Changes with AutoFill Feature
Last reviewed: June 3, 1997
Article ID: Q107138
The information in this article applies to:
- Microsoft Excel for Windows, version 5.0
- Microsoft Excel for the Macintosh, version 5.0
- Microsoft Excel for Windows 95, version 7.0
- Microsoft Excel 97 for Windows
SUMMARY
In Microsoft Excel, you can format text to have more than one style in a cell (for example, the first letter of a word can be bold and the rest of the letters can be italic). When you use the AutoFill feature, this mixed formatting is retained.
However, if you use the AutoFill feature to copy numbers which have been formatted as text and that use these mixed formats, you may notice that the formatting is not applied to the numbers consistently.
MORE INFORMATION
This inconsistent formatting occurs because of the way formats are applied to values or characters that change in the AutoFill. With the AutoFill feature, if characters are different from one cell to the next, the formatting of the first character in an item will be applied to all of the characters in that item.
For example, if you type the number 1.10 and format this number as text, the number is interpreted as two items. That is, a text item (1.) which does not change when you use the AutoFill feature, and a number item (10), which does change. Because the text item (1.) never changes, its formatting remains constant; however, because the number item changes (from 10 to 11, 12, 13 and so on), the formatting of the first character ("1") in the number item ("10") is applied to all of the characters in the number item of the filled cells. As a result, after the first cell in the series, each character in the number item is formatted in bold.
Steps to Reproduce Behavior
- In a new worksheet, select cell A1.
- From the Format menu, choose Cells.
- Select the Number tab.
- Under Category, select Text and choose OK.
- In cell A1, type "1.10" (without the quotation marks).
- In cell A1, select the 1 to the right of the decimal point.
- With the 1 to the right of the decimal point highlighted, choose the Bold button on the Formatting toolbar and press ENTER.
- Select cell A1.
- Drag the fill handle down to fill the range A1:A20.
Note that all of the numbers to the right of the decimal point are formatted in bold, even though in the initial number (10) only the '1' is bold.
Additional query words: 5.00 7.00 Last reviewed: June 3, 1997 |