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
=B2<15
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
- Border
- 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 😀👍😍
Other examples
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 😉👍
Tutorial video
In this video, you will see 3 examples with 3 different techniques
02/05/2021 @ 11:09
Thank you for these great lessons. However, I have a question. If I want to conditional format a particular cell amongst cells.
For example for my company from Mondays - Thursdays you are expected to work for 8 hours (8 am-5 am) with an 1-hour break, and Fridays work is 5 hours (8 am-1 pm). Now on the timesheet how can I condition format Fridays to turn red when a staff mistakenly enters more than 5 hours. Because it is expected that to work overtime you will have to get approval from one superior. So the cell turning read will be fine so the staff can provide reasons for working overtime and approval from his/her supervisor.
I need your support on this sir
08/09/2020 @ 20:04
Hello Mr / Mrs;
I am very happy to discover your website mainly concerning Excel courses;
this is why I take this opportunity to ask you for a service on the concepts of excel, namely:
Someone ask me, to do the conditional formatting of the cells but the excel file is in a server and online on web page,
when I apply the function, Excel only takes the last condition into account. I do not able access to do formatting conditional.
i am able to do it but off the server and i am told it online.
that's my problem;
Bah; thanks for helping me.