In Excel's custom format codes, special symbols can be used to control how data is displayed more effectively. Below are some common symbols and their meanings:
1. The “#” Symbol
- Purpose: Displays the number in the cell but does not show insignificant zeros.
- Example: The format code
####.00 displays numbers with two decimal places.
| Original Content |
Format |
Displayed Format |
| 20 |
####.00 |
20.00 |
| 424894 |
####.00 |
424894.00 |
| 166 |
####.00 |
166.00 |
| 326.89 |
####.00 |
326.89 |
2. The “0” Symbol
- Purpose: Displays the number in the cell and pads with zeros if the number of digits is less than specified.
- Example: The format code
00000 displays the number as five digits.
| Original Content |
Format |
Displayed Format |
| 20 |
00000 |
00020 |
| 424894 |
00000 |
424894 |
| 166 |
00000 |
00166 |
| 326.89 |
00000 |
00327 |
3. The “?” Symbol
- Purpose: Displays a space instead of insignificant zeros.
- Example: The format code
????? is used for left-aligned display.
| Original Content |
Format |
Displayed Format |
| 20 |
????? |
20 |
| 424894 |
????? |
424894 |
| 166 |
????? |
166 |
| 326.89 |
????? |
327 |
4. Displaying Text
- Purpose: Displays text in the custom format, enclosed in half-width double quotes.
- Example: The format code
0"KM" adds a unit after the number.
| Original Content |
Format |
Displayed Format |
| 20 |
0″KM” |
20KM |
| 424894 |
0″KM” |
424894KM |
| 166 |
0″KM” |
166KM |
| 326.89 |
0″KM” |
327KM |
5. The Half-Width Comma “,” Symbol
- Purpose: Represents a thousands separator.
- Example: The format code
#,###.00 displays numbers with a thousands separator.
| Original Content |
Format |
Displayed Format |
| 20 |
#,###.00 |
20.00 |
| 424894 |
#,###.01 |
424,894.01 |
| 166 |
#,###.02 |
166.02 |
| 326.89 |
#,###.03 |
326.93 |
6. The Half-Width Exclamation Mark “!” or Backslash “\” Symbol
- Purpose: Forces the display of one character after an exclamation mark or backslash.
- Example: The format code
0\.0 forces a decimal point before the last digit of the number.
| Original Content |
Format |
Displayed Format |
| 20 |
0.0 |
2.0 |
| 424894 |
0.0 |
42489.4 |
| 166 |
0.0 |
16.6 |
| 326.89 |
0.0 |
32.7 |
7. The “@” Symbol
- Purpose: Applies format codes for text.
- Example: The format code
@ten-thousand Hong Kong dollars adds a suffix to each cell's text.
| Original Content |
Format |
Displayed Format |
| Twenty |
@ten-thousand Hong Kong dollars |
HKD 200,000 |
| Forty-two |
@ten-thousand Hong Kong dollars |
HKD 420,000 |
| A lot |
@ten-thousand Hong Kong dollars |
Many tens of thousands of Hong Kong dollars |
| None |
@ten-thousand Hong Kong dollars |
No tens of thousands of Hong Kong dollars |
8. The “M” and “H”
- Purpose: Represents hours and minutes.
- Example: The format code
HH:MM displays the hour and minute portion of time.
| Original Content |
Format |
Displayed Format |
| 12:05 |
HH:MM |
12:05 |
| 0.1 |
HH:MM |
02:24 |
| 166:01:00 |
HH:MM |
22:01 |
| 9:09 |
HH:MM |
09:09 |
9. The “Y”, “M”, “D”
- Purpose: Used to specify date formats.
- Example: The format code
DD/MM/YYYY displays dates.
| Original Content |
Format |
Displayed Format |
| 18691 |
DD/MM/YYYY |
04/03/1951 |
| 161661.16 |
DD/MM/YYYY |
11/08/2342 |
| 163 |
DD/MM/YYYY |
11/06/1900 |
| 11 |
DD/MM/YYYY |
11/01/1900 |
10. The Semicolon “;” Symbol
- Purpose: Used to separate different format sections.
- ExampleThe structure of the format code is: positive-number format; negative-number format; zero-value format; text format.
| Original Content |
Format |
Displayed Format |
| 89 |
[<60]0 Not Qualified;[<80]0 Not Bad;0 Good |
89 Good |
| 60 |
[<60]0 Not Qualified;[<80]0 Not Bad;0 Good |
60 Not Bad |
| 50 |
[<60]0 Not Qualified;[<80]0 Not Bad;0 Good |
50 Not Qualified |
| 11 |
[<60]0 Not Qualified;[<80]0 Not Bad;0 Good |
11 Not Qualified |
Conclusion
By using these custom format codes, you can precisely control how data is displayed in Excel, enhancing the readability and professionalism of reports.
A0007
