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.

---

SEO Description

Learn how to use Conditional Formatting in Excel to highlight values, apply color scales, add data bars, and visualize data instantly.

Publication date: 17 March 2025

---

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.

• 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

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

---

3. Highlight Cell Rules

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

---

4. Top/Bottom Rules

• 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

---

7. Common Mistakes to Avoid

• Applying rules to the wrong range
• Using too many colors
• Overlapping rules
• Forgetting that formatting updates automatically

---

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.
5. Add data bars to a second column.
6. Highlight duplicates.
7. Edit a rule using “Manage 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