In Excel, conditional formatting is where raw numbers begin to speak.
A dull sheet turns into a signal board—warnings in red, victories in green, patterns glowing quietly in the grid. Practice builds that instinct. Below are 100 practical exercises with datasets and solutions designed the way real office data appears.

To avoid repetition, several exercises share the same dataset. You can paste the dataset once and apply multiple rules.

Dataset 1 – Student Marks

Paste into Excel.

Exercises 1–10 (Basic Highlight Rules)

1. Highlight marks greater than 85
   • Solution: Highlight Cells → Greater Than → 85
2. Highlight marks less than 40
   • Solution: Less Than → 40
3. Highlight marks between 70 and 90
   • Solution: Between → 70–90
4. Highlight top 3 marks in Math
   • Solution: Top/Bottom Rules → Top 3 Items
5. Highlight bottom 2 Science marks
   • Solution: Bottom Rules → Bottom 2
6. Highlight duplicate scores
   • Solution: Duplicate Values
7. Highlight students scoring exactly 90
   • Rule: Equal To → 90
8. Highlight cells containing value 40
   • Rule: Equal To → 40
9. Highlight marks above average
   • Rule: Above Average
10. Highlight marks below average

• Rule: Below Average

Exercises 11–20 (Color Scales)

Select marks columns.

11. Apply Green-Yellow-Red color scale
12. Apply Red-Yellow-Green color scale
13. Apply Blue data gradient
14. Apply color scale only to Math column
15. Apply color scale to all subjects
16. Highlight highest marks using darkest color
17. Highlight lowest marks using light color
18. Compare Science scores using color scale
19. Compare English scores visually
20. Apply 3-color scale using:

• Minimum = Red
• Midpoint = Yellow
• Maximum = Green

Dataset 2 – Employee Attendance

Exercises 21–30 (Attendance Rules)

21. Highlight attendance below 75

• Rule: Less Than 75

22. Highlight above 95
23. Highlight Sales department
24. Highlight HR department
25. Highlight attendance between 80–90
26. Highlight top 2 attendance
27. Highlight bottom 2 attendance
28. Apply data bars
29. Highlight attendance above average
30. Highlight attendance below average

Dataset 3 – Sales Data

Exercises 31–40 (Sales Rules)

31. Highlight sales above 80,000
32. Highlight below 30,000
33. Highlight top 5 sales
34. Apply green data bars
35. Apply icon sets
36. Highlight Laptop product rows
37. Highlight North region
38. Highlight duplicate products
39. Apply 3 color scale
40. Highlight highest sales value

Dataset 4 – Task Deadlines

Exercises 41–50 (Date Rules)

41. Highlight overdue tasks

Formula:

=TODAY()>C2

=TODAY()>C2

42. Highlight tasks due today

=C2=TODAY()

=C2=TODAY()

43. Highlight tasks within next 3 days

=C2<=TODAY()+3

=C2<=TODAY()+3

44. Highlight Completed tasks
45. Highlight Pending tasks
46. Highlight Overdue tasks in red
47. Highlight future deadlines
48. Highlight tasks assigned to Rahul
49. Highlight tasks due this week
50. Highlight earliest deadline

Dataset 5 – Inventory Stock

Exercises 51–60 (Stock Alerts)

51. Highlight stock below 30
52. Highlight stock above 100
53. Highlight Electronics category
54. Highlight Furniture items
55. Apply data bars to stock
56. Apply icon sets
57. Highlight lowest stock
58. Highlight highest stock
59. Highlight top 3 items
60. Highlight bottom 3 items

Advanced Formula Exercises

Exercises 61–70

61. Highlight rows where sales > 80000

=$C2>80000

62. Highlight rows where attendance <80

=$D2<80

63. Highlight students scoring above 90 in any subject
64. Highlight rows where status = Pending

=$D2="Pending"

65. Highlight duplicate employee IDs
66. Highlight rows where stock < average
67. Highlight sales greater than average
68. Highlight maximum value
69. Highlight minimum value
70. Highlight values divisible by 5

Pattern Recognition Exercises

Exercises 71–80

71. Highlight even numbers

=MOD(A1,2)=0

=MOD(A1,2)=0

72. Highlight odd numbers
73. Highlight values divisible by 10
74. Highlight numbers greater than median
75. Highlight numbers between average ±10
76. Highlight top 10% values
77. Highlight bottom 10%
78. Highlight values repeated more than twice
79. Highlight largest value in each column
80. Highlight smallest value in each row

Real Office Scenario Exercises

Exercises 81–100

81. Highlight employees with perfect attendance
82. Highlight students scoring distinction (>85)
83. Highlight failed students (<40)
84. Highlight high sales regions
85. Highlight inventory shortage
86. Highlight tasks overdue
87. Highlight duplicate product entries
88. Highlight rows containing “Laptop”
89. Highlight weekend dates
90. Highlight monthly highest sales
91. Highlight rows where stock < 20
92. Highlight top performing employee
93. Highlight lowest performing employee
94. Highlight sales target achieved
95. Highlight sales target missed
96. Highlight department HR
97. Highlight records updated today
98. Highlight values above 2nd highest
99. Highlight rows containing text “Pending”
100. Highlight entire row where sales > 90,000

Formula:

=$C2>90000

=$C2>90000

Apply rule to entire table.

If you want more exercise, then Comment Topic: