Format Guide

Excel number formats control how a value is displayed. They do not change the underlying value that Excel uses in formulas, sorting, filtering, or calculations.

That single idea explains most of the feature:

• 1234.567 can display as 1,234.57, 1235, 1.23E+03, or 123456.7%
• The same date can display as 2026-03-19, 19 Mar 2026, or Thursday
• The same time can display as 16:36, 4:36 PM, or 40:36 when shown as a duration

How to read a format string

A custom number format in Excel can contain up to four sections, separated by semicolons:

positive;negative;zero;text

Each section controls how a specific type of value is displayed. When Excel formats a cell, it evaluates the underlying value and applies the first matching section. This means the same value can be displayed differently depending on how the format is structured, without changing the actual data stored in the cell.

You don’t have to define all four sections. Formats can include one, two, three, or four sections. If some sections are missing, Excel automatically falls back to default behavior. For example, with only one section defined, that format is used for all numeric values. With two sections, the first is used for positive and zero values, and the second for negative values.

The fourth section is optional and only applies to text values. To explicitly control how text is displayed, you typically include @, which acts as a placeholder for the original text. If no text section is defined, Excel will still display text normally using its default behavior.

The table below summarizes how Excel interprets the format string depending on how many sections are provided:

Examples

Skipping a section

You can leave a section empty, but you must keep the semicolon. Each semicolon acts as a placeholder so Excel can correctly map values to their sections.

An empty section means: display nothing for that type of value. The underlying value is still stored in the cell—it’s just hidden.

Example: Positive values use 0.00, negative values are hidden, and zero displays as -:

0.00;;-

Sections: positive → 0.00, negative → (empty), zero → -

You can also hide all values in a cell:

;;;

All sections are empty, so nothing is displayed—numbers and text are both hidden.

Basic number formats

Excel's number formatting system is built on a small set of symbols that describe how a value should be displayed. These symbols are called placeholders, and they act as a visual template.

When Excel renders a cell, it takes the stored value and applies your format string to it. This process does not change the value itself — it only changes how the value appears to the user.

Digit placeholders

The most important elements in any format string are the digit placeholders. These define how numbers are structured, rounded, padded, or simplified.

How Excel "fills" a format

A useful way to think about formats is that Excel reads your format string from left to right and fits the number into it. For example:

0.00

• The integer part is placed before the decimal
• Exactly two digits are shown after the decimal
• Missing digits are filled with zeros

Whereas:

#.##

• Only existing digits are shown
• No padding is added
• The result adapts to the value

The difference between 0, #, and ?

These three placeholders look similar but serve different purposes. Try typing the same value into both rows below:

0 — enforce structure and precision

Use 0 when you need a fixed number of digits — especially in financial contexts where values must align and be directly comparable.

# — simplify and reduce visual noise

Use # when readability matters more than strict formatting — it removes unnecessary zeros and adapts dynamically to the value.

? — align values visually

? behaves like # but reserves a blank space where a hidden digit would have been. This keeps decimal points and fraction bars aligned in a column without adding visible zeros.

???.???

Common patterns

These patterns are combinations of the basic placeholders and appear frequently in real-world usage. Rather than memorizing them, focus on understanding how they are constructed from the core tokens.

Display vs. stored value

One of the most important concepts in Excel formatting is that rounding is often only visual. A cell containing 2.3456 formatted as 0.00 displays 2.35 — but the stored value is still 2.3456, and all calculations use the full precision.

Grouping and scaling large numbers

The comma , has two distinct behaviors depending on where it appears in the format string.

When placed inside the digit pattern it adds thousands separators. When placed after the last digit placeholder it scales the displayed value — each comma divides by 1,000.

Percentages

The % symbol changes both meaning and scale. Excel assumes the stored value is already in decimal form — so 0.08 means 8%, and the format multiplies it by 100 before displaying.

Fractions

Fraction formats convert decimal numbers into fractional representations. The number of placeholders on each side of the slash controls how precise the fraction can be.

# ?/?
# ??/??
# ???/???

Scientific notation

Scientific notation is used for very large or very small numbers. The format splits the value into a coefficient and an exponent.

0.00E+00
#0.0E+0

Special characters

So far, number formats have been about shaping numbers — deciding how many digits to show, where decimals go, and how values are grouped.

But in real-world usage, numbers rarely stand alone. You often need to add units, labels, symbols, or visual structure. This is where special characters come in.

Excel allows you to mix numeric placeholders with literal text and layout instructions. The key is understanding how Excel decides what is part of the format logic — and what should be displayed exactly as written.

Adding text to a number with quotes

The most common way to include text in a format is by using double quotes. Anything inside quotes is treated as literal text and displayed exactly as written — Excel does not try to interpret it.

These formats don't change the number itself. They combine the numeric value with a label. This is especially useful for units, currencies, or simple annotations directly in the cell.

Showing characters that normally have meaning by escaping them

Some characters in Excel formats are not just text — they have special meaning. For example, % scales a number and # defines a digit placeholder.

If you want to display these characters literally, use a backslash \ to tell Excel not to interpret the next character.

Working with text values (@)

Number formats don't only apply to numbers — they can also define how text values are displayed. The @ symbol is a placeholder that means: insert the original text here.

If a user enters Sample, the cell displays Text: Sample. The @ acts as a bridge between the raw input and your format.

The underscore _

The underscore inserts a blank space equal to the width of the next character. This solves a real alignment problem: negative numbers shown in parentheses like (123) take more horizontal space than 123. Without adjustment, columns look misaligned.

By adding _( and _), Excel reserves space for the parentheses on positive rows — even when they're not shown. Both rows below use the same format code, which is what makes the columns line up:

Notice how the last row — without alignment — sits visually to the left compared to the rows above it. The number occupies less space than its parenthesised counterpart, so in a real column of mixed positive and negative values it would appear indented and out of step. The underscore closes that gap invisibly.

The asterisk *

The asterisk repeats a character to fill the remaining width of the cell. Instead of inserting a fixed number of characters, Excel dynamically fills the available space.

A common use is pushing the minus sign to the left edge of the cell, with the number remaining right-aligned — a layout pattern common in financial statements:

Understanding literal characters

At this point, it helps to think of every character in a format as belonging to one of three groups:

1. Formatting instructions — define how numbers behave (0, #, %, *, _, @, etc.)
2. Quoted text — always displayed exactly as written ("kg", "Total: ")
3. Escaped characters — forced to display literally using \ (e.g. \#, \%, \:)

Special case: : and /

The characters : and / are commonly used in time and date formats as separators. Outside of those contexts, they can behave inconsistently. If you want to be explicit — or avoid edge cases — it's safer to escape them:

\:
\/

Colors

You can set the text color for any section by placing a color code in square brackets at the very start of that section. The color only applies to the value displayed by that section — it does not affect the cell background or other cells.

[Red]0.00
[Blue]#,##0.00;[Red](#,##0.00)

Named colors (Color 1–8)

Excel defines eight colors that can be referenced by name. These are the only colors with standard names — everything else must use an index number.

Indexed colors (Color 9–56)

Beyond the eight named colors, Excel supports 48 additional indexed colors — for a total palette of 56. These can only be referenced by their index number using [Color N] syntax.

[Color 9]0.00    → Dark Red
[Color 44]0.00   → Gold
[Color 46]0.00   → Orange

A sample of the indexed range:

The full palette runs from Color 1 to Color 56. The format builder lists all available colors when you open the color picker.

Examples

Type a value into the input to see the color applied in the output column:

How color inheritance works

When a section is not explicitly defined, Excel does not simply leave it colorless — it follows a set of inheritance rules:

• Negative and zero sections inherit the color of the positive section when they are not separately defined. If you write [Red]0.00 with only one section, negative and zero values will also display in red.
• The text section always falls back to the application's default text color — it does not inherit the positive color. To color text values you must define the fourth section explicitly, e.g. 0;0;0;[Blue]@.

The color code must come first

The color bracket must be the first element in its section — before any digit placeholders, prefixes, or other tokens.

[Red]0.00

0.00[Red]

Conditions

Conditions allow you to make a format dynamic — instead of always applying the same pattern, Excel can choose a format based on the value in the cell.

This means you can change how numbers look depending on rules like:

• how large or small a value is
• whether it matches a specific number exactly
• whether it falls inside or outside a defined range

Unlike Conditional Formatting, this logic lives inside the number format string itself — no extra rules to manage, no separate panel to open. The condition travels with the format wherever it goes.

How conditions work

A condition is written inside square brackets. When a section also has a color, the color comes first, followed by the condition, then the format pattern:

[Color][condition]format
[Red][<10]0.00

Without a color, the condition alone starts the section. You can use any of the standard comparison operators:

[<10]
[>=100]
[=1]
[<>0]

Each condition acts as a filter: "use this section only if the value satisfies this rule." Excel evaluates sections from left to right and applies the first one whose condition matches.

Section structure with conditions

Conditions can only be placed in the first and second sections. This gives you a structure with a built-in fallback:

[condition1]format1;[condition2]format2;fallback

• First section — used when condition 1 matches
• Second section — used when condition 2 matches
• Third section — fallback if neither condition matches (this is no longer a "zero section" — it is a general catch-all)
• Fourth section — text values, same as always (optional)

Examples

Values up to 100 in red, values above 100 in blue:

[Red][<=100]0;[Blue][>100]0

Below 10 with two decimals in red, 10 and above in green:

[Red][<10]0.00;[Green][>=10]0.00

Singular and plural label — a common trick for unit formatting:

[=1]0" item";0" items"

Three-tier threshold with a fallback:

[Red][<10]"Low";[Green][>=10]"High";"Other"

Values below 10 → Low, values 10 and above → High, anything else (e.g. text or unmatched values) → Other.

What happens to negative numbers

This is one of the most important things to understand about conditional formats: when using conditions, Excel no longer treats negative numbers as a special category. Negative values are simply evaluated against your conditions like any other number.

[<100]0;[>=100]0.00

For this format, -50 matches [<100] and is displayed as -50 — with a minus sign added automatically by Excel, using the first section's pattern.

If no condition matches the value, Excel falls back to the third section (if defined), or applies a default display.

Why custom negative formatting may not work

In a standard two-section format, the second section is dedicated to negative numbers and you can apply styling like parentheses:

0;(0)   → -5 displays as (5)

But in a conditional format, there is no dedicated negative section:

[<100]0;[>=100]0.00

Here, Excel does not apply custom negative styling. It simply prepends a minus sign where needed. If you need special formatting for negative numbers — parentheses, color, or a different pattern — you must handle it explicitly in your conditions:

[Red][<0](0.00);[=0]"-";0.00

Values below 0 → red parentheses, zero → dash, anything else → standard two-decimal display.

Text behavior

Conditions only apply to numeric values. Text entered into a cell is never evaluated against a numeric condition.

• If a fourth section is defined, it is used for text — exactly as in a standard format
• If no fourth section exists, Excel displays text values using its default behavior, regardless of any conditions in the format

When to use conditions

Custom number format conditions work best for lightweight, display-only rules that travel with the format:

• Changing text labels based on value (singular vs. plural, Low / High)
• Applying color changes at simple thresholds
• Defining a fallback display for edge cases
• Keeping formatting logic self-contained in the format string

When to use Conditional Formatting instead

Number format conditions are intentionally limited — they only control how a value is displayed as text. For anything more complex, Excel's dedicated Conditional Formatting feature is the right tool:

Date, time & duration

Excel does not actually store dates and times as separate types. Instead, everything is stored as a number, and formatting decides how that number is interpreted and displayed.

• A date is a whole number — the count of days since Excel's starting point
• A time is a decimal fraction of a day
• A date + time is a single number combining both: integer part for the date, fractional part for the time

How date and time formats are built

A date or time format is assembled by combining tokens. Each token extracts a specific component — year, month, day, hour, minute — from the underlying number and places it in the output.

You can think of the process as:

1. Excel reads the serial number
2. It splits it into components (year, month, day, hour, minute, second)
3. It inserts those components into your format string in the positions you defined

yyyy-mm-dd
↓
2026-03-19

The - characters are just literal separators between the tokens. Everything else is a token that extracts part of the date.

Date tokens

Date tokens extract parts of the calendar date from the serial number:

Tokens combine by being placed in sequence, with any punctuation or spaces as separators. The order you write them is the order they appear in the output — you are not choosing a preset format, you are building one from components:

Time tokens

Time tokens extract the time portion — the fractional part of the serial number. They work on the same principle as date tokens: combine them in order, separated by literal characters like :.

The examples below all use 0.6917 as the input — a fraction representing approximately 16:36 of a day. Try your own decimal values to explore how the time output changes:

Duration and elapsed time

Standard time formats behave like a clock — they reset after 24 hours. A value of 1.125 (27 hours) formatted as hh:mm would display as 03:00 because the display wraps around midnight.

To display true elapsed time without wrapping, wrap the leading token in square brackets:

The default input below is 1.125, which equals exactly 27 hours. Compare what [h]:mm and [mm]:ss show versus a non-bracketed format:

The month vs. minute ambiguity

Whether the month and minute tokens share the same letter depends on your language setting. In English — and several other languages — month and minute both use m and mm, which creates an ambiguity. In other languages, such as German, month uses uppercase M / MM while minute keeps lowercase m / mm, so no ambiguity exists.

Where the ambiguity does exist, Excel resolves which meaning applies based purely on position:

• After h or hh → interpreted as minutes
• Before s or ss → interpreted as minutes
• In any other position → interpreted as month

Mixing date and time

Because date and time come from the same number — the integer part for the date, the fractional part for the time — you can mix date and time tokens freely in a single format string. The examples below use 46100.6917 as the input (March 19, 2026 at approximately 16:36):