Custom Numeric Formatting Symbols

Excel

Excel has custom formatting symbols for just about everything, including colors, text, and special characters. I’d recommend you click on some custom formats in the Type list, then modify them with these custom formatting symbols to see the effect.

Symbol Format Effect
General Unformatted cells. Displays numeric precision to match cell width.
# Placeholder for numbers. 4.678 as #.## appears as 4.68
0 Displays 0 if no number entered. Decimals round up to match the number of 0s to the right of decimal. Place a 0 to the left of decimal to indicate a leading zero. 4.6 as $#.00 appears as $4.60
? Use as placeholders in fractions so that number align. 12.75 as # ??/??  appears as 12 ¾
_ (underscore) Leaves a blank the width of the character following the underscore. Use this to create a trailing blank after positive numbers so they align with negative numbers that are enclosed in parenthesis. #.00_) leaves a space the width of ) on the right side
. (decimal) Locates the decimal point in US systems. #,###.00
, (comma) Locates the thousand separator in US systems. You only need to indicate the first comma. #,###.00
% Shows the entry as a percentage with % in the cell. A decimal number appears in the formula bar.
E  _E  +e  _e+ Displays in scientific notation. The power of the exponent is indicated by one or more 0s or #s to the right.
:  $  _  +  (  ) Displays this character in the same position in the number
/ (slash) Acts as the separator in a fraction.
\ (backslash) Displays a single character or symbol that follows it.
“text” Displays the text that is inside quotes. You can use this as a prefix for identifying a number, e.g., tax ID, “TIN” or other text prefix or suffix. You can also use this as a message for example,#,##0;”Positive numbers only”;”Positive numbers only”Displays the message if a negative number or zero is entered.
*character Fills the cell width with the character(s). This is way to hide a number as it is entered as only the character(s) appear in the cell. You can still do math with the number in the cell, although you must apply a standard numeric format to the result to see it.
@character The character(s) that follow @ appear after the number entered.
[color] Formats the number with color. Color codes are in a following table. Type the square brackets around the color. See the related article for the standard Excel color names such as [Red], [Green], and so on.
[condition value] Sets conditional formats. When the condition is satisfied, then the format is used. This is a remnant from earlier Excel. Now this is more easily done Conditional Formatting.

 

Share the power...