Excel: Using Underscores in Custom Number Formats

• Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0
• Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0
• Microsoft Excel for OS/2, version 3.0

SUMMARY

In Microsoft Excel, when you create a custom number format that contains an even number of underscore characters after the digit placeholders followed by another character such as a closing parenthesis, the character will appear in the formatted number.

CAUSE

In Microsoft Excel versions 3.0 and later, one of the special formatting characters used in a custom number format is the underscore character "_" (without the quotation marks). This character acts as a placeholder, leaving a space in the number format that is the width of the character that follows. For example, a custom number format of "#,##0_)" will leave a space that is exactly the width of the parentheses at the end of the number. This formatting is useful when displaying negative numbers in parentheses and also desiring the digits to align with positive numbers in the same column.

Using an even number of underscores does not leave extra space in the number format equal to the width of the last character in the format. When Excel encounters the first underscore character, it will leave space in the number format for the following character. Therefore, if there are two underline characters followed by ")", the width of the second underline character is left after the digits and the ")" will be displayed.

WORKAROUND

To prevent the ")" from appearing in the format and add extra spaces, use an odd number of underscore characters.

STATUS

This behavior is by design.

MORE INFORMATION

The amount of space left by the underscore character is dependent upon the font being used. If the font is proportional such as Microsoft SanSerif or Helvetica, the characters "W" and ")" will leave different size spaces. If the font is non-proportional, such as Courier, the two characters will leave the same size spaces.

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, page 226

"Microsoft Excel User's Guide," version 3.0, page 192

Additional query words: underscore underline

Keywords :
Version :
Platform :
Issue type :