1. 0:
- Represents
a digit placeholder. If a number has fewer digits than the number
of zeros in the format, Excel will add leading zeros to meet the length.
- Example:
00000 for
the number 42 will display as 00042.
2. #:
- Also a
digit placeholder, but it does not add leading zeros. It
only displays significant digits.
- Example:
### for
the number 42 will display as 42, but
for 7, it will display as 7.
3. ?:
- This
is a space placeholder for digits in a number format.
- It is
used to align numbers properly, particularly when some numbers have fewer
digits than others.
- It
will insert spaces in place of missing digits.
- Example:
??# for
the number 42 will display as 42, but 7 will
be displayed as 7 (with a leading
space).
4. \:
- The
backslash is an escape character in custom formatting.
- It is
used to display literal characters. If you want to display a character
like a dollar sign ($), you will use \$.
- Example:
\$0.00 will
display $5.00 if the value is 5.
- Displaying Currency, Units, or
Symbols:
- If you
need to include a symbol such as $, €, or % along with your numbers but
don't want Excel to apply the corresponding currency or percentage format,
use the backslash:
- Example:
0.00\% will display 50.00% for the number 50 (without triggering Excel's
percentage formatting).
- Displaying Units in Calculations:
- If
you're using Excel to show units, like kg, m, or hours, you can add these
as literal text in custom formats:
- Example:
0 "kg" will display 5 kg for the number 5.
- Formatting Dates:
- If you
want to display dates in a particular format but include text like
"Date" or other descriptors, you can use the backslash to
prevent Excel from interpreting text as part of the date format:
- Example:
yyyy "Date" dd will show 2025 Date 02 if the date is January 2,
2025.
5. /:
- This
symbol is used as a separator for date and time formatting in
certain custom formats.
- Example:
mm/dd/yyyy to
display the date as 01/02/2025.
- The
forward slash is also used in fractional number formatting.
- It
allows you to format a number as a fraction instead of a decimal.
- You
can also use the forward slash as a literal character in the format.
6. _:
- This
is a space filler character.
- It
adds a space equal to the width of the character that follows it. It’s
commonly used for alignment purposes.
- Example:
0_ will
align a single digit number like 5 with
spaces to its right.
7. "" (Double Quotes):
- Double
quotes are used to enclose literal text in a format.
- If you
want to display specific text along with your number, you enclose it in
double quotes.
- Example:
0
"USD" will display 5 USD for
the value 5.
8. , (Comma):
- The
comma symbol is used as a thousand separator.
- It
separates the thousands in a number format. For example, #,### will
display 1,000 for the value 1000.
- It can
also be used to scale numbers for different units (e.g., millions,
thousands) when used multiple times.
- Example:
#,##0,, will
display 1 for 1,000,000.
9. *:
- This
symbol is used for repeating the character following it to fill the
remaining space in a cell.
- Example:
*# will
repeat the # symbol until the cell
is filled.
Other common symbols in custom
formatting:
10. . (Period):
- The
period is used as a decimal separator.
- It’s
typically used in number formats to separate the integer part from the
fractional part.
- Example:
0.00 will
display 5.25 if the value is 5.25.
11. %:
- The
percent sign is used to multiply a number by 100 and display it as
a percentage.
- Example:
0% will
display 50% for the number 0.5.
12. ; (Semicolon):
- The
semicolon is used to separate different sections of the custom format.
There are typically 4 sections:
1. Positive
numbers
2. Negative
numbers
3. Zero values
4. Text
- Example:
0;[Red]-0;0;"Text" would
format positive numbers normally, negative numbers in red, zero values as 0, and
text as "Text".
13. @ (At symbol):
- This
symbol is used to represent text in custom formatting.
- It
allows you to display text values exactly as they are.
- Example:
@ will
display any text value exactly as it is, like Hello will
show Hello.
By combining these symbols, you can create complex custom formats in
Excel.
Tags
Excel Study