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.
Excel Formula to Color Cell If Value Follows a Condition: 3 Approaches
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 Cells of a Column Based on Specific Value
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: Selecting the Cells
- Select the column you want to color (Without the Column Header).
- Here I have selected the column Book Type (C5:C14).
Step 2: Opening Conditional Formatting
- Go to the Home > Conditional Formatting > New Rule tool in the Excel Toolbar.
- Click on New Rule.
Step 3: Rule Type Selection
- A dialogue box called New Formatting Rule will open.
- Click on Use a formula to determine which cells to format.
Step 4: Formula for Specific Value
- Then put your cursor into the box called Format values where this formula is true.
- Enter any of the following formulas:
=C5="Novel"Or
=$C5="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: Applying Colors
- 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: Applying the Set-up
- 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 the same. The few exceptions are:
- In Step 1, select the column Price.
- And in Step 3, replace the formula with this:
=D5>=25 Or
=$D5>=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 Cells of a Column If It Contains 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 the same. The changes are:
- In Step 1, select the column Book Name.
- Furthermore, 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",B5)),FALSE,TRUE)Or
=IF(ISERROR(FIND("History",$B5)),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 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 so 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 Cells 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. The exceptions are below:
- In Step 1, select both the two columns.
- And in Step 3, you must enter the formula with the Mixed Cell Reference:
=$C5="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:
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:
- 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.
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 why 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.
Download Practice Workbook
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
- Uses of CELL Color A1 in Excel
- How to Color Code Cells in Excel
- How to Get Cell Color in Excel
- VBA to Change Cell Color Based on Value in Excel
- How to Change Cell Color Based on a Value in Excel
- How to Fill Color in Cell Using Formula in Excel
- How to Fill Cell with Color Based on Percentage in Excel
- How to Change Text Color with Formula in Excel
- How to Apply Formula Based on Cell Color in Excel
