MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Highlighting Cells Based on Rules0%
Highlighting Cells Based on Rules
Beginner12 min read•Updated: Jul 11, 2026
Highlighting Cells Based on Rules
Conditional Formatting automatically applies styling—such as cell fills, font colors, and borders—to cells that satisfy specific criteria. It instantly draws attention to critical metrics, overdue deadlines, low inventory thresholds, and duplicate values.
1. Accessing Highlight Cells Rules
Conditional Formatting is located on the Home tab in the Styles group:
Home Tab > Conditional Formatting > Highlight Cells Rules:
├── Greater Than...
├── Less Than...
├── Between...
├── Equal To...
├── Text that Contains...
├── A Date Occurring...
└── Duplicate Values...
Click to Zoom2. Common Highlighting Scenarios
A. Highlighting Exceptions with Greater Than / Less Than
- Scenario: Flag all orders where shipment delay exceeds 5 days.
- Steps:
- 1Highlight the Days Delayed column (e.g., 'E2:E200').
- 2Click Conditional Formatting > Highlight Cells Rules > Greater Than....
- 3Enter '5' in the value box.
- 4Select Light Red Fill with Dark Red Text from the preset dropdown (or choose Custom Format...).
- 5Click OK.
B. Finding and Flagging Duplicate Records
- Scenario: Identify duplicate Employee IDs or Invoice Numbers.
- Steps:
- 1Select the identifier column (e.g., 'A2:A500').
- 2Click Conditional Formatting > Highlight Cells Rules > Duplicate Values....
- 3Choose whether to highlight Duplicate or Unique entries.
- 4Select a formatting style (e.g., Yellow Fill with Dark Yellow Text).
- 5Click OK. Every duplicate ID is highlighted immediately.
C. Text That Contains (Status Tracking)
- Scenario: Color code task status columns:
- Highlight cells containing 'Completed' in Green.
- Highlight cells containing 'Pending' in Yellow.
- Highlight cells containing 'Cancelled' in Red.
- Apply three separate rules using Highlight Cells Rules > Text that Contains....
3. Top / Bottom Rules
Located directly beneath Highlight Cells Rules, Top/Bottom Rules dynamically evaluate numbers relative to the entire selected group:
- Top 10 Items... / Bottom 10 Items...: Highlights the highest or lowest N values (you can customize N to Top 3, Top 5, etc.).
- Top 10%... / Bottom 10%...: Flags the upper or lower percentile tier.
- Above Average... / Below Average...: Calculates the mathematical mean on the fly and highlights values above or below average. As numbers change, the formatting dynamically recalculates!
4. Managing and Clearing Rules
- Clear Rules from Selected Cells: Removes rules from only the active selection.
- Clear Rules from Entire Sheet: Strips all conditional formatting across the sheet.
- Conditional Formatting Rules Manager: Click Home > Conditional Formatting > Manage Rules... (or press Alt + O + D) to edit threshold numbers, change colors, or adjust evaluation order.
Interactive Quiz
Multiple Choice Questions
Question 1
Where is the Conditional Formatting tool located in the Microsoft Excel ribbon?
Question 2
Which preset rule allows you to quickly locate duplicate customer IDs in a column?
Question 3
What happens to the formatting applied by 'Above Average' if you update the numbers in the column?
Question 4
How can you open the Rules Manager to edit, reorder, or delete existing conditional formatting rules?
Question 5
If you only want to highlight tasks that contain the word 'Urgent' anywhere in the cell, which rule is best suited?
Next Lesson
Data Bars, Color Scales, and Icon Sets
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
Related Lessons
| Previous Lesson | Next Lesson |
|---|---|
| Using Table Styles and Total Row Calculations | Data Bars, Color Scales, and Icon Sets |
Practice Quiz
Test your understanding of this lesson with 5 questions. Each question has one correct answer.