Conditional Formatting in Excel — Data Ko Auto-Highlight Karo (Examples)
Excel conditional formatting se cells automatically color karo — rules, data bars, color scales, highlight duplicates — complete guide with examples.
Conditional Formatting in Excel — Data Ko Auto-Highlight Karo
1000 rows mein se top values manually dhoondna band karo — Conditional Formatting automatically color kar deta hai jo important hai. Complete guide with examples.
Table of Contents
- Basic Use
- Common Rules
- Data Bars, Color Scales, Icons
- Custom Formula Rules
- Duplicates Highlight
- Remove/Edit Rules
- FAQ
- Conclusion
Basic Use
- Cells select karo (data range)
- Home → Conditional Formatting
- Rule choose karo → value daalo → format pick karo
Common Rules
| Rule | Kaam |
| Highlight > / < | Value se upar/neeche |
| Between | Range ke andar |
| Text Contains | Specific text wale cells |
| Dates | Today, yesterday, last week |
| Duplicates | Repeat values pakdo |
| Top 10 / Bottom 10 | Highest/lowest |
Example: Marks column pe → Greater Than → 80 → green — sab 80+ green ho jayenge automatically.
Data Bars, Color Scales, Icons
- Data Bars: Cell ke andar bar — jitna zyada number, utna bada bar (visual comparison)
- Color Scales: Green (high) → Yellow → Red (low) gradient
- Icon Sets: Arrows, traffic lights, ratings — visual indicators
Custom Formula Rules
New Rule → "Use a formula":
=B2>80 # B2>80 ho toh
=MOD(ROW(),2)=0 # alternate rows color
=$B2="Absent" # "Absent" wali poori row
=AND($B2>80,$C2>80) # multiple conditions
Duplicates Highlight
Conditional Formatting → Highlight → Duplicate Values — repeat entries instantly dikhti hain. Remove duplicates ke saath combo = clean data.
Remove/Edit Rules
Conditional Formatting → Manage Rules → rule select → Edit ya Delete → Apply.
FAQ
Formula rule apply nahi ho raha?
Cell reference relative vs absolute check karo — $B2 vs B2 farak karta hai.
Rules apply hue nahi dikhte?
Manage Rules → range check — rules alag range pe ho sakte hain.
Performance slow?
Bohot saare rules = slow — unnecessary rules delete karo.
Conclusion
Conditional Formatting = Excel ka auto-highlighter. Ek rule banao — data change hone pe colors automatically update hote hain. Data analysis 10x fast ho jata hai.
TAGS: conditional formatting excel, excel conditional formatting formula, highlight cells excel, data bars excel, color scale excel, excel duplicate highlight, excel rules, excel tips, techn trainer
What's Your Reaction?
