Google Sheets conditional formatting highlight row if cell contains text

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 F

Google Sheets conditional formatting highlight row if cell contains text

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.


Table of Contents hideDownload the Practice Workbook7 Methods to Highlight Row If Cell Contains Any Text1. Use Simple Formula to Highlight Row If Cell Contains Any Text2. Excel MATCH Function to Highlight Row if Cell Remains any Text3. Highlight Row Using SEARCH Function If Cell Contains Any Text4. Apply FIND Function to Highlight Row for Case Sensitive Option6. Highlighting Row on Multiple Conditions for Text Containing Cell7. Highlight Row Based on Drop Down List If Any Text Remains in CellConclusionRelated Articles

Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.Highlighting Cell Contains Text.xlsx


7 Methods to Highlight Row If Cell Contains Any Text

1. Use 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).
Use Simple Formula to Highlight Row If Cell Contains Any Text
  • Secondly, go to Home > Conditional Formatting.
Use Simple Formula to Highlight Row If Cell Contains Any Text
  • Thirdly, choose the New Rule from the Conditional Formatting drop-down.
Use Simple Formula to Highlight Row If Cell Contains Any Text
  • Now, the New Formatting Rule window will show up. Choose the Use a formula to determine which cells to format option.
Use Simple Formula to Highlight Row If Cell Contains Any Text
  • Type the below formula in the Rule Description box.=$C5="Orange"
Use Simple Formula to Highlight Row If Cell Contains Any Text
  • Click Format.
Use Simple Formula to Highlight Row If Cell Contains Any Text
  • Then click the Fill tab and choose the fill color. Next, select OK.
  • After formatting is done, again click OK.
  • Finally, all the rows that contain Orange will be highlighted.

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


2. Excel 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).
Excel MATCH Function to Highlight Row if Cell Remains any Text
  • 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.=MATCH($F$5,$B5:$D5,0)
Excel MATCH Function to Highlight Row if Cell Remains any Text
  • After that, click Format and choose Fill color, select OK.
  • Again click OK.
  • Finally, all the rows that contain Orange in any cell will be highlighted.

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)


3. Highlight Row Using SEARCH Function 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).
Highlight Row Using SEARCH Function If Cell Contains Any Text
  • Next, go to Home > Conditional Formatting > New Rule.
  • Now the New Formatting Rule window will show up. Choose Use a formula to determine which cells to format.
  • Then type the below formula in the Rule Description box.=SEARCH("Orange",$B5&$C5&$D5)
Highlight Row Using SEARCH Function If Cell Contains Any Text
  • After that, click Format and choose Fill color, click OK.
  • Again click OK.
  • Finally, all the rows that contain Orange in any cell will be highlighted.

How Does the Formula Work?

The SEARCH function returns the number of the character 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:

  • How to Unhide Top Rows in Excel (7 Methods)
  • Hide Rows and Columns in Excel: Shortcut & Other Techniques
  • Hidden Rows in Excel: How to Unhide or Delete Them?
  • VBA to Hide Rows in Excel (14 Methods)
  • How to Resize All Rows in Excel (6 Different Approaches)

4. Apply 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 case sensitive and it will work accordingly.

Steps:

  • First, select the entire dataset (B5:D13).
Apply FIND Function to Highlight Row for Case Sensitive Option
  • Go to Home > Conditional Formatting > New Rule.
  • Next, 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.=FIND("Orange",$B5&$C5&$D5)
Apply FIND Function to Highlight Row for Case Sensitive Option
  • Then, click Format and choose Fill color, click OK.
  • Again click OK.
  • 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.

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


5. Insert 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 functionoffers great help. For instance, in the following example, we want to highlight the sales of Orange when the sold Quantity is greater than 70. So, we will follow the steps below.

Steps:

  • First, select the entire dataset (B5:E13).
Insert AND Function to Highlight Row If Cell Contains Any Text
  • 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)
Insert AND Function to Highlight Row If Cell Contains Any Text
  • Click Format and choose the Fill color, click OK.
  • Again click OK.
  • Finally, all the rows that contain Fruit Name: Orange and Quantity: >70 will be highlighted.

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)


6. Highlighting Row on Multiple Conditions for Text Containing Cell

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

Steps:

  • First, Select the entire dataset (B5:E13).
Highlighting Row on Multiple Conditions for Text Containing Cell
  • Now go to Home > Conditional Formatting > New Rule.
  • The New Formatting Rule window will show up. Choose Use a formula to determine which cells to format
  • Then, type the below formula in the Rule Description box.=$C5="Orange"
Highlighting Row on Multiple Conditions for Text Containing Cell
  • Select Format and choose Fill color, click OK.
  • Again click OK.
  • 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"
Highlighting Row on Multiple Conditions for Text Containing Cell
  • Finally, both conditions will be shown in the dataset in different colors.

Related Content: How to Highlight Every Other Row in Excel


7. Highlight Row Based on Drop Down List 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 of the drop-down.

Steps:

  • First, create the drop-down list from Fruit Name.
Highlight Row Based on Drop Down List If Any Text Remains in Cell
  • Now, select the entire dataset (B5:D13).
Highlight Row Based on Drop Down List If Any Text Remains in Cell
  • 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
Highlight Row Based on Drop Down List If Any Text Remains in Cell

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

  • Click Format and choose Fill color, click OK.
  • Again click OK.
  • 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.

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.


  • [Fix]: Unable to Unhide Rows in Excel (4 Solutions)
  • Data Clean-up Techniques in Excel: Randomizing the Rows
  • How to Delete Blank Rows in Excel (6 Ways)
  • Hide Rows in Excel (6 Effective Methods)
  • How to Group Rows in Excel (5 Easy Ways)

Video liên quan