Apply Conditional Formatting Based on Another Text Cell in Excel

In this article, we will be familiarized with an interesting topic which is conditional formatting based on another text cell in Excel. Conditional Formatting makes it easy to highlight data in your worksheets. In this article, we will see how to apply conditional formatting based on another text cell in excel in 4 easy ways.

Get this sample file to practice by yourself.

4 Easy Ways to Apply Conditional Formatting Based on Another Text Cell in Excel

To describe the process, we have prepared a dataset here. The dataset shows the information of the Annual Transcript of 7 students with their Names, Obtained Marks and Status.

Following, we inserted the condition Pass in cell C13.

Now, let’s highlight the dataset based on this condition following the methods below.

1. Apply Conditional Formatting with Formula Based on Another Text Cell

In this first process, we will use the SEARCH function to apply conditional formatting and find the required text. Let’s see the steps below:

• First, select cell range B4:D11 where you want to apply the conditional formatting.

• Now, go to the Home tab and select Conditional Formatting.

• Under this drop-down, select New Rule.

• Next, in the New Formatting Rule dialogue box, select Use a formula to determine which cells to format.
• Here, insert this formula in the Format values where this formula is true box.

• Following, click on the Format option to open the Format Cells dialogue box.

• Here, in the Format Cells dialogue box under the Fill option, select the color you want. We can see the color preview in the Sample section.

• Lastly, press OK twice to close all dialogue boxes.
• Finally, you will get the below result after this.

Here, we used the SEARCH function to return the cell text in C13 inside cell range B4:D11 and highlight it afterward.

2. Highlight Entire Row Based on Another Cell Using Excel Formula with Conditional Formatting

Let’s say you want to highlight the names of the students along with the status of the final exam. Let’s work on the students who have passed. Here we will be using the 3 types of formulas to highlight the entire row.

2.1. Apply SEARCH Function

The first option to highlight the entire row is to use the SEARCH function. Follow the process below.

• First, select the whole dataset.
• Then, open the New Formatting Rule dialogue box as described in the first method.
• Here, insert this formula.

• Along with it, change the color from the Format > Fill > OK option.

• Lastly, again press OK and you will see the result.

Here, we used the SEARCH function to return the cell text in C13 inside cell D4 making it unchangeable so that the formula gets repeated throughout this column.

2.2. Use AND Function

Another helpful technique for highlighting the entire row is to apply the AND function in excel. Follow the process below.

• First, like the previous method, insert this formula in the New Formatting Rule dialogue box.

• Afterward, change the color from the Format > Fill tab as described above.
• Lastly, press OK and get the final output.

Here, we applied the AND function to determine more than one condition at the same time for the selected cell range B4:D11.

2.3. Insert OR Function

The OR function also works for highlighting the total row based on the cell text.

• In the beginning, select cell range B4:D11.
• Then, Home > Conditional Formatting > New Rule.
• Insert this formula in the New Formatting Rule dialogue box.

• Next, change the color and press OK.
• That’s it, you will see the entire row is highlighted now.

Here, we applied the OR function to determine whether at least one condition is true from multiple criteria according to the cell text.

3. Insert Data Validation for Conditional Formatting in Excel

Data Validation is very interesting in the case of conditional formatting based on another cell. Carefully go through the process.

• In the beginning, select cell C13 as we want to imply data here.

• Then, go to the Data and select Data Validation under the Data Tools group.

• Now, in the Data Validation dialogue box, select List as the Validation criteria.

• Following, insert the conditions Pass and Fail in the Source box.
• Next, press OK.

• Therefore, you will see that cell C13 has the list of conditions to be selected.

• Now, select cell range D5:D11.

• Then, insert this formula in the New Formatting Rule dialogue box like the previous methods.

• After that, choose a color from the Format > Fill tab and press OK > OK.

• That’s it, you have got the required output.

• Lastly, for proof checking, change the condition to Fail and the highlighted cells will be changed automatically.

4. Excel Conditional Formatting Based on Specific Text

In this last section, let us try the Specific Text option to apply conditional formatting. Here are two ways to do this.

4.1. Apply New Formatting Rule

This first one will be done directly from the conditional formatting tab.

• First, go to the Data tab and select Conditional Formatting.
• Then, select Text that Contains from the Highlight Cell Rules section.

• After this, insert cell C13 as Cell Text.

• Along with it, change the color just like the image below:

• Lastly, press OK and see the final output.

4.2. Use Highlight Cells Rules

Another way is to highlight cells from the New Formatting Rule dialogue box.

• In the beginning, select Format only cells that contain as the Rule Type.

• Then, choose Specific Text under the Format only cells with section.

• Next, insert the cell reference as shown below.

• Lastly, change the highlight color from the Format > Fill tab > OK.

• That’s it, press OK and you will see the cells are highlighted according to the other cell text.

Things to Remember

• Before applying conditional formatting, always select the cells where you will apply the condition.
• You can always clear the condition either from a single sheet or the entire workbook with the Clear Rules command in the Conditional Formatting tab.

Conclusion

Hope you will find this article on how to apply conditional formatting based on another text cell in excel in 4 easy ways very helpful. Tell us if you find any other process. Keep an eye on ExcelDemy for more exciting articles like this.