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 |
About this data: Teaching sample for a fictional shop “Pearl Textiles”. Figures and names (e.g. Vikram Rao) are made up for this lesson on custom number formats.
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 tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of custom number formats on a blank sheet for HomeNest Supplies.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Vikram Rao and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Oliver Bennett.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply custom number formats to a 5-row made-up HomeNest Supplies table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with custom number formats.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as HomeNestSup_practice.xlsx.
Hint: Use a clear file name for class.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
