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.

Sample Dataset: How to Highlight Cells 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.

Excel Conditional Formatting to Highlight Cells Based on Text Value: Select New Rule

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.

=$C5="Cloth"

⏩ Click Format option.

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

Create New Rule in Conditional Formatting to Highlight Cells Based on Text Value

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.

Fill the highlighted cells with color

Step 4:

⏩ Later, just press OK again.

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

Output: New Rule from Conditional Formatting in Excel to highlight cells based on text value

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.

Conditional Formatting to Highlight Cells Based on Text Value

Step 2:

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

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

Conditional Formatting to Highlight Cells Based on Text Value

Now take a look that cells with food items are highlighted.

Conditional Formatting to Highlight Cells Based on Text Value

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.

Conditional Formatting to Highlight Cells Based on Text Value

Step 2:

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

⏩ Then pick your desired highlight color.

⏩ Press OK.

Conditional Formatting to Highlight Cells Based on Text Value

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

Conditional Formatting to Highlight Cells Based on Text Value

Read More: How to Highlight Cells in Excel Based on Value (9 Methods)


Similar Readings:


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

=ISNUMBER(SEARCH("Food",C5))

⏩ Hit the Enter button.

Conditional Formatting to Highlight Cells Based on Text Value

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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo