Concept
Conditional Formatting (CF) cell ki value ke basis pe apni-se-apna formatting badalta hai — data change hote hi formatting bhi update ho jaati hai. Home tab → Conditional Formatting.
4 main types:
1. Highlight Cells Rules — simple conditions:
- Greater Than / Less Than / Between
- Equal To
- Text that Contains
- Duplicate Values
Example: Sales column me Greater Than 50000 select karo, "Green Fill with Dark Green Text" choose karo — sab 50000+ wale cells highlight ho jaate hain.
2. Top/Bottom Rules:
- Top 10 Items (ya Top 10%)
- Bottom 10 Items
- Above Average / Below Average
3. Data Bars — cell ke andar hi ek horizontal bar dikhta hai, value ke proportion me. Bade dataset me pattern turant dikh jaata hai bina numbers padhe.
4. Color Scales — 2-color ya 3-color gradient (jaise laal-peela-hara), pura range ek "heatmap" ban jaata hai. Sabse high value hare (ya jo bhi color set karo), sabse low laal.
[SS-3: a sales column with green data bars showing proportional length, next to the same column with a 3-color scale heatmap]
Icon Sets bhi hota hai (traffic lights, arrows, flags) — Top/Bottom aur Data Bars ke saath hi menu me milta hai.
Formula-based rule (sabse powerful):
Jab built-in rules kaam na aayein, apna formula likho — "New Rule → Use a formula to determine which cells to format":
=$C2>$B2 → C column ka value B column se zyada hai to highlight
=$D2="Overdue" → D column me exact text match
=MOD(ROW(),2)=0 → alternate row banding (zebra stripes)
Important: Formula me row reference relative rakho ($C2, $ sirf column pe), taaki jab rule pura range pe apply ho to har row apna sahi comparison kare. Yeh wahi $ logic hai jo M3 (F4 ka jaadu) me detail se aata hai.
Rules manage karna:
Home → Conditional Formatting → Manage Rules — yahan se:
- Existing rules dekh sakte ho, edit/delete kar sakte ho
- Rules ka order matter karta hai — upar wala rule pehle apply hota hai
- "Stop If True" checkbox — agar ek rule match ho gaya to neeche wale rules skip ho jaate hain
Common mistake: CF ek range pe lagane ke baad, agar tum beech me rows insert karte ho, kabhi-kabhi rule ka range shift nahi hota sahi se. Manage Rules me jaakar "Applies to" range verify kar lo naye rows add karne ke baad.
Kab use na karo: Bahut zyada rules ek hi sheet pe (10+) file ko slow kar dete hain, especially bade datasets pe. Simple highlight ke liye ek formula rule kaafi hota hai, alag-alag manual rules ki jagah.