Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Highlight Row If Cell Contains Any Text in Excel

In this article, we will discuss how to highlight a row if any cell contains any text. Sometimes highlighting only the cells that contain text is not enough. Luckily, Conditional Formatting in Excel offers easy ways to highlight these rows. Here, in this article, we will use functions, the combination of functions along with Conditional Formatting to highlight rows. Here, we will demonstrate a gif for a better understanding of highlighting rows with text.


Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.


7 Smart Methods to Highlight Row If Cell Contains Any Text in Excel

This article will demonstrate how to highlight rows if a cell contains any text in Excel by using the AND function, the MATCH function, the SEARCH function, the FIND function, and also with the help of multiple conditions, as well as making a drop-down list. So, you will go through all these ways for a better understanding.


Method 1: Using Simple Formula to Highlight Row If Cell Contains Any Text

We can use a simple arithmetic formula to highlight rows if a cell contains any text. For example, we have a fruit sales dataset and we want to highlight rows that only contain ‘Orange’ in any cell.

Steps:

  • Firstly, select the entire dataset (B5:E13).

Sample Data Set

  • Secondly, go to Home > Conditional Formatting.
  • Thirdly, choose the New Rule from the Conditional Formatting drop-down.

Using Conditional Formatting

  • Then, the New Formatting Rule window will show up. Choose the ‘Use a formula to determine which cells to format’ option.
  • After that, type the below formula in the ‘Rule Description’ box.

=$C5="Orange"

  • Besides, click Format.

Opening New Formatting Rule by Using Simple Formula to Highlight Row If Cell Contains Any Text

  • Then, click the Fill tab and choose the fill color. Next, select OK.

Selecting Color

  • After formatting is done, again click OK.

  • Finally, all the rows that contain ‘Orange’ will be highlighted.

Showing Result by Using Simple Formula to Highlight Row If Cell Contains Any Text

Read More: How to Highlight Active Row in Excel (3 Methods)


Method 2: Inserting MATCH Function to Highlight Row if Cell Remains any Text

You can highlight rows using the MATCH function along with Conditional Formatting.

Steps:

  • First, select the entire dataset (B5:D13).

Sample Data Set

  • Next, go to Home > Conditional Formatting > New Rule.
  • Then, the New Formatting Rule window will show up. Choose ‘Use a formula to determine which cells to format’.
  • Afterward, type the below formula in the ‘Rule Description’ box.

=MATCH($F$5,$B5:$D5,0)

  • After that, click Format and choose the Fill color, and select OK.
  • Again click OK.

Opening New Formatting Rule

  • Finally, all the rows that contain ‘Orange’ in any cell will be highlighted.

Showing Result by Inserting MATCH Function to Highlight Row if Cell Remains any Text

🔎 How Does the Formula Work?

The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order. The function matches the value of Cell E5 in the range (B5:D5) and thus highlights the corresponding rows containing the text ‘Orange’.

Read More: How to Select Row in Excel If Cell Contains Specific Data (4 Ways)


Method 3:  Using SEARCH Function to Highlight Row If Cell Contains Any Text

In this method, we will use the SEARCH function to highlight rows that contain any text. For example, we have a fruit name dataset and will search for a particular fruit and highlight the rows accordingly.

Steps:

  • First, Select the entire dataset (B5:D13).

Sample Data Set

  • Next, go to Home > Conditional Formatting > New Rule.
  • After that, the New Formatting Rule window will show up. Besides, select ‘Use a formula to determine which cells to format’.
  • Then, type the below formula in the ‘Rule Description’ box.

=SEARCH("Orange",$B5&$C5&$D5)

  • After that, click Format and choose the Fill color, and click OK.
  • Again click OK.

Opening New Formatting Rule

  • Finally, all the rows that contain ‘Orange’ in any cell will be highlighted.

Showing Result by Using SEARCH Function to Highlight Row If Cell Contains Any Text

🔎 How Does the Formula Work?

The SEARCH function returns the number of characters at which a specific character or text is first found, reading left to right. The SEARCH function looks for the text ‘Orange’ in Cell B5, C5, and D5. As relative reference is used, the function looks for the values in the entire range (B5:D13). Finally, rows containing the text ‘Orange’ are highlighted.

Related Content: How to Highlight Row If Cell Is Not Blank (4 Methods)


Similar Readings:


Method 4: Applying FIND Function to Highlight Row for Case Sensitive Option

Likewise, in the SEARCH function, we can use the FIND function to highlight rows if a cell contains any text. However, the FIND function is the case- and it will work accordingly.

Steps:

  • To start with, select the entire dataset (B5:D13).

Sample Data Set

  • Secondly, Go to Home > Conditional Formatting > New Rule.
  • Next, the New Formatting Rule window will show up. Next, choose ‘Use a formula to determine which cells to format’.
  • After that, type the below formula in the Rule Description box.

=FIND("Orange",$B5&$C5&$D5)

  • Then, click Format and choose the Fill color, and click OK.
  • Again click OK.

Opening New Formatting Rule

  • In the end, all the rows that contain ‘Orange’ in any cell will be highlighted. But, rows containing the text ‘orange’ or ‘ORANGE’ are not highlighted as the FIND function is case-sensitive.

Showing Result by Applying FIND Function to Highlight Row for Case Sensitive Option

Related Content: Excel Alternating Row Color with Conditional Formatting [Video]


Method 5: Inserting AND Function to Highlight Row If Cell Contains Any Text

Sometimes you need to highlight rows based on two criteria. In such cases, the AND function offers great help. For instance, in the following example, we want to highlight the sales of ‘Orange’ when the old Quantity is greater than 70. So, we will follow the steps below.

Steps:

  • First, select the entire dataset (B5:E13).

Sample Data Set

  • Next, go to Home > Conditional Formatting > New Rule.
  • Then, the New Formatting Rule window will show up. Choose ‘Use a formula to determine which cells to format’.
  • Now, type the below formula in the ‘Rule Description’ box.

=AND($C5="Orange",$D5>70)

  • Next, click Format and choose the Fill color, and click OK.
  • Again click OK.

Opening New Formatting Rule

  • Finally, all the rows that contain Fruit Name:Orange’ and Quantity: >70 will be highlighted.

Showing Result by Inserting AND Function to Highlight Row If Cell Contains Any Text

🔎 How Does the Formula Work?

The AND function checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE. Here this function checks both the Fruit Name and Quantity arguments and highlights rows accordingly.

Note

You can use the OR function instead of AND function.

Related Content: How to Color Alternate Rows in Excel (8 Ways)


Method 6: Employing Multiple Conditions for Text Containing Cell to Highlight Row

You can highlight rows based on multiple conditions too. For example, in the following example, we will highlight rows that have the text ‘Orange’ and text ‘Apple’.

Steps:

  • To begin with, select the entire dataset (B5:E13).

Sample Data Set

  • Then, go to Home > Conditional Formatting > New Rule.
  • After that, the New Formatting Rule window will show up. Besides, choose the Use a formula to determine which cells to format.
  • Then, type the below formula in the ‘Rule Description’ box.

=$C5="Orange"

  • After that, choose Format and choose the Fill color, and click OK.
  • Again click OK.

Opening New Formating for Employing Multiple Conditions for Text Containing Cell to Highlight Row

  • All the rows that contain ‘Orange’ in any cell will be highlighted.
  • After that, repeat Steps 1 to 7 to set the new conditions to the existing dataset and write the below formula:

=$C5="Apple"

  • Finally, both conditions will be shown in the dataset in different colors.

Showing Result by Employing Multiple Conditions for Text Containing Cell to Highlight Row

Related Content: How to Highlight Every Other Row in Excel


Method 7: Making Drop-Down List to Highlight Row If Any Text Remains in Cell

In this method, we will use a drop-down list to highlight rows if any cell contains any text from the drop-down.

Steps:

  • Firstly, create the drop-down list from ‘Fruit Name’.

Sample Data Set for making Drop down list

  • Secondly, select the entire dataset (B5:D13).

Sample Data Set for Making Drop-Down List to Highlight Row If Any Text Remains in Cell

  • After that, go to Home > Conditional Formatting > New Rule.
  • Then, the ‘New Formatting Rule’ window will show up. Choose ‘Use a formula to determine which cells to format’.
  • Type the below formula in the ‘Rule Description’ box.

=$C5=$F$5

Here, the drop-down value is used as a reference value in the formula.

  • Click Format and choose ‘Fill’ color, and click OK.
  • Again click OK.

Opening New Formatting Rule

  • Finally, when we change the fruit name from the drop-down list highlighting will also change. Such as I have selected ‘Orange’ from the drop-down and all the rows containing ‘Orange’ are highlighted.

Showing Result by Making Drop-Down List to Highlight Row If Any Text Remains in Cell

Related Content: How to Hide Rows Based on Cell Value in Excel (5 Methods)


Conclusion

In the above article, I have tried to discuss all the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

4 Comments
  1. Great Job. This article was well written clear, concise, and thorough with different functions and conditions. I was able to find what I needed to do with the explanations in no time.

  2. How to highlight an entire row if a a cell contains any random entry (number/text). Thank you

    • Reply
      Fahim Shahriyar Dipto Nov 30, 2022 at 4:29 PM

      Hello Euliin,
      Thanks for commenting on our website. You can highlight a cell containing any random value with Conditional Formatting. Follow the steps.
      Firstly, select the entire row that you want to be highlighted. Then go to the Conditional Formatting and choose New Rule.

      Now, New Formatting Rule dialog box appears. Choose Use a formula which cells to format. Write the formula in the box in the Format values where this formula is true.
      =OR(ISTEXT(B5),ISNUMBER(B5))
      And click on Format.

      Now, Format Cells appears. Pick a color and hit OK.

      Now, again hit OK in the New Formatting Rule box.

      Finally, as you see you can highlight the row.

Leave a reply

ExcelDemy
Logo