Change the color according to the value in Excel

Change the color according to the value in Excel
Last Updated on 13/07/2023
Reading time: 3 minutes

How to change the color of your cells in Excel according to their values?

The custom rule applies to a range of cells

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.

Which cells have a value lower than 15

So in column C, let's write the test and see the result

=B2<15

Test TRUE when the value is lower than 15

Step 2: Copy your test as conditional formatting rules

  1. Copy the logical test of C2
  2. Select the cell to apply the rule, the cell B2 in our example
  3. Open the menu Home > Conditional Formatting > New Rule
Menu Condtional Formatting New Rule
  1. Select the rule type Use a formula to determine which cells to format
  2. In the text box, paste your test 😀
Copy your test as rule

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.

Format to apply 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

  1. Open the menu Conditional Formatting > Manage Rules
Menu Manage Rules
  1. When the dialog box opens, you can see that the rule only applies to B2.
The rule applies only in B2
  1. Update this field to write the range of cells to apply the rule
New range to apply the conditional formatting rule
  1. Now, all the cells where the rule is TRUE are red 😀👍😍
The custom rule applies to a range of cells

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

Same reference that is what must be done

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.

The rule doesnt apply in the correct cell

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

2 Comments

  1. Charles Chibuisi Ehiemere
    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

    Reply

  2. Bah
    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.

    Reply

Leave a Reply

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

Change the color according to the value in Excel

Reading time: 3 minutes
Last Updated on 13/07/2023

How to change the color of your cells in Excel according to their values?

The custom rule applies to a range of cells

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.

Which cells have a value lower than 15

So in column C, let's write the test and see the result

=B2<15

Test TRUE when the value is lower than 15

Step 2: Copy your test as conditional formatting rules

  1. Copy the logical test of C2
  2. Select the cell to apply the rule, the cell B2 in our example
  3. Open the menu Home > Conditional Formatting > New Rule
Menu Condtional Formatting New Rule
  1. Select the rule type Use a formula to determine which cells to format
  2. In the text box, paste your test 😀
Copy your test as rule

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.

Format to apply 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

  1. Open the menu Conditional Formatting > Manage Rules
Menu Manage Rules
  1. When the dialog box opens, you can see that the rule only applies to B2.
The rule applies only in B2
  1. Update this field to write the range of cells to apply the rule
New range to apply the conditional formatting rule
  1. Now, all the cells where the rule is TRUE are red 😀👍😍
The custom rule applies to a range of cells

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

Same reference that is what must be done

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.

The rule doesnt apply in the correct cell

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

2 Comments

  1. Charles Chibuisi Ehiemere
    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

    Reply

  2. Bah
    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.

    Reply

Leave a Reply

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