Skip to main content
SheetHub Docs
Navigation Menu
SheetHubDOCSSpreadsheet Knowledge Base
About
Formatting & Layout•7 min read

Excel Custom Number Formats: Format Codes, Colors & Conditions

Learn Excel custom number format codes: four-section structure, colors, conditions, and thousands scaling with examples.

SheetHub••7 min
Type 1200 into a cell and it looks like 1200. Change the number format to $#,##0.00 and the same cell reads $1,200.00. Switch the format to 0.0, "K" and it shows 1.2K. The value never changed. The formula bar still says 1200. Only the display changed. That is the whole idea behind Excel custom number formats: a format code is a small language that controls how a number looks without altering the number itself.

Same number, three looks

The same underlying value presented three ways:
Value in cellFormat codeWhat you see
1200General1200
1200$#,##0.00$1,200.00
12000.0, "K"1.2K
 Same cell, same number, three different stories, and switching between them takes seconds.

What is a custom number format?

A custom number format is a text string you type into the Format Cells dialog: select the cells, press Ctrl + 1, open the Number tab, choose Custom, and type your code into the Type box. The preview shows the effect before you apply it. For a broader cleanup workflow, see these Excel data cleaning techniques before you format the finished values. The code only affects display. The stored value and every formula that references the cell stay untouched.

The four-section structure

Format codes can hold up to four sections, separated by semicolons: 
positive;negative;zero;text
Each section handles one case:
SectionApplies to
1stPositive numbers
2ndNegative numbers
3rdZero
4thText entries
 A practical four-section code: 
0.00;[Red]-0.00;0;"n/a"
Positive values show two decimals, negatives red with a minus sign, zero as 0, and text entries as n/a. Fewer sections are fine: one applies to every number, two split positive-and-zero from negative, three add a zero case. Text shows as typed unless a fourth section exists.

Placeholders: 0, #, and ?

The digits in a format code are placeholders with different levels of commitment:
CodeBehavior
0Required digit; a missing position shows as 0
#Optional digit; it disappears when there is nothing to show
?Optional digit that leaves a space, aligning decimals
#,##0Thousands separator
 0 forces a digit: 000 turns 7 into 007. # shows only existing digits: ### leaves 7 as 7. ? pads with spaces, aligning decimals in a column.

Scaling large numbers

A trailing comma scales the value down by one thousand per comma: 
#,##0,
1200 becomes 1 (thousands). A second comma moves into millions: 
#,##0,,
1,200,000 becomes 1, and 0.0,, turns 1,200,000 into 1.2. Full values stay in the cells while the report shows tidy K and M numbers.

Colors and conditions

Colors go in square brackets at the start of a section. Excel recognizes eight named colors: [Black], [Blue], [Cyan], [Green], [Magenta], [Red], [White], and [Yellow]. The classic red-negative format is: 
0.00;[Red]-0.00
Conditions use a comparison in brackets with a two-section layout: the first section wins when true, and the second handles everything else: 
[>100][Green]0;[<=100][Red]0
Above 100 displays in green; at or below 100 in red. With conditions, sections mean "true;otherwise", not positive/negative.

Percent, scientific, and text

Percent formats multiply the display by 100 and add the symbol: 
0.0%
0.125 shows as 12.5%. Scientific notation uses the exponent pattern: 
0.00E+00
1200 shows as 1.20E+03. Literal text goes in double quotes, and @ shows whatever text the cell contains. Used as the fourth section, it formats text entries: 
0.00;[Red]-0.00;0;"Call: "@
Type John and the cell displays Call: John. For a single literal character, escape it with a backslash. 0\K appends K. * (fill) and _ (space) have special meaning. Quote or escape them.

Custom dates and cumulative time

Date parts combine freely: 
yyyy-mm-dd
renders 2026-08-09; ddd, mmm d gives Sun, Aug 9. Time is where one character changes everything: 
[h]:mm
h:mm
[h] counts elapsed hours, so 25 hours and 30 minutes displays as 25:30. Plain h resets at 24, showing 1:30. Timesheets that cross midnight need the brackets. That is the most common fix in this topic.

Real use cases

NeedFormat code
USD with two decimals$#,##0.00
Millions with an M label#,##0,, "M"
US phone number(###) ###-####
Negative values in red0.00;[Red]-0.00
Aligned decimal column0.0?
Hide the value on screen;;;
 For phone numbers that can start with zero, swap # for 0 and use (000) 000-0000. The leading digits then stay visible.

Error handling and limitations

  • Malformed codes fall back to General. An invalid code, such as one with the wrong section count, a stray character, or unbalanced quotes, silently shows the raw value. Check the preview in Format Cells before applying.
  • Locale changes punctuation. A comma where your Excel expects a decimal point (or the reverse) is the usual reason a copied code "does nothing."
  • [h] and h are easy to mix up. Durations over 24 hours display incorrectly, so decide whether you need elapsed time or clock time before writing the code.
  • Format-code conditions are not conditional formatting. They handle two numeric comparisons; for formulas, icon sets, or data bars, use conditional formatting rules.
  • Format codes never transform data. They affect display only. Use ROUND or a helper column when calculations need real rounding.

Pro tips

  • Keep growing reports consistent. Combine Excel custom number format codes with Excel Tables structured references so formatted report ranges stay organized as rows are added.
  • Build a format library. Custom formats are stored with the workbook, so keep a hidden sheet of your best codes as text.
  • Use ;;; to hide values. Three empty sections display nothing while the cell keeps its value.
  • Scale before you style. Apply 0.0,, to revenue columns first, then decide on the K/M label.

Common mistakes to avoid

  • Forgetting the semicolons can cause a code with conditions or colors to fail silently.
  • Format codes do not round data. Formulas still read the full-precision value, so use ROUND when a rounded result matters.
  • 0.0 rounds the display, not the value.
  • Check separators before copying codes across locales. A code can fail silently on another machine.
  • A numeric code cannot format text entries without a fourth section. Otherwise, text shows as typed.

FAQ

Why doesn't my format show up? Check the Format Cells preview, verify the separator matches your locale, and confirm the code is on the right cells. Can a number format change the value? No. Formats affect display only; to change the actual data, use formulas. How do I show negatives in red? Use two sections: 0.00;[Red]-0.00. How do I display millions as 1.2M? Use 1,200,000 with the double-comma scale #,##0.0,, "M"; the cell displays 1.2M while retaining the full value. Availability: Custom number formats exist in every Excel version, including desktop, web, and mobile. Microsoft 365 includes the same codes.
AI Prompt Idea
Use the format-code rules above to describe your display problem to ChatGPT, Gemini, or Claude:Prompt:
"I have a sales report in Excel. Column C holds revenue values in full dollars, and some values are negative. I want them displayed with two decimals and a thousands separator, negatives in red with a minus sign, and zero shown as a dash. Write a custom number format code for this."
Compare the AI's code against the examples in this article. Count the sections and check where [Red] sits before you apply it.

Summary

Custom number formats are a practical way to control how a spreadsheet displays data: the same value can read as $1,200.00, 1.2K, or 1.20E+03 without a single formula. Learn the four-section structure, remember that 0 forces digits while # lets them go, and keep [h]:mm for anything that crosses midnight. The value never changes; only its display does.
Article Topics
#excel#number-formatting#formatting#custom-format

Recommended Next Reading

All Articles

Share this tutorial

Discussion & Community

Share questions, tips, or edge-cases about this spreadsheet formula.

Recommended Next Reading

All Articles