Excel Formula to Color a Cell If the Value Follows a Condition

In this article, I’ll show you how you can create an Excel formula to color a cell if the value follows a specific condition. You’ll learn the suitable methods to color cells of a single column, along with how to color cells of multiple columns depending on a single column.


Download Practice Workbook


Excel Formula to Color a Cell If the Value Follows a Condition

Here we’ve got a data set with the Names, Book Types and Prices of some books of a book shop called Martin Bookstore.

Data Set to Create Excel Formula to Color a Cell If the Value Follows a Condition

Today our objective is to color the cells from this data set following some conditions.


1. Excel Formula to Color a Cell of a Single Column If the Value is Equal to Something

First of all, we’ll color the cells of a single column if the values are equal to something.

For example, let’s try to color the cells of the column Book Type if the book is a novel.

I am breaking it down step by step so that it becomes clear for you how to accomplish this.

Step 1:

Select the column you want to color (Without the Column Header).

Here I have selected the column Book Type (C4:C13).

Selecting Single Column to Create Excel Formula to Color a Cell If the Value Follows a Condition

Step 2:

Go to the Home > Conditional Formatting > New Rule tool in Excel Toolbar.

Click on New Rule.

Step 3:

A dialogue box called New Formatting Rule will open.

Click on Use a formula to determine which cells to format.

New Formatting Rule Box to Create Excel Formula to Color a Cell If the Value Follows a Condition

Step 4:

Then put your cursor into the box called Format values where this formula is true.

Enter any of the following formulas:

=C4="Novel"

Or

=$C4="Novel"

Entering Excel Formula to Color a Cell If the Value Follows a Condition

Notes:

  • To color a single column based on some condition using Conditional Formatting, use the Relative Cell Reference or the Mixed Cell Reference (lock the column, not the row) of the first cell inside the formula.
  • Here, C4 is the first cell of the column Book Type. So I can use either C4 or $C4, but not C$4 or $C$4.
  • And the text “Novel” is my criteria. I want to find out the cells whose values are equal to the text “Novel”. You put it according to your need.

Step 5:

Then click on Format. A dialogue box called Format Cells will open.

Select your desired color from the tab Fill. Here I have selected the color light brown.

Step 6:

Click on OK. You will be directed back to the New Formatting Rule box.

Then again click on OK. You will find the cells of your selected column that fulfill the criteria marked in your chosen color.

Output after Applying Excel Formula to Color a Cell If the Value Follows a Condition

Additional Example:

You can also color the cells of the column Price that maintain a condition in this way.

For example, let’s try to color the cells that have a price greater than or equal to $25.00.

The steps are all same.

Just in Step 1, select the column Price.

Selecting the Column to Create Excel Formula to Color a Cell If the Value Follows a Condition

And in Step 3, replace the formula with this:

=D4>=25 

Or

=$D4>=25

Entering Excel Formula to Color a Cell If the Value Follows a Condition

And you will get the cells that contain a price greater than or equal to $25.00 marked in your chosen color.

You can use the same method to color cells of a single column that are less than a particular value.

Just use the less-than sign (<) in the formula.


2. Excel Formula to Color a Cell of a Single Column If the Value Contains a Specific Text

In the previous example, we saw how to color the cells of a single column that are greater than, less than, or equal to value.

Now we’ll see how to color the cells of a single having a specific text in them.

For example, let’s try to color the Book Names with the text “History” in them.

The steps are all same.

Just in Step 1, select the column Book Name.

Selecting Column to Create Excel Formula to Color a Cell If the Value Follows a Condition

And in Step 3, replace the formula. You can use two different formulas for case-sensitive matches and case-insensitive matches.

Formula for Case-Sensitive Match:

If you want a case-sensitive match, use this formula consisting of the IF function, the ISERROR function, and the FIND function.

=IF(ISERROR(FIND("History",B4)),FALSE,TRUE)

Or

=IF(ISERROR(FIND("History",$B4)),FALSE,TRUE)

Entering Excel Formula to Color a Cell If the Value Follows a Condition

After inserting the formula, choose your desired color from the Format Cells dialogue box.

Then click OK twice.

You will get the books that have the text “History” in their names marked in your desired color.

Explanation of the Formula:

  • FIND(“History”,B4) returns an integer if it finds the text “History” (case-sensitive match) inside cell B4. Otherwise, returns a value error.
  • Let the cell B4 doesn’t contain the text “History”. So, now the formula becomes IF(ISERROR(#VALUE!),FALSE,TRUE).
  • The ISERROR function returns a TRUE if it finds an error. So, now the formula becomes IF(TRUE,FALSE,TRUE).
  • The IF function returns the 2nd argument if the 1st argument is TRUE. Otherwise, returns the 3rd argument.
  • Therefore, it will return the 2nd argument, FALSE.
  • Similarly, if cell B4 had contained the text “History”, the formula would have returned TRUE.

Note:

This formula works for a case-sensitive match. So “history” or “HISTORY” in place of “History” won’t work.

Formula for Case-Insensitive Match:

And if you want a case-insensitive match, use this formula consisting of the IF function, ISERROR function, and the SEARCH function.

=IF(ISERROR(SEARCH("history",B4)),FALSE,TRUE)

Or

=IF(ISERROR(SEARCH("history",$B4)),FALSE,TRUE)

Entering Excel Formula to Color a Cell If the Value Follows a Condition

After inserting the formula, choose your desired color from the Format Cells dialogue box.

Then click OK twice.

You will get the books that have the text “History” or “history” or “HISTORY” or son on in their names marked in your desired color.

Explanation of the Formula:

  • SEARCH(“history”,B4) returns an integer if it finds the text “history” (case-insensitive match) inside cell B4. Otherwise, returns a value error.
  • Let the cell B4 doesn’t contain the text “history”. So, now the formula becomes IF(ISERROR(#VALUE!),FALSE,TRUE).
  • The ISERROR function returns a TRUE if it finds an error. So, now the formula becomes IF(TRUE,FALSE,TRUE).
  • The IF function returns the 2nd argument if the 1st argument is TRUE. Otherwise, returns the 3rd argument.
  • Therefore, it will return the 2nd argument, FALSE.
  • Similarly, if cell B4 had contained the text “history”, the formula would have returned TRUE.

Note:

This formula works for a case-insensitive match. So “History” or “HISTORY” in place of “history” will also work.


3. Excel Formula to Color a Cell of Multiple Columns Depending on the Value of a Single Column

Up till now, we have colored the cells of only a single column based on their values.

If you want to color the cells of multiple columns based on the values of a single column, you must use the Mixed Cell Reference in the formula (Locking the Column).

For example, let’s try to color the columns Book Name and Book Type depending on the values of the column Book Type.

We’ll color a book only if it’s a novel.

The steps are the same. Just in Step 1, select both the two columns.

Selecting Multiple Columns to Create Excel Formula to Color a Cell If the Value Follows a Condition

And in Step 3, you must enter the formula with the Mixed Cell Reference:

=$C4="Novel"

Entering Excel Formula to Color a Cell If the Value Follows a Condition

After inserting the formula, choose your desired color from the Format Cells dialogue box.

Then click OK twice.

You will get the cells in both the columns marked in your chosen color if the book is a novel.

Additional Example 1:

You can also color the cells of the column Book Name and Price if the price is greater than or equal to $25.00 in this way.

In Step 1, select the two columns Book Name and Price.

Selecting Multiple Columns to Create Excel Formula to Color a Cell If the Value Follows a Condition

And in Step 3, enter this formula with Mixed Cell Reference:

=$D4>=25

Then choose your desired color from the Format Cells dialogue box.

Then click OK twice.

You will get the cells in both the columns marked in your chosen color if the price is greater than or equal to $25.00.

Additional Example 2:

Let’s try to color the whole data set if the Book Name contains the text “History”.

In Step 1, select the whole data set.

Selecting Columns to Create Excel Formula to Color a Cell If the Value Follows a Condition

And in Step 3, enter this formula with Mixed Cell Reference:

=IF(ISERROR(FIND("History",$B4)),FALSE,TRUE)

Then choose your desired color from the Format Cells dialogue box.

Then click OK twice.

You will get the cells in all the three columns marked in your chosen color if the book name has the text “History” in it.

Excel Formula to Color Cell of Multiple Columns If the Value Has a Specific Text


Things to Remember

  • To color the cells of a single column, you can use either the Relative Cell Reference or the Mixed Cell Reference (Locking the Column) in the formula.
  • But to color the cells of multiple columns based on a single column, you must use the Mixed Cell Reference (Locking the Column) in the formula.
  • The FIND function goes for a case-sensitive match, and the SEARCH function goes for a case-insensitive match.
  • Therefore, use the FIND function if you want a case-sensitive match. Use the SEARCH function otherwise.

Frequently Asked Question

Why must we use Mixed Cell Reference while coloring cells of multiple columns based on a single column?

Well, to get the answer to this question, first we need to know how Conditional Formatting actually works in Excel.

When we select multiple cells and apply a formula to them through Conditional Formatting, the formula is applied to the first cell of the selected range.

Then it’s copied to the rest of the selected cells, just like we copy a formula in our worksheet from one cell to another by dragging the Fill Handle.

Let’s understand it through an example.

When we apply the formula =C4=”Novel” to the selected range B4:C13 through Conditional Formatting, cell B4 gets the formula =C4=”Novel”.

Then cell B5 gets =C5=”Novel”.

Cell B6 gets =C6=”Novel”.

This is OK. For column B, there will be no problem.

But when we go to column C, cell C4 will get the formula =D4=”Novel”. Because the formula will be copied rightwards.

This is not what we want. We want to color cell C4 based on the value of cell C4, that whether it contains “Novel” or not.

So, we want the formula =C4=”Novel” in cell C4, not =D4=”Novel”.

That’s why we need to insert the Mixed Cell Reference. If we use =$C4=”Novel”, it will remain =C4=”Novel” when it is copied from column B to column C.

But we lock only the column, not the row. Because we want the cell reference to increase by 1 when it is copied downwards.

This is the reason we need to use Mixed Cell Reference in the formula to color cells of multiple columns based on a single column.

Hope you have got it.


Conclusion

Using these methods, you can create an Excel formula to color a cell if the value follows a condition. Do you know any other method? Or do you have any problems? Feel free to ask us.


Related Articles

How to Count Colored Cells in Excel (4 Simple Ways)

Excel Cell Color: Add, Edit, Use & Remove

How to Highlight Highest Value in Excel (3 Quick Ways)

How to Compare Rows in Excel for Duplicates

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo