How to Highlight Cells Based on Text in Excel (2 Methods)

If we highlight cells with specific text while working in MS Excel, it can be very useful for us to find it out easily or will help for many other purposes. This article will guide you to highlight cells in Excel based on text value with two easy methods and relevant cases.

Let’s get introduced to our dataset first. I have placed some food and clothing items and their prices for my dataset. Now, we’ll highlight specific items using 2 different methods based on text in Excel.

1. Using Excel Conditional Formatting to Highlight Cells Based on Text Value

Conditional formatting will help you to highlight cells with a certain color, depending on the cell’s values, text, and conditions.
Here, I’ll show three cases of Conditional Formatting to highlight cells in Excel based on text value.

1.1. Use of New Rule from the Conditional Formatting

I want to highlight the cells that have the cloth items’ names using the New Rule option in Conditional Formatting. The New Rule option provides you with the facility to use any suitable self-made formula for your dataset.
So, let’s see the steps one by one.

Step 1:

⏩ Click as follows: Home > Conditional Formatting > New Rule.

A dialog box will appear.

Step 2:

⏩ Select Use a formula to determine which cells to format from the Select a Rule Type drop-down.

⏩ After that, type the following formula in Format values where this formula is true box.

⏩ Click the Format option.

Another dialog box named “Format Cells” will open up.

Step 3:

⏩ Now choose your desired color from the Fill tab. I have chosen a light green color.

⏩ Then press the OK and we’ll go back to our previous dialog box.

Step 4:

⏩ Later, just press OK again.

Now you will see that cloth items are highlighted with our picked color.

1.2. Using “Text That Contains…” Feature from the Conditional Formatting

Here, we’ll use another option from Conditional Formatting named Text that Contains. I’ll highlight the food items here using this. You can use it for partial text too.

Step 1:

⏩ Click serially: Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.

Then, you will get a dialog box.

Step 2:

⏩ Type Food in the Format cells that contain the text bar.

⏩ Then pick your desired highlight color, and press OK.

Now take a look at the cells with food items highlighted.

Read More: Highlight Cells That Contain Text from a List in Excel

1.3. Applying “Equal To…” Option from the Cell Rules for Highlighting

We can only use it when we will format the whole text in a cell; it’s not applicable for partial text.

Step 1:

⏩ Click successively: Home > Conditional Formatting > Highlight Cells Rules > Equal To

A dialog box will open up.

Step 2:

⏩ Type Food in the Format cells that are EQUAL TO box.

⏩ Then, pick your desired highlight color.

⏩ Press OK.

Then, we’ll observe that all the cells with food items are highlighted.

Read More: How to Highlight Cells in Excel Based on Value

2. Combining ISNUMBER & SEARCH Functions to Highlight Cells Based on Text Value in Excel

In our last method, we’ll use the combination of two functions to highlight cells in a different way. Those are the ISNUMBER function and the SEARCH function. Instead of using color, we’ll highlight with a remark here. If specified text is found, then it will show TRUE, and if not, then it will show FALSE. That’s why I have added a new column in my dataset for showing remarks. We’ll do it here for food items. The  ISNUMBER function returns TRUE when a cell contains a number and FALSE if not. The SEARCH function returns the location of a substring in a string.

Step 1:

⏩ Type the formula in cell E5–

⏩ Hit the Enter button.

Then, double-click the Fill Handle icon to copy the formula over the rest of the cells.

Finally, here’s our output.

⏬ Formula Breakdown:

⏩ SEARCH(“Food”,C5)

The SEARCH function will return the position of the text “Food” in cell C5; if the text is not found, then it will show #VALUE!. So, it will return as:

1

⏩ ISNUMBER(SEARCH(“Food”,C5))

Finally, the ISNUMBER function will return TRUE if it gets any number and return FALSE if it gets #VALUE!. It will return for cell C5–

TRUE

Read More: How to Highlight Selected Cells in Excel

Download Sample Excel Workbook

You can download the free Excel template from here and practice on your own.

Conclusion

I hope all of the methods described above will be good enough to highlight cells in Excel based on text. Feel free to ask any questions in the comment section, and please give me feedback.

Related Articles

• How to Highlight Blank Cells with Conditional Formatting in Excel
• How to Highlight Cell If Value Is Less Than Another Cell in Excel
• How to Highlight Cells in Excel but Not Print
• Cells Are Not Highlighting in Excel Formula
• How to Click One Cell and Highlight Another in Excel
• How to Highlight Cell Using the If Statement in Excel 
  

<< Go Back to Highlight Cell | Highlight in Excel | Learn Excel