Custom Number Formats in Excel for Financial Models
To create a custom number format in Excel, select the cells, press Ctrl+1 to open Format Cells, choose Custom, and type a format code. The code has up to four sections separated by semicolons: positive;negative;zero;text. Format codes change only how a number displays, never its underlying value, which keeps your model math intact.
How the format code structure works
A custom format string is built from placeholder characters. # shows a digit only if present, 0 shows a digit always (padding with zeros), and , is the thousands separator. A period sets the decimal point. Literal text goes in double quotes.
The full code can have four sections divided by semicolons, applied in this order: positive numbers, negative numbers, zero, and text. If you supply only one section it applies to all numbers. Two sections split positive-or-zero from negative.
Crucially, this is display only. A cell formatted to show 1.2 still holds 1.234567 underneath, so every downstream formula uses the true value. Number formats never round the actual data the way the ROUND function does.
| Code | Displays 1234.5 as | Use |
|---|---|---|
#,##0 | 1,235 | Whole numbers with thousands separator |
#,##0.00 | 1,234.50 | Two decimals, common for currency |
#,##0;(#,##0) | 1,235 (negatives in parentheses) | Accounting-style negatives |
0.0% | Shows 123450.0% for 1234.5 | Percentages |
#,##0, | 1 | Scale display to thousands (trailing comma) |
#,##0.0,, | 0.0 | Scale display to millions (two trailing commas) |
A trailing comma divides the displayed value by 1000. Two trailing commas divide by a million.
Scaling to thousands and millions
Large models read better when figures show in thousands or millions rather than full digits. The trick is a trailing comma in the format code: each comma at the end of the number section divides the displayed value by 1,000 without changing the stored value.
So #,##0, displays 1,250,000 as 1,250 (thousands), and #,##0.0,, displays it as 1.3 (millions, rounded for display). Pair this with a unit label so readers know the scale, for example #,##0," K" or #,##0.0,," M". Add a header note like (USD 000s) and your whole statement stays clean and consistent.
- Select the range you want to scale.
- Press
Ctrl+1to open Format Cells and pick Custom. - For thousands, enter
#,##0,(note the trailing comma). - For millions, enter
#,##0.0,,(two trailing commas). - To append a unit, add a quoted label:
#,##0," K". - Click OK. The numbers shrink on screen while formulas still use the true figures.
Negatives, colors, and conditional formats
Finance conventions favor negative numbers in parentheses and often in a different color. The negative section of the format code handles both. #,##0;[Red](#,##0) shows positives plain and negatives red inside parentheses.
You can embed one of eight named colors in square brackets: [Black], [Blue], [Cyan], [Green], [Magenta], [Red], [White], [Yellow]. You can also put a simple condition in brackets, such as [>=1000000]#,##0.0,," M";[>=1000]#,##0," K";#,##0, which switches the display scale based on magnitude.
Keep custom formats consistent across a workbook. Mixed conventions (some negatives red, some in parentheses, some scaled, some not) make a model look unfinished and slow down review.
#,##0;[Red](#,##0)gives red negatives in parentheses.- Wrap a color name in brackets at the start of any section:
[Blue]#,##0. - Use bracketed conditions like
[>=1000]to switch formats by value, limited to two conditions plus a default. - An underscore followed by a character,
_), adds a space the width of that character to align positives with parenthesized negatives. - An asterisk repeats the next character to fill the cell, used for leader dots in some statements.
Color Coder
Custom number formats make a model readable, but the other half of a clean sheet is consistent cell coloring, blue for inputs, black for formulas, green for links. ModelMint's Color Coder applies that convention across the model automatically, so your formatting and your number codes tell the same story.
Get ModelMint See how it worksFAQ
Does a custom number format change the actual value in the cell?
No. Number formats only affect display. A cell showing 1,250 via a thousands format still stores the full value, and every formula referencing it uses the true number. Use the ROUND function if you need to change the stored value itself.
How do I show numbers in thousands without dividing the data?
Add a trailing comma to the format code. #,##0, displays the value divided by 1,000 on screen while leaving the stored number intact. Two trailing commas (#,##0.0,,) display in millions.
Can I make negative numbers red and in parentheses at the same time?
Yes. Use two sections with a color tag in the negative section, for example #,##0;[Red](#,##0). The positive section formats positives, and the negative section after the semicolon adds the red color and parentheses.