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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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 the 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 a light 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


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.

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 at the cells with food items highlighted.

Conditional Formatting to Highlight Cells Based on Text Value

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.

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


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

=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 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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo