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.

Download Sample Excel Workbook

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

2 Ways to Highlight Cells Based on Text Value in Excel

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

Method 1: Use 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.

Case 1: New Rule

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

Case 2: Text that Contains

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 that cells with food items are highlighted.

Case 3: Equal To

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 (9 Methods)

Similar Readings:

• How to Highlight from Top to Bottom in Excel (5 Methods)
• How to Highlight a Row in Excel (5 Quick Methods)
• Excel Formula Based on Cell Color (5 Examples)
• How to Fill Color in Excel Cell Using Formula (5 Easy Ways)
• Excel Cell Color: Add, Edit, Use & Remove

Method 2: 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 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 Text in Excel (8 Ways)

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 Compare Two Excel Sheets and Highlight Differences (7 Ways)
• Highlight Cells That Contain Text from a List in Excel (7 Easy Ways)
• How to Highlight Cell Using the If Statement in Excel (7 Ways)
• How to Highlight Every 5 Rows in Excel (4 Methods)
• VBA to Change Cell Color Based on Value in Excel (3 Easy Examples)