excel number format placeholder cheat sheet
EXCEL NUMBER FORMAT PLACEHOLDER CHEAT SHEET
Description Value Format Formatted
----------------------------------------------------------------------
General Default format 1234.567 General 1234.567
# Placeholder for digits 1234.567 #####.#### 1234.567
(numbers) and does not
add any leading zeroes
0 Placeholder for digits 1234.567 00000.0000 01234.5670
(numbers) and add any
leading zeroes
? Placeholder for digits 1234.567 ?????.???? 1234.567
(numbers) and add space
characters
. Placeholder for the 1234.567 0.00 1234.57
decimal place
_ Adds a blank space to 99 _(#_);(#) 99
the width of the -25 _(#_);(#) (25)
following character. 58 _(#_);(#) 58
Use in combination with 12 _(#_);(#) 12
parentheses to add left -71 _(#_);(#) (71)
and right indents, 36 _(#_);(#) 36
_( and _) respectively -99 _(#_);(#) (99)
* Repeats the character 66 0 *! 66 !!!!!!!!
after the asterisk FullName @ *_ FullName __
until the width of the Item @*. Item.......
cell is filled 0 @*= 0==========
% Convert value to a 0.12 % 12%
percentage with 0.125 0.0% 12.5%
% sign 0.1275 0.00% 12.75%
, Thousands separator 1234.567 #, 1
12345678 #, 12,346
12345678 #,###, 12,346
12345678 #,, 12
E Scientific notation 1234.567 0.00E+00 1.23E+03
format. Requires a ‘+’
symbol after, and a
digit placeholder
before and after
/ Represents fractions 1.234 # ##/## 1 11/47
1.234 # 000/000 1 117/500
1.234z ##/## 58/47
"" Text placeholder for 1234.567 #,##0 "km/h" 1,235 km/h
multiple characters Good "Result is: "@ Result is: Good
\ Text placeholder for 1234 #.00, \K 1.23 K
single character 1234567 #.00,, \M 1.23 M
@ Placeholder for text Bad "Result is: "@ Result is: Bad
[color] Change Color of value: [Green]#,##0.00_);[Red](#,##0.00);[Blue]0.00_);[Magenta]@
Options:
[Black], [Green], 1234.567 Prints in GREEN
[White], [Blue], -1234.567 Prints in RED
[Magenta], [Yellow], 0 Prints in BLUE
[Cyan], [Red] This is a text Prints in MAGENTA