Excel's Date & Number Format Codes: The "Secret" Language of Spreadsheets

The **Custom Number Format** feature in Excel allows you to change how a value looks without changing the value itself. This is done by writing "secret" codes that act as display instructions for your data, saving you from complex text formulas.

To access this feature, select your cell(s), right-click, choose **Format Cells**, and go to the **Number** tab, then **Custom**.

Core Code Syntax: The Four Sections

A custom format code can have up to four sections, separated by a semicolon (;). If present, they control how positive, negative, zero, and text values are displayed, in that order:

[Positive];[Negative];[Zero];[Text]

The Digits: `#` vs. `0`

These two codes control how numerical digits are displayed. Understanding the difference is key:

• **`0` (Zero Placeholders):** Always displays a digit, even if it is a leading or trailing zero. Use this for consistent fixed length (e.g., currency).
  Code: `0.00` Value: `12.5` Result: `12.50`
• **`#` (Number Placeholders):** Only displays a digit if it is significant (i.e., affects the value). Zeros are suppressed.
  Code: `#.##` Value: `12.5` Result: `12.5`

The Commas: Scaling and Separating

The comma (`,`) has a dual function in custom number formatting:

• **Thousands Separator:** A comma between digits (e.g., `#,#00`) inserts the thousands separator (e.g., `12,345`).
• **Scaling Large Numbers:** A comma placed *after* the digit codes scales the number by a thousand. This is vital for displaying large figures concisely on a dashboard.
  Code: `0, "K"` Value: `50000` Result: `50 K`

Conditional Formatting for Numbers

You can use square brackets to apply a format only when a value meets a certain condition, often overriding the standard four-section rule:

[Red][>=1000]0.0,"K";[Blue]0.0;

This code will display any value 1,000 or greater in **Red** and as a scaled-down "K" value, but other positive values will be in **Blue** with one decimal place. This is powerful for visual reporting!

Using custom formats is often cleaner and more efficient than using nested `IF` or `TEXT` formulas to achieve the same visual result.