Lesson 4.4 – Conditional Formatting

Conditional Formatting allows Excel to automatically highlight cells based on rules. It helps you identify trends, spot errors, and visualize patterns without creating charts. In this lesson, you will learn how to apply basic conditional formatting rules used worldwide.

---

1. What Is Conditional Formatting?

Conditional Formatting changes the appearance of a cell based on its value. Excel can automatically apply colors, icons, or data bars when certain conditions are met.

Examples:

• Highlight values greater than 100
• Color cells containing specific text
• Show data bars to compare numbers visually
• Highlight duplicate values

---

2. How to Apply Conditional Formatting

Steps:

1. Select the range you want to format.
2. Go to Home → Conditional Formatting.
3. Choose the rule type you need.

Excel will instantly apply the formatting based on your rule.

---

3. Highlight Cell Rules

These rules highlight cells based on simple conditions.

• Greater Than
• Less Than
• Between
• Equal To
• Text That Contains
• A Date Occurring
• Duplicate Values

Example:

Highlight all values greater than 80

---

4. Top/Bottom Rules

These rules help you identify the highest or lowest values in a dataset.

• Top 10 items
• Top 10%
• Bottom 10 items
• Above average
• Below average

---

5. Data Bars, Color Scales, and Icon Sets

• Data Bars

Add horizontal bars inside cells to compare values visually.

• Color Scales

Apply gradient colors based on value (e.g., green = high, red = low).

• Icon Sets

Add icons such as arrows, flags, or circles to represent categories.

---

6. Managing Rules

You can edit or remove rules using:

Home → Conditional Formatting → Manage Rules

This is useful when multiple rules overlap or when you need to adjust priorities.

---

7. Common Mistakes to Avoid

• Applying rules to the wrong range
• Using too many colors (keep it simple!)
• Forgetting that formatting updates automatically when data changes
• Overlapping rules that create visual confusion

---

8. Practical Exercise

1. Create a worksheet named Lesson_4_4_Practice.
2. Enter at least 20 numeric values.
3. Highlight values greater than 50.
4. Apply a color scale to the entire range.
5. Add data bars to a second numeric column.
6. Highlight duplicate values.
7. Open “Manage Rules” and edit one of the rules.

---

Internal Links

• Lesson 4.2 – Sorting Data
• Lesson 4.3 – Filtering Data
• Lesson 4.5 – Removing Duplicates

---

Next Lesson

Lesson 4.5 – Removing Duplicates