Custom Number Format Codes

Excel Formulas › Advanced

All versionsFormatting

A custom number format changes how a value looks without changing the value itself. The format code has up to four sections — positive;negative;zero;text — each styled independently.

Quick formula: Format Cells → Custom, enter a code with sections:
#,##0;[Red](#,##0);"–";@
Positives plain, negatives red in parentheses, zeros as a dash, text passed through. The cell’s real value never changes.

Functions used (tap for the full reference guide):

TEXT

The example

One format code, four behaviors.

AB
1ValueDisplays as
212501,250
3−1250(1,250) in red
40–

The formula

Four sections, separated by semicolons:

#,##0;[Red](#,##0);"–";@ // positive ; negative ; zero ; text

How it works

Each section styles a different kind of value:

  1. Section 1 = positive numbers, 2 = negative, 3 = zero, 4 = text.
  2. Codes: 0 forces a digit, # shows a digit only if needed, , is the thousands separator, @ is the text placeholder.
  3. Add colors in brackets: [Red], [Blue], and literal text in quotes: "USD "0.
  4. Apply via Format Cells → Number → Custom. The underlying number is unchanged — formulas still see the true value.

Fewer sections is fine. One section applies to everything; two sections are positive;negative. You only need all four when zero and text deserve special treatment. To bake a format into text, mirror the code in TEXT(value, "…").

Try it: interactive demo

Live demo

See how a value displays under the 4-section code.

Displays as:

Variations

Two sections

Positive ; negative only:

#,##0;[Red]-#,##0

Add a label

Literal text in the code:

"USD "#,##0.00

As TEXT()

Bake the format into a string:

=TEXT(A1, "#,##0;[Red](#,##0)")

Pitfalls & errors

Format ≠ value. A cell showing “–” or “(1,250)” still holds the real number; formulas use the true value, not the display.

Section order is fixed. It’s always positive;negative;zero;text — you can’t reorder them.

0 vs #. 0 pads with zeros (showing 007); # does not. Mixing them up changes leading/trailing zeros.

Practice workbook

📊
Download the free Custom Number Format Codes practice workbook
Cells with real custom number formats applied (positive/negative/zero/text), the TEXT equivalent, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

What are the sections in an Excel custom number format?
Up to four, separated by semicolons: positive;negative;zero;text. Each styles that kind of value independently, e.g. #,##0;[Red](#,##0);"–";@.
Does a custom format change the cell's value?
No — it only changes the display. Formulas and calculations use the underlying true value.
What's the difference between 0 and # in a format code?
0 forces a digit to show (padding with zeros), while # shows a digit only when needed. Use 0 for fixed decimals or leading zeros.

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: Conditional number format · Hide zeros · Format large numbers

Function references: TEXT