Excel Custom Number Formats for Financial Models

Start with the four-section grammar

A custom format changes display, not the stored value. Semicolons separate positive, negative, zero and text formats in that order.

One section applies to all numbers.
Two sections use the first for positive numbers and zero, and the second for negative numbers.
Three sections split positive, negative and zero.
Four sections add a dedicated text treatment with @ as the text placeholder.

Start with the four-section grammar
Use	Stored input	Format code	Displayed output
Four sectionsColour is optional and should not be the only cue.	1,250.5 / -1,250.5 / 0 / Covenant	`[Blue]#,##0.0;[Red](#,##0.0);-;"Note: "@`	1,250.5 / (1,250.5) / - / Note: Covenant

These placeholders control required digits, optional digits and alignment spaces.

0 displays insignificant zeros, so 8.9 under 0.00 becomes 8.90.
# displays significant digits without insignificant zeros, so 8.9 under #.## stays 8.9.
? leaves a space for insignificant zeros so decimal points or fractions can align in a column.
Use one placeholder pattern across a comparable block.

Choose 0, # and ? deliberately
Use	Stored input	Format code	Displayed output
Required decimalUse when fixed precision is meaningful.	8.9	`0.00`	8.90
Optional decimalUse when trailing zeros add noise.	8.9	`#.##`	8.9
Aligned optional digitThe spacing is visible only in a fixed-width comparison.	8.9	`0.0?`	8.9 plus one alignment space

A comma between placeholders groups thousands. Each trailing comma scales the display by 1,000.

Use commas for grouping and scaling
Use	Stored input	Format code	Displayed output
ThousandsOne trailing comma divides the display by 1,000.	12,000	`0.0,`	12.0
MillionsTwo trailing commas divide the display by 1,000,000.	12,200,000	`0.0,,"m";(0.0,,"m");-`	12.2m

Quotation marks and escapes add display characters without converting the value to text.

Quote literal suffixes such as x or m so their role is explicit.
Use % only when the stored decimal should be multiplied by 100 for display.
Keep currency and unit labels consistent with the model header and locale.
Use a number format instead of concatenating display text into a formula result.

Add literal text without converting values to text
Use	Stored input	Format code	Displayed output
Percentage% multiplies the displayed number by 100.	0.2375	`0.0%`	23.8%
MultipleThe quoted x is literal display text.	8.45	`0.0"x"`	8.5x
Currency and suffixValidate currency conventions with the case brief or house style.	125.5	`$0.0"m"`	$125.5m

Colour, conditions and spacing can improve scanning, but they do not validate a number.

Put one of Excel's eight named colours in square brackets at the start of a section.
Conditions such as [<0] apply a section only when the value meets the condition.
An underscore adds a space equal to the next character; an asterisk repeats the next character to fill the cell.
Do not use colour or spacing to hide errors, zeros or assumptions.

Use colours, conditions and spacing sparingly
Use	Stored input	Format code	Displayed output
Conditional warningKeep a written label because colour alone is inaccessible.	-2.4	`[Red][<0]-0.0;[Blue][>=0]0.0`	-2.4 in red
Parenthesis spacingThis aligns positive and negative values.	1,250	`#,##0_);(#,##0)`	1,250 plus closing-parenthesis space

Test the code because the wrong scale, precision or sign can still look plausible.

Test representative positive, negative, zero and text inputs in a scratch range.
Confirm the formula bar still contains the intended number or formula.
Check the model in its target locale, desktop version, normal zoom and print layout.
Apply the agreed format to the full comparable range and inspect exceptions.