Excel conditional formatting based on another cell text [5 ways]

Last updated on May 19th, 2018

In this article, we will be familiarized with an interesting topic which is “Excel Conditional Formatting Based on Another Cell Text”. Excel Conditional Formatting makes it easy to highlight data in your worksheets. In this article, we will see different processes of highlighting cells that contain text using conditional formatting.

Highlighting Cells Based on Another Cell Text with Formula

You can use the search option to highlight specific cells in conditional formatting. In this case, we will be identifying the cells containing the word “Passed”. Here in B2 cell, we have the text which is displaying “Passed the Exam”. We will be targeting this cell to perform the conditional formatting for the entire range of cells. The procedure is given below.

  • First, write down the text “Pass” in cell C2.

  • Select the range of cells where you want to apply the conditional formatting.

  • Now, select the New Rule from Conditional Formatting option under the Home.

  • In the New Formatting Rule dialogue box select Use a formula to determine which cells to format and type =SEARCH($C$2,A1)>0 in Format values where this formula is true box.

  • Click on to the Format option to open the Format Cells dialogue box. In the Format Cells dialogue box under the Fill option select the color you want and press OK.

  • After this, you can see the color you choose in the Preview option.

Excel Conditional Formatting Based on Another Cell Text

  • Now press OK in the New Formatting Rule dialogue box to apply the formatting. You will get the below result after this.

  • In the same way, if you want to find the students who failed the exam but can sit for a retake you just need to write “Retake” instead of “Pass” in cell C2.

Excel Conditional Formatting Based on Another Cell Text

Note: The formula =SEARCH($C$2,A1)>0 will highlight all cells where the word “Retake” is located whereas the formula =SEARCH($C$2,A1)>1 will highlight only the cells which start with the word “Retake”.

Excel Conditional Formatting Entire Row Based on One Cell Text

Let`s say you want to highlight the names of the candidate along with the status of the final exam. Let`s work on the students who can sit for a retake. Here we will be using the same formula with a slight change. The formula we will be using is, =SEARCH($C$2,$B1)>0. So, in the New Formatting Rule dialogue box, we will use this formula.

Excel Conditional Formatting Based on Another Cell Text

After pressing OK in the New Formatting Rule dialogue box, we will get the below result.

Excel Conditional Formatting Entire Row Based on One Cell Text

Note: Don’t forget to select the range before applying the conditional formatting.

Highlight cells containing specific text using “Highlight Cells Rules”

Conditional Formatting makes it easy to highlight the cells which contain some specific text. Let’s say you have some colors in three separate columns. From them, you want to highlight the colors red, yellow and green. The color list is given below.

Let`s highlight the colors red, yellow and green from the above list. The procedure is stated below.

  • First, select the cells from which you want to perform the highlighting.

  • Now, Under the Home tab select the Conditional Formatting option.
  • Under the Highlight Cells Rules option select the Text that Contains option.

Highlight cells containing specific text using “Highlight Cells Rules”

  • In the Text that Contains dialogue box write Red in Format cells that contain the text option and select Light Red Fill with Dark Red Text option and press OK.

  • You will get to see the below result after that.

Now, to highlight the Yellow text in the sheet do the following after you open the Text That Contain dialogue box. Don’t forget to select the range first.

  • In the Text That Contains dialogue box just select Yellow in Format cells that contain the text option and select Light Yellow Fill with Dark Yellow Text option and press OK.

  • You will find the below result by doing this.

  • In the same way, Green texts can be highlighted in the picture below.

Another Process of Highlighting Text using Conditional Formatting

There is another detailed way to highlight cells with specific text. The procedure is given below.

  • First, select the cells where you want to apply the conditional formatting.

  • Now, select the New Rule from Conditional Formatting option under the Home tab.

  • In the New Formatting Rule dialogue box select Format only cells that contain and, in the Format only cells with option select Specific Text. Select Containing beside it and write Blue after that.

  • Click on the Format option to choose the color you want. After selecting the color, you will get to see it in the Preview Press OK to continue the formatting.

Highlighting Text using Conditional Formatting

  • You will see the below result after that.

Download The Working File

You can download the two worksheets for practicing these conditional formatting by clicking the link below.

Conclusion

Highlighting different text is an important task that you have to perform in Excel. You can choose these options which include the conditional formatting process. Highlighting cells with conditional formatting is easy and once your task is performed you can also erase the conditional formatting easily.

Hope you will like this article. Tell us if you find any other process of highlighting cells containing text using conditional formatting.

Siam Hasan Khan on FacebookSiam Hasan Khan on Linkedin
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 attitude to grow continuously. Continuous improvement and life-long learning is my motto.

We will be happy to hear your thoughts

      Leave a reply