Step 1: Enter the Dataset
Create the following headers in Row 1:
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| Emp ID | Employee Name | Department | Sales | Target | Attendance % | Rating | Joining Date |
Enter the sample data in rows 2–21.
Example 1: Highlight Sales Greater Than ₹100,000
Objective
Highlight employees whose sales exceed ₹100,000.
Steps
Step 1
Select cells:
D2:D21
Step 2
Go to:
Home → Conditional Formatting
Step 3
Choose:
Highlight Cells Rules → Greater Than
Step 4
Enter:
100000
Step 5
Choose formatting color.
Example:
Green Fill with Dark Green Text
Step 6
Click:
OK
Result:
All sales above ₹100,000 are highlighted.
Example 2: Highlight Attendance Below 80%
Steps
Select:
F2:F21
Go to:
Home → Conditional Formatting
→ Highlight Cells Rules
→ Less Than
Enter:
80
Choose:
Red Fill
Click:
OK
Example 3: Highlight Rating = 5
Steps
Select:
G2:G21
Go to:
Home → Conditional Formatting
→ Highlight Cells Rules
→ Equal To
Enter:
5
Choose:
Gold Fill
Click:
OK
Example 4: Highlight Finance Department
Steps
Select:
C2:C21
Go to:
Home → Conditional Formatting
→ Highlight Cells Rules
→ Text That Contains
Enter:
Finance
Choose:
Blue Fill
Click:
OK
Example 5: Highlight Employees Joined Before 2021
Steps
Select:
H2:H21
Go to:
Home → Conditional Formatting
→ New Rule
→ Use a Formula to Determine Which Cells to Format
Formula:
=YEAR(H2)<2021
Click:
Format
Choose:
Orange Fill
Click:
OK → OK
Example 6: Highlight Entire Row When Rating = 5
Steps
Select entire table:
A2:H21
Go to:
Home → Conditional Formatting
→ New Rule
→ Use a Formula
Formula:
=$G2=5
Click:
Format
Choose:
Green Fill
Click:
OK → OK
Result:
Complete employee row becomes green when Rating = 5.
Example 7: Highlight Sales Greater Than Target
Steps
Select:
A2:H21
Go to:
Home → Conditional Formatting
→ New Rule
→ Use a Formula
Formula:
=$D2>$E2
Choose:
Light Green Fill
Click:
OK
Example 8: Highlight Sales Less Than Target
Steps
Select:
A2:H21
Formula:
=$D2<$E2
Choose:
Light Red Fill
Example 9: Highlight Attendance Below 85% AND Rating Below 3
Steps
Select:
A2:H21
Formula:
=AND($F2<85,$G2<3)
Choose:
Dark Red Fill
Example 10: Highlight Top 5 Sales Employees
Steps
Select:
D2:D21
Go to:
Home → Conditional Formatting
→ Top/Bottom Rules
→ Top 10 Items
Change:
10 → 5
Choose:
Green Fill
Click:
OK
Example 11: Highlight Bottom 5 Sales Employees
Steps
Select:
D2:D21
Go to:
Conditional Formatting
→ Top/Bottom Rules
→ Bottom 10 Items
Change:
10 → 5
Click:
OK
Example 12: Apply Data Bars
Steps
Select:
D2:D21
Go to:
Home → Conditional Formatting
→ Data Bars
Choose any style.
Result:
Longer bars indicate higher sales.
Example 13: Apply Color Scale
Steps
Select:
F2:F21
Go to:
Conditional Formatting
→ Color Scales
Choose:
Green-Yellow-Red
Result:
- Green = High Attendance
- Yellow = Average
- Red = Low Attendance
Example 14: Apply Icon Sets
Steps
Select:
G2:G21
Go to:
Conditional Formatting
→ Icon Sets
Choose:
3 Stars
Result:
⭐⭐⭐ High Rating
⭐⭐ Medium Rating
⭐ Low Rating
Example 15: Highlight Duplicate Employee Names
Steps
Select:
B2:B21
Go to:
Conditional Formatting
→ Highlight Cells Rules
→ Duplicate Values
Choose:
Red Fill
Click:
OK
Managing Rules
View Rules
Home
→ Conditional Formatting
→ Manage Rules
Edit Rule
Manage Rules
→ Select Rule
→ Edit Rule
Delete Rule
Manage Rules
→ Select Rule
→ Delete Rule
Professional Dashboard Uses
HR Dashboard
- Attendance below 80%
- Rating below 3
- Late employees
Sales Dashboard
- Top performers
- Target achieved
- Target missed
Inventory Dashboard
- Low stock
- Reorder alerts
- Expired products
Finance Dashboard
- Overdue invoices
- Negative profit
- High expenses
School Management
- Failed students
- Top scorers
- Fee pending students
These step-by-step methods cover the most common conditional formatting tasks used in real-world Excel reporting, MIS reports, HR dashboards, sales dashboards, and business analytics.
Leave a Reply