How to have 2 different highlight colors in Excel?

How to have 2 different highlight colors in Excel?
Last Updated on 26/04/2024
Reading time: 3 minutes

How do you highlight 2 different colors for 2 distinct values with the conditional formatting in Excel?

  1. Write your first conditional formatting rule

    Write a test on your first value

  2. Create a second conditional formatting rule

    Write a second test on the other value

  3. Apply rules in Order

    That's the most important point of this topic. If your rules aren't in the expected order, your result couldn't be correct.

In Excel, you can apply conditional formatting to highlight cells based on certain conditions. To display two conditional formatting rules for the values 30 and 100, follow these steps:

Transaction Records

To illustrate the techniques to highlight 2 different values, we will use this sample of data.

Sample of computer sales

We want to apply 2 conditional formatting rules:

  • In green for a value greater than 800
  • In blue for the value greater than 1000

Create the first rule

Let's create or first rule for the amount greater or equal than 800

  1. Select the Range: First, select the range of cells where you want to apply the conditional formatting.
  2. Open Conditional Formatting: Go to the "Home" tab on the Excel ribbon, then click on "Conditional Formatting".
Conditional formatting menu in the ribbon
  1. Add New Rule: Choose "New Rule" from the dropdown menu.
Conditional formatting menu New Rule

In the next dialog box, follow these steps:

  1. Choose "Format cells that contain".
  2. For the first rule, select "Cell Value" from the first dropdown menu.
  3. Choose "greater than or equal to" from the second dropdown menu.
  4. Enter the value "800" in the third field.
  5. Click on the "Format" button to specify the formatting options you want to apply when the cell's value is greater than 800.
  6. Click "OK" to confirm the formatting and return to the New Formatting Rule dialog box.
Setting for the first conditional formatting rule

And the result is:

All the values above 800 are green

Create the second rule

Let's be lazy! We will duplicate the first rule

  1. Go to the menu Home > Conditional formatting > Manage Rules
  2. Select the first rule (maybe you must select This Worksheet in the top dropdown menu)
  3. Click on Duplicate Rule
Duplicate conditional formatting rule
  • Edit the second rule
  • Change the value in the third field and enter the value "1000"
  • Click on the "Format" button to specify the formatting options you want to apply when the value is 1000.
  • Click "OK" to confirm the formatting.
  • Click "Apply" to apply this new rule to your table.
Second rule in the Rule Manager to highlight 2 different colors

But ................. nothing has changed 😟 There is no blue color 😱

Only one rule displayed not the second

Apply Rules in Order

The problem isn't hard to solve. We have to correct the rules in order. The rules are applied from top to bottom, so if a cell meets the criteria for multiple rules, the formatting of the rule listed first will be applied.

  1. Open the Rules Manager (Home > Conditional formatting > Manage Rules)
  2. Select the second rule
  3. Click on the following button to put the rule in the first position.
Move up the conditionnal formating rule

In order to have the rule in this order to highlight 2 different colors in your table

Swapping the rules in order to highlight 2 different colors

And now, your document shows 2 different colors according to 2 different conditional formatting rule.

Highlight 2 different colors with conditional formatting rules

Now, any cell in the selected range containing the value 800 will be formatted according to the first rule, and any cell containing the value 1000 will be formatted according to the second rule.

Leave a Reply

Your email address will not be published. Required fields are marked *

How to have 2 different highlight colors in Excel?

Reading time: 3 minutes
Last Updated on 26/04/2024

How do you highlight 2 different colors for 2 distinct values with the conditional formatting in Excel?

  1. Write your first conditional formatting rule

    Write a test on your first value

  2. Create a second conditional formatting rule

    Write a second test on the other value

  3. Apply rules in Order

    That's the most important point of this topic. If your rules aren't in the expected order, your result couldn't be correct.

In Excel, you can apply conditional formatting to highlight cells based on certain conditions. To display two conditional formatting rules for the values 30 and 100, follow these steps:

Transaction Records

To illustrate the techniques to highlight 2 different values, we will use this sample of data.

Sample of computer sales

We want to apply 2 conditional formatting rules:

  • In green for a value greater than 800
  • In blue for the value greater than 1000

Create the first rule

Let's create or first rule for the amount greater or equal than 800

  1. Select the Range: First, select the range of cells where you want to apply the conditional formatting.
  2. Open Conditional Formatting: Go to the "Home" tab on the Excel ribbon, then click on "Conditional Formatting".
Conditional formatting menu in the ribbon
  1. Add New Rule: Choose "New Rule" from the dropdown menu.
Conditional formatting menu New Rule

In the next dialog box, follow these steps:

  1. Choose "Format cells that contain".
  2. For the first rule, select "Cell Value" from the first dropdown menu.
  3. Choose "greater than or equal to" from the second dropdown menu.
  4. Enter the value "800" in the third field.
  5. Click on the "Format" button to specify the formatting options you want to apply when the cell's value is greater than 800.
  6. Click "OK" to confirm the formatting and return to the New Formatting Rule dialog box.
Setting for the first conditional formatting rule

And the result is:

All the values above 800 are green

Create the second rule

Let's be lazy! We will duplicate the first rule

  1. Go to the menu Home > Conditional formatting > Manage Rules
  2. Select the first rule (maybe you must select This Worksheet in the top dropdown menu)
  3. Click on Duplicate Rule
Duplicate conditional formatting rule
  • Edit the second rule
  • Change the value in the third field and enter the value "1000"
  • Click on the "Format" button to specify the formatting options you want to apply when the value is 1000.
  • Click "OK" to confirm the formatting.
  • Click "Apply" to apply this new rule to your table.
Second rule in the Rule Manager to highlight 2 different colors

But ................. nothing has changed 😟 There is no blue color 😱

Only one rule displayed not the second

Apply Rules in Order

The problem isn't hard to solve. We have to correct the rules in order. The rules are applied from top to bottom, so if a cell meets the criteria for multiple rules, the formatting of the rule listed first will be applied.

  1. Open the Rules Manager (Home > Conditional formatting > Manage Rules)
  2. Select the second rule
  3. Click on the following button to put the rule in the first position.
Move up the conditionnal formating rule

In order to have the rule in this order to highlight 2 different colors in your table

Swapping the rules in order to highlight 2 different colors

And now, your document shows 2 different colors according to 2 different conditional formatting rule.

Highlight 2 different colors with conditional formatting rules

Now, any cell in the selected range containing the value 800 will be formatted according to the first rule, and any cell containing the value 1000 will be formatted according to the second rule.

Leave a Reply

Your email address will not be published. Required fields are marked *