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.

**Table of Contents**hide

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

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

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

**⧪**** 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"`

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

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

And in **Step 3**, replace the formula with this:

`=D4>=25 `

Or

`=$D4>=25`

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

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)`

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)`

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.

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

`=$C4="Novel"`

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

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.

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.

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