Excel Formula to Color Cell If It Has Specific Value

Here’s an overview of coloring cells based on various conditions.

excel formula color cell if value


Excel Formula to Color Cell If the Value Follows a Condition: 3 Approaches

We’ve got a data set with the Names, Book Types, and Prices of some books of a book shop. We’ll color the cells based on various conditions.

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


Method 1 – Excel Formula to Color Cells of a Column Based on a Specific Value

Let’s color the cells of the column Book Type if the book is a novel.

Steps:

  • Select the column you want to color (without the Column Header). We have selected the column Book Type (C5:C14).

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

  • Go to the Home tab and select Conditional Formatting.
  • Click on New Rule.

Selecting Conditional Formatting from the Ribbon

  • A dialog 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

  • In the box called Format values where this formula is true, enter any of the following formulas:
=C5="Novel"

OR

=$C5="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.
  • C4 is the first cell of the column Book Type. We can use either C4 or $C4, but not C$4 or $C$4.
  • And the text “Novel” is my criteria. We want to find out the cells whose values are equal to the text “Novel”.
  • Click on Format. A dialog box called Format Cells will open.

Triggering Format Cells panel

  • Select your desired color from the tab Fill. We have selected light brown.

Selecting Color for the Cells

  • Click on OK. You will be directed back to the New Formatting Rule box.
  • Click on OK again.
  • 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. Let’s color the cells that have a price greater than or equal to $25.00. The steps are all the largely the same with the following exceptions:

  • Select the column Price when choosing the columns.

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

  • In the New Formatting Rule box, replace the formula with this:
=D5>=25 

Or

=$D5>=25

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

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

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

You can use the same method to color cells of a single column that are less than a particular value. Use the less-than sign (<) in the formula.


Method 2 – Excel Formula to Color Cells of a Column If It Contains Specific Text

We’ll color the Book Names with the text “History” in them. The steps are all the same as in Method 1. The changes are:

  • Select the column Book Name.

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

  • For Conditional Formatting New Rule, you can use different formulas for case-sensitive matches and case-insensitive matches.

Formulas for Case-Sensitive Match:

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

OR

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

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

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

Output after Applying Excel Formula to Color a Cell If the Value Contains a specific text

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

Formulas for Case-Insensitive Match:

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

OR

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

Case In-sensitive Excel Formula to Color a Cell If the Value contains a specific text

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

Output after Applying Excel Formula to Color a Cell If the Value Contains a specific text

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.


Method 3 – Excel Formula to Color Cells in Multiple Columns Depending on the Value of a Single Column

We’ll 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 as in Method 1, with the following exceptions:

  • Select both of the columns when applying Conditional Formatting.

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

  • Use the following formula with the Mixed Cell Reference in the formula box for the New Formatting Rule dialog.
=$C5="Novel"

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

  • Choose your desired color from the Format Cells dialogue box.
  • Click OK twice. You will get the cells in both the columns marked in your chosen color if the book is a novel.

Multiple columns colored based on a single column cell value

Additional Example:

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:

  • Select the two columns Book Name and Price.
  • Open Conditional Formatting and New Rule.

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

  • Enter this formula with a Mixed Cell Reference:
=$D5>=25

formula for coloring multiple columns cells based on a single column cell value

  • Choose your desired color from the Format Cells dialogue box.
  • 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.

Multiple columns colored based on a single column cell value


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.
  • 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.

Frequently Asked Question

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

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.


Download the Practice Workbook


Related Articles


<< Go Back to Color Cell in ExcelExcel Cell FormatLearn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo