MS Excel - Microsoft Excel, Highlight Cell Rules

In Microsoft Excel, Highlight Cell Rules are part of the Conditional Formatting feature. They allow you to automatically highlight cells based on specific conditions, such as values greater than a number, dates before today, duplicate values, or text that contains specific words.

This feature makes it easier to analyze data visually by applying color formatting to cells that meet certain criteria.

1. Where to Find Highlight Cell Rules

  1. Go to the Home tab.

  2. In the Styles group, click Conditional Formatting.

  3. Select Highlight Cells Rules from the drop-down menu.

2. Types of Highlight Cell Rules

Excel provides several built-in options:

a) Greater Than

  • Highlights cells with values greater than a specified number.

  • Example: Highlight sales above 10,000 in green.

Steps:
Home → Conditional Formatting → Highlight Cell Rules → Greater Than → Enter 10000 → Choose a color.

b) Less Than

  • Highlights cells with values less than a specified number.

  • Example: Highlight marks below 40 in red.

Steps:
Home → Conditional Formatting → Highlight Cell Rules → Less Than → Enter 40 → Choose a color.

c) Between

  • Highlights cells with values between two numbers.

  • Example: Highlight scores between 60 and 80.

Steps:
Home → Conditional Formatting → Highlight Cell Rules → Between → Enter 60 and 80 → Choose a color.

d) Equal To

  • Highlights cells exactly matching a number or text.

  • Example: Highlight cells where status = “Completed”.

Steps:
Home → Conditional Formatting → Highlight Cell Rules → Equal To → Enter Completed → Choose a color.

e) Text That Contains

  • Highlights cells containing specific text.

  • Example: Highlight all products containing the word “Pro”.

Steps:
Home → Conditional Formatting → Highlight Cell Rules → Text that Contains → Enter Pro → Choose a color.

f) A Date Occurring

  • Highlights cells based on date conditions.

  • Options include: Today, Yesterday, Last 7 Days, Next Month, etc.

  • Example: Highlight due dates within the next 7 days.

Steps:
Home → Conditional Formatting → Highlight Cell Rules → A Date Occurring → Select Next 7 Days → Choose a color.

g) Duplicate Values

  • Highlights cells with duplicate or unique values.

  • Example: Highlight duplicate employee IDs.

Steps:
Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values → Choose Duplicate → Pick a color.

3. Customizing Highlight Cell Rules

After creating a rule, you can customize it:

  1. Go to Home → Conditional Formatting → Manage Rules.

  2. Select the rule and click Edit Rule.

  3. Modify:

    • Formula

    • Formatting color

    • Applies to range

4. Removing Highlight Cell Rules

  • For selected cells:
    Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells

  • For entire sheet:
    Home → Conditional Formatting → Clear Rules → Clear Rules from Entire Sheet

5. Example

Student Marks
John 85
Sarah 45
David 35
Emma 95

Goal: Highlight students who scored less than 50 in red.
Steps:

  1. Select the Marks column.

  2. Apply: Home → Conditional Formatting → Highlight Cell Rules → Less Than → 50 → Red Fill.

  3. Now Sarah and David’s cells will be highlighted.