MO-211 Expert Prep
Module 2 · Lesson 2.4

Custom Number Formats

Edit on GitHub

Python's f-strings (f"{value:,.2f}") and SQL's FORMAT() function let you control exactly how a number is displayed without changing its underlying value. Excel's custom number formats work the same way — a cell can store 1500000 while displaying $1.5M, 1,500,000, or 1.50M USD depending entirely on the format code you apply.

Part 1: Accessing the Custom Format Dialog

Right-click any cell or selection > Format Cells > Number tab > Custom category.

Alternatively: Ctrl + 1 to open Format Cells, then navigate to Custom.

The Type field is where you enter your format code. A preview updates in real time as you type.

Part 2: The Four-Section Structure

A complete format code can have up to four sections, separated by semicolons:

Positive ; Negative ; Zero ; Text
Section Applies to
First Positive numbers
Second Negative numbers
Third Zero values
Fourth Text entries

Examples:

Format Code Positive Negative Zero Text
#,##0 1,500 -1,500 0 (unchanged)
#,##0;[Red]-#,##0 1,500 -1,500 (red) 0 (unchanged)
#,##0;(#,##0);"-" 1,500 (1,500) - (unchanged)
;;; (hidden) (hidden) (hidden) (hidden)

You do not need all four sections. One section applies to all numbers; two sections apply to positive and negative separately.

Part 3: Core Format Code Characters

Code Meaning Example Code Displays
0 Force a digit (shows 0 if none) 000 7 → 007
# Optional digit (no padding) #,##0 1500 → 1,500
? Space placeholder (aligns decimals) 0.?? 3.1 and 3.14 align
, Thousands separator #,##0 1000 → 1,000
, at end Scale by 1,000 0, 1500000 → 1500
"text" Literal text 0" units" 5 → 5 units
@ Text placeholder @" (required)" Name → Name (required)
* Repeat next char to fill width * 0 Pads with spaces
_ Skip width of next character _) Aligns with closing paren

Part 4: Date and Time Codes

Code Meaning Example Output
d Day number, no padding 5
dd Day number, zero-padded 05
ddd Abbreviated day name Mon
dddd Full day name Monday
m Month number, no padding 3
mm Month number, zero-padded 03
mmm Abbreviated month name Mar
mmmm Full month name March
yy Two-digit year 24
yyyy Four-digit year 2024
h Hour (12-hour), no padding 9
hh Hour (12-hour), zero-padded 09
mm (after h) Minutes 45
ss Seconds 30
AM/PM 12-hour indicator AM or PM

Part 5: Special Techniques

Color Codes

Wrap a color name in brackets in the first position of a section:

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

Available colors: [Black], [White], [Red], [Green], [Blue], [Yellow], [Magenta], [Cyan]

Suffix Labels (K, M, B)

Use trailing commas and literal text:

#,##0,"K"        → 1,500,000 displays as 1,500K
#,##0,,"M"       → 1,500,000 displays as 2M
#,##0,,,"B"      → 1,500,000,000 displays as 2B

Hide Zeros Completely

#,##0;-#,##0;""

The third section is empty quotes — zeros display as blank.

Accounting-Style Negative with Parentheses

_(#,##0_);(#,##0)

Positives right-align, negatives appear in parentheses, both align perfectly.

Part 6: Conditional Format Codes

The four-section structure (Positive ; Negative ; Zero ; Text) is the default routing — Excel decides which section to apply based on the sign of the value. You can override that default and route values into sections based on arbitrary numeric conditions by placing a comparison in square brackets at the start of the section.

Replacing Sign-Based Routing with Conditions

A condition in brackets — [>=1000], [<0], [=0] — replaces the default sign test for that section.

[>=1000000]0.0,,"M";[>=1000]0.0,"K";0

Reading left to right: - If the value is >= 1,000,000, scale by millions and append M → 1,500,000 displays as 1.5M. - Otherwise, if the value is >= 1,000, scale by thousands and append K → 2,500 displays as 2.5K. - Otherwise, display the plain integer → 42 displays as 42.

Combining Conditions with Colors

Conditions and color codes can both appear in the leading bracket group:

[Red][<=0]0.00;[Blue][>1000]0.00;0.00
  • Values <= 0 display in red.
  • Values > 1000 display in blue.
  • All other values use the default color and the third section's format.

The 3-Condition Limit

IMPORTANT
A custom format supports a maximum of **three condition-tested sections plus one default section** (four sections total). For more than three branches, use **Conditional Formatting** instead — that feature has no such limit.

If you need exactly four conditional branches with no default, the format engine cannot do it; switch to Conditional Formatting > New Rule > Format only cells that contain.

Hiding Specific Values with Conditions

You can hide values in a particular range by giving them an empty quoted string:

[<0]"";[>=0]#,##0

Negative values display as nothing; non-negative values display with thousands separators.

Part 7: Locale and Currency Codes (beyond exam scope)

Excel also supports locale prefixes like [$-409] for English (US), but locale codes are not exam content — see the Microsoft locale-code reference if you need them in production.

Official Resources