1.6 Custom Number Formats
A custom format has up to four sections separated by semicolons:
positive ; negative ; zero ; text
| Code | Value | Displays | Use |
|---|---|---|---|
0.0 "mins" |
9.5 | 9.5 mins | Units without making it text |
₹#,##0;[Red]-₹#,##0;"–" |
-500 | -₹500 in red | Profit/loss, dash for zero |
00000 |
411 | 00411 | Fixed-length codes |
[h]:mm |
1.25 | 30:00 | Total hours above 24 |
dd-mmm-yy |
14-03-2026 | 14-Mar-26 | Short date |
ddd |
14-03-2026 | Sat | Weekday |
0.0,, "M" |
2345000 | 2.3 M | Millions |
[Green]▲0%;[Red]▼0% |
0.12 | ▲12% | KPI change arrows |
@" (Maharashtra)" |
Pune | Pune (Maharashtra) | Add text to text |
Conditions go in square brackets, e.g. [>=100000]. Colours available by name: [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow].
Steps in Excel
- Select the cells › Ctrl + 1 › Number › Custom.
- Type the code in the Type box; watch the Sample preview.
- OK. The value is unchanged – only the display changes, so formulas still work.
Worked example. Raja shows delivery time as "9 mins" without breaking the average: format Delivery Mins with 0 "mins". =AVERAGE(M2:M500) still works, because the cells remain numbers.
Ravindra Bagale's Tip
Khup students "9 mins" asa text type kartat kiwa =M2&" mins" formula ne column banavtat, aani mag average kadhta yet nahi. Unit dakhvaycha asel tar custom format vapra – value number ch rahate. Lakshat theva: format fakt disnyasathi, value tashich rahate.
Practice task
Create formats for: delivery minutes with "mins", pincodes with 6 digits, profit with red negative and dash for zero, and growth % with green ▲ / red ▼.