Concept
Number format kabhi cell ki asli value nahi badalta — sirf usse dikhane ka tareeka badalta hai. Formula bar me hamesha asli value dikhti hai.
Built-in formats (Home tab → Number group, ya Ctrl+1):
| Format | 1250 dikhega |
|---|---|
| General | 1250 |
| Number | 1250.00 |
| Currency | ₹1,250.00 |
| Accounting | ₹ 1,250.00 (symbol left-aligned, decimals sabme align) |
| Percentage | 125000% |
| Comma | 1,250.00 |
Custom format codes — jab built-in options kaam na aaye. Ctrl+1 → Number → Custom.
Code me 4 sections hote hain, semicolon ; se separate:
positive;negative;zero;text
Sab likhna zaroori nahi — jitne section doge, wahi apply honge.
Placeholder symbols:
| Symbol | Matlab |
|---|---|
0 |
Digit dikhao, chahe 0 ho (padding ke liye) |
# |
Digit dikhao, agar hai to hi (extra zero nahi) |
, |
Thousands separator |
. |
Decimal point |
0000 → 7 ko "0007" banata hai (fixed-width IDs ke liye)
#,##0 → 1250 ko "1,250" banata hai, .00 nahi
#,##0.00 → 1250 ko "1,250.00" banata hai
Lakh/Crore format (Indian numbering): Excel ka default , grouping western hai (thousands, millions). Indian style — lakh (2 zeros ke baad comma), crore — ke liye:
##,##,##0
12500000 isse "1,25,00,000" dikhega (1 crore 25 lakh). Pattern: pehle group 3 digits, phir har agla group 2 digits ka.
Color aur conditions custom format me:
[Blue]#,##0;[Red]-#,##0;0
Matlab: positive blue, negative red (bina minus sign ke, kyunki bracket already handle kar rahi hai... actually minus khud dikhega, color sirf apply hoga), zero plain.
Condition wale brackets bhi chal sakte hain:
[>=1000000]0.0,,"M";[>=1000]0.0,"K";0
Yeh 2500000 ko "2.5M" aur 4500 ko "4.5K" dikhata hai — dashboards me common trick.
Percentage format ka trap:
Format apply karne se pehle value already decimal honi chahiye. 0.85 pe 0% format lagao to 85% dikhega — sahi. Lekin agar cell me already 85 type kar diya aur % format lagaya, to 8500% dikhega — Excel 85 ko 85 hi treat karta hai, % format sirf ×100 karke % sign lagata hai.
[SS-2: Format Cells dialog open on Custom category, showing ##,##,##0 in the Type box with the preview showing 1,25,00,000]
Practical rule: Currency/Accounting format un cells pe lagao jo final report me jaayenge. Raw working data ko General/Number rakho — formatting recalculate nahi hoti automatically jab tum naya data paste karte ho agar wahan koi format already set nahi hai.