How to Highlight Cell If Contains Text in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

We like to have our data sorted. Sometimes, we might face the necessity to differentiate cells containing text from the entire dataset. In this article, I will explain step-by-step procedures to highlight cell if contains text in Excel. I hope it will be very helpful for you if you are looking for an efficient way to do so.


How to Highlight Cell If Contains Text in Excel: with Easy Steps

With the help of Conditional Formatting along with the ISTEXT function, we can highlight the cell if contains text in Excel. Let’s dive into detail.


Step 1: Create an Organized Dataset to Highlight Cell If Contains Text

  • In order to differentiate cells containing text from the dataset, it is the first and foremost criterion to have a proper dataset with both text and numbers. Here, I have organized a dataset with the Product ID, Product, and Price columns.

Excel Highlight Cell If Contains Text

Read More: How to Return TRUE If Cell Contains Text in Excel


Step 2: Assign a Formula for Conditional Formatting

  • In order to separate cells containing text with Conditional Formatting, we need to assign a formula. For this, select the entire dataset and go to the Home tab.
  • Next, click on Conditional Formatting from the ribbon and choose New Rule…

Assign a Formula for Conditional Formatting to Highlight Cell If Contains Text

  • A New Formatting Rule wizard will appear. Pick Use a formula to determine which cells to format from the Select a Rule Type section.
  • Then, insert the following formula in the Format values where this formula is true section.
=ISTEXT(B5)
  • Next, click on Format to define the cell pattern containing text.

Assign a Formula for Conditional Formatting to Highlight Cell If Contains Text

Read More: How to Use IF-THEN Statements with Text in Excel


Step 3: Set Format for Cell to Highlight Cell If Contains Text

  • After clicking on Format…, we will have the Format Cells wizard.
  • Go to Fill.
  • Pick a color according to your choice.

Set Format for Cell If Contains Text

  • Next, go to the Font tab.
  • Make necessary adjustments from here. I have applied white color font with an Italic style.
  • Afterward, click on OK.

Set Format for Cell If Contains Text

  • Click OK again to apply Conditional Formatting.

Apply ISTEXT Function

Read More: How to Use VLOOKUP If Cell Contains a Word within Text in Excel


Step 4: Highlighted Cell If Contains Text

  • After following the above steps, we will have the cells containing text sorted.

Highlighted Cell If Contains Text


How to Highlight Cell If Contains Specific Text in Excel

There is a slight modification needed if we are going to highlight a cell that contains specific text. The details are described in the below section.

Steps:

  • Select the entire dataset.
  • Next, go to the Home tab.
  • Choose New Rule… from the Conditional Formatting feature.

Use Conditional Formatting Command

  • A wizard named New Formatting Rule will appear.
  • Pick Use a formula to determine which cells to format from the Select a Rule Type section.
  • Then, insert the following formula in the Format values where this formula is true section.
=$B5 =$C$12
  • Next, click on Format and make the necessary specifications to define the cell pattern containing specific text.
  • Click on ENTER to finish the process.

Apply Formula

  • Finally, we have highlighted cell that contains specific text.

Highlight Cell If Contains Specific Text in Excel

Read More: How to Use “Not Equal to” Operator for Text in Excel


Download Practice Workbook


Conclusion

At the end of this article, I like to add that I have tried to explain step-by-step procedures to highlight cells if contains text in Excel. It will be a great pleasure for me if this article helps any Excel user even a little. For any further queries, comment below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Hello everyone, I am Naimul Hasan Arif, graduated from Bangladesh University of Engineering and Technology (BUET). I am working as an Excel and VBA Content Developer. I try to remain dedicated to my duties and give my best with my skills & knowledge.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo