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
Many students type text like "9 mins" or build a column with the formula =M2&" mins", and then they can't calculate an average. If you want to show a unit, use a custom format – the value stays a number. Remember: a format only changes how it looks; the value stays the same.
Ravindra Bagale's Tip – मराठी
बरेच students "9 mins" असा text type करतात किंवा =M2&" mins" formula ने column बनवतात, आणि मग average काढता येत नाही. Unit दाखवायचं असेल तर custom format वापरा – value number च राहते. लक्षात ठेवा: format फक्त दिसण्यासाठी, value तशीच राहते.
Ravindra Bagale's Tip – हिंदी
बहुत से students "9 mins" जैसा text type करते हैं या =M2&" mins" formula से column बनाते हैं, और फिर average नहीं निकाल पाते. Unit दिखाना हो तो custom format इस्तेमाल करो – value number ही रहती है. याद रखो: format सिर्फ़ दिखने के लिए है, value वैसी ही रहती है.
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 ▼.