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.


Download Practice Workbook

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.

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

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.

Apply Conditional Formatting with Formula Based on Another Text Cell

  • 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.
=SEARCH($C$13, B4)>0

Apply Conditional Formatting with Formula Based on Another Text Cell

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

Note: You can use this formula =SEARCH($C$13, B4)>1 to highlight only the cells which start with the word “Pass” in your database. For example, Pass with distinction or Pass with conditions etc.

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.
=SEARCH($C$13,$D4)>0

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

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

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

Excel Conditional Formatting Based on Another Cell Text

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.
=AND($D5="Pass",$C5>40)

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

  • 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.
=OR($D5="Pass",$C5>40)

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

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

Insert Data Validation for Conditional Formatting in Excel

  • 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.
=D5=$C$13
  • 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.

Excel Conditional Formatting Based on Specific Text

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

Excel Conditional Formatting Based on Specific Text

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

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

2 Comments
  1. On an Excel sheet, I need a conditional formatting rule to format every cell containing any value that is >=1, in various non-contiguous ranges: (B5:U10,B14:K20,B24:K24,B28:K33,B37:K46,B50:K59,B63:K71,B75:K84,B88:K97,B101:K109,B113:K122,B126:K131,B135:K136), BUT I want the specific formatting of the cells (not the entire rows) that meet that criteria (>=1) within each row, determined by one of three search terms contained within the full text (search term is just a part of the whole text string) of a cell adjacent to each row (in Column A).

    As an example (looking at only one row): Within B5:KY5, numerous, but not all, cells contain values greater than or equal to 1. Cell A5 contains text that includes one of my search terms (I’ll call that term “Term1”). Cells within the range of B5:KY5 that have values greater than or equal to one (1) should therefore be formatted according to the search term in A5 (“Term1”).

    Any help would be greatly appreciated.

    I assume that I will then have to repeat the steps to create Conditional Formatting rules; changing the formatting to coincide with the other two search terms (“Term2” and “Term3”).

  2. Hello Mark,
    We have sent you a mail. Can you see it and give us a response?

Leave a reply

ExcelDemy
Logo