How to change the color of your cells in Excel according to their values?
Everything is based on a logical test
A logical test returns TRUE or FALSE, or also 0 and 1. And it's exactly what we need to create our own rule.
- The test returns TRUE, then we apply of custom format
- The test returns FALSE, the conditional formatting is not applied
For many people, there is a confusion between a logical test and the IF function. The first argument of the IF function is the logical test and it's just what we need.
Step 1: Write your test in a cell
The best way to build your logical test is to write it in a cell.
For instance, you want to highlight all the cells with a value lower than 15.
So in column C, let's write the test and see the result
Step 2: Copy your test as conditional formatting rules
- Copy the logical test of C2
- Select the cell to apply the rule, the cell B2 in our example
- Open the menu Home > Conditional Formatting > New Rule
- Select the rule type Use a formula to determine which cells to format
- In the text box, paste your test 😀
Step 3: Change the color, or any Excel format
When you click on the button format, you can change any of the following format aspects
- The font
- Number format
- Background color
For instance here, we change only the color of the background when the test is TRUE.
Validate your rule to close the New Rule dialog box
Step 4: Apply the same rule for many cells
At this step, the rule applies only to cell B2. But now, we need to apply the same formula for the range B2:B9
- Open the menu Conditional Formatting > Manage Rules
- When the dialog box opens, you can see that the rule only applies to B2.
- Update this field to write the range of cells to apply the rule
- Now, all the cells where the rule is TRUE are red 😀👍😍
Create your own rules to change the color is a very common task in Excel. Here is a list of other articles where we use the same technique.
Why it doesn't work in my workbook 😱😱
One thing is very, very, VERY important is to select the cell when you create your rule
If you haven't selected the correct cells when you have written or pasted your test, the rule applies in the wrong cell.
For instance, here, we have selected the cell B4 when we have passed the test.
This is the only reason why you can have an issue between the result expected and the result you want 😉👍
In this video, you will see 3 examples with 3 different techniques