Custom & Conditional Number Formatting
Change how a number looks — currency, thousands separators, a “k” suffix, or red for negatives — without ever changing the value itself, using Excel’s format codes.
What you will learn
- Explain that formatting changes display, not value
- Read and write a custom format code
- Colour negatives and add units with conditional number formats
Looks versus value
A number format controls how a value is shown, not what it is. The cell might hold the exact number 1234.5, but you can make it display as ₹1,234.50, 1.2k or 1,235 — the stored value never changes, so every formula still uses the real number. This is the single most important idea: formatting is a costume the number wears, not a haircut.
You reach the controls with Ctrl + 1 (the Format Cells box) or Home > Number. The built-in choices — Currency, Percentage, Date — cover most days. When none of them fit, you write your own with a custom format code.
Reading a format code
A format code is a tiny pattern made of placeholder symbols. You type it under Format Cells > Number > Custom. The two main placeholders are:
0— a digit that always shows (it pads with a zero if needed).#— a digit that shows only if present (no padding).
Everything else in the code — a comma, a currency sign, letters in quotes — is shown literally. Here is how the same value 1234.5 looks under different codes:
| Format code | Value 1234.5 shows as | What it does |
|---|---|---|
0 | 1235 | Whole number, rounded for display |
0.00 | 1234.50 | Always two decimal places |
#,##0 | 1,235 | Thousands separator, no decimals |
#,##0.00 | 1,234.50 | Thousands separator + 2 decimals |
"₹"#,##0.00 | ₹1,234.50 | Adds a literal rupee sign in front |
0"%" (on 95) | 95% | Adds a literal percent text after |
Watch out: Adding the text "%" in a format does not divide by 100 — it just types a percent sign next to the real number. The true Percentage format (Ctrl+Shift+5) multiplies by 100 for display, so 0.25 shows as 25%. Know which you want: a label, or a real percentage.
A worked example: show big numbers as “k”
Dashboards often shorten large sales figures. The code below divides the display by 1000 (each trailing comma after the number means “÷ 1000 for display”) and tacks on the letter k:
#,##0,"k"Note: Output: The value 1234500 displays as 1,235k. The value 8200 displays as 8k. The single comma right before the closing quote scaled the display down by 1000, and "k" was added as plain text. The cell still holds the full number, so SUM and charts use the real figure — only the look is shortened.
Conditional number formats: a colour per sign
A custom code can hold up to four parts separated by semicolons, applied in this fixed order: positive ; negative ; zero ; text. This lets the format react to the value — for instance, showing negatives in red and inside brackets, the way accountants write them.
#,##0;[Red](#,##0);"-"Note: Output:
1500 displays as 1,500
-1500 displays as (1,500) in red
0 displays as -
The three parts ran in order: positives used the first pattern, negatives used the second (the [Red] colour and the brackets), and an exact zero used the third (a single dash). All from one format code, and the underlying values are unchanged — a negative is still a real negative for any formula.
Tip: You do not have to memorise codes. Pick the closest built-in format first (say Currency), then open Custom — Excel shows that format’s code already filled in, and you can tweak it. Reading existing codes this way is the fastest path to writing your own.
Watch out: Formatting is display-only, so a cell showing 1,235 may really hold 1234.5. If you need the rounded value for further maths (not just the look), use the ROUND function to change the actual number — formatting alone will not.
Q. A cell holds the value 1234.5 and you apply the custom format #,##0. What is true?
✍️ Practice
- Type 1234.5 in a cell and try the formats
0.00,#,##0and"₹"#,##0.00, noting the cell value never changes. - Apply the four-part code
#,##0;[Red](#,##0);"-"to a column with a mix of positive, negative and zero values.
🏠 Homework
- Build a small profit/loss list with positive and negative values. Write one custom format that shows a thousands separator, displays negatives in red brackets, and shows zeros as a dash. Confirm with a SUM that the real values are unchanged.