Conditional Number Formats

Excel Formulas › Advanced

All versionsFormatting

Put conditions right inside a number format code with [brackets] — color values by threshold or apply different formats above and below a cutoff, all without a conditional-formatting rule.

Quick formula: color by value with bracketed conditions:
[Green][>=100]#,##0;[Red][<100]#,##0
Values at or above 100 show green, below 100 show red — the condition lives in the format itself.

The example

Scores colored by a 100 threshold via the format code.

AB
1ValueDisplays
2120120 (green)
38585 (red)

The formula

Conditions in square brackets:

[Green][>=100]#,##0;[Red][<100]#,##0 // format depends on the value

How it works

Bracketed conditions choose the section:

  1. Put a condition in brackets at the start of a section: [>=100].
  2. Add a color in brackets too: [Green], [Red], [Blue].
  3. Sections are evaluated in order; with conditions you get up to two custom conditions plus a default third section.
  4. Apply via Format Cells → Custom. It styles the number in place, no separate rule needed.

Limited to colors and conditions, not fills. Number-format conditions can change font color and digits, but can’t set a cell background or use more than two conditions. For fills, icon sets, or richer logic, use real Conditional Formatting instead.

Try it: interactive demo

Live demo

≥100 green, <100 red (via format code).

Variations

Three-way

Condition + condition + default:

[Blue][>1000]#,##0;[Red][<0]#,##0;#,##0

Show status text

Replace number with a word:

[>=1]"In stock";"Out"

Use CF for fills

Background color → Conditional Formatting.

Pitfalls & errors

Max two conditions. A format code allows two bracketed conditions plus a default section — need more? Use Conditional Formatting.

No background fills. Number formats change font color/text only, never the cell fill.

Conditions use comparison operators. [>=100], [<0] — put the operator and value inside the brackets, before the format.

Practice workbook

📊
Download the free Conditional Number Formats practice workbook
Cells with conditional number-format codes applied, the three-way and status-text variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I color numbers by value with a format code?
Use bracketed conditions and colors, e.g. [Green][>=100]#,##0;[Red][<100]#,##0. Values are styled by the section whose condition they meet.
How many conditions can a number format have?
Two custom conditions plus a default third section. For more conditions or background fills, use Conditional Formatting.
Can a number format change the cell background?
No — number formats affect font color and the displayed digits/text only. Use Conditional Formatting for fills and icons.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Custom number format · Highlight with a formula · Icon sets