How do you highlight 2 different colors for 2 distinct values with the conditional formatting in Excel?
- Write your first conditional formatting rule
Write a test on your first value
- Create a second conditional formatting rule
Write a second test on the other value
- 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.
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
- Select the Range: First, select the range of cells where you want to apply the conditional formatting.
- Open Conditional Formatting: Go to the "Home" tab on the Excel ribbon, then click on "Conditional Formatting".
- Add New Rule: Choose "New Rule" from the dropdown menu.
In the next dialog box, follow these steps:
- Choose "Format cells that contain".
- For the first rule, select "Cell Value" from the first dropdown menu.
- Choose "greater than or equal to" from the second dropdown menu.
- Enter the value "800" in the third field.
- Click on the "Format" button to specify the formatting options you want to apply when the cell's value is greater than 800.
- Click "OK" to confirm the formatting and return to the New Formatting Rule dialog box.
And the result is:
Create the second rule
Let's be lazy! We will duplicate the first rule
- Go to the menu Home > Conditional formatting > Manage Rules
- Select the first rule (maybe you must select This Worksheet in the top dropdown menu)
- Click on Duplicate 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.
But ................. nothing has changed 😟 There is no blue color 😱
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.
- Open the Rules Manager (Home > Conditional formatting > Manage Rules)
- Select the second rule
- Click on the following button to put the rule in the first position.
In order to have the rule in this order to highlight 2 different colors in your table
And now, your document shows 2 different colors according to 2 different conditional formatting rule.
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.
