Highlight Row If Cell Contains Text with Conditional Formatting in Google Sheets

While working with data in Google Sheets you may sometimes need to highlight the entire row containing data in a particular cell. Doing this manually without any formula may be tiring and time-consuming if you have a large amount of data. In this article, we will try to show you how you can use conditional formatting to highlight row if cell contains text in Google Sheets easily.

A Sample of Practice Spreadsheet

2 Ways to Use Conditional Formatting to Highlight Row If Cell Contains Text in Google Sheets

1. Using Custom Formula in Conditional Formatting

We’ll begin with a simple example that highlights the row if the cell contains text. In our example, we use a data table with employee ID, Names, Months, and monthly Sales amount.

dataset for conditional formatting with custom formula to highlight row if cell contains text in google sheets

We will highlight the row containing the name Robert in the table using Conditional formatting. For this to work, we need to introduce a formula that we discussed later.

Conditional formatting will highlight the entire row if it can find the name Robert in any of the cells.

final outlook of conditional formatting to highlight row if cell contains text

The steps you need to follow:

  • First, open the spreadsheet and select the range of tables where you want the highlight to take place. We select the range B5:E16.
  • Then, go to Menu bar > Format > Conditional formatting.

selecting conditional formatting from format panel

  • You will see a Conditional formatting sidebar appear from the right side. The sidebar specifies where it is applying the Conditional formatting in the Apply to range In our example, it says B5:E16.

conditional format sidebar with range

  • Next, from the Format rules tab from Format cells if box select Custom Formula is option because by default the Conditional formatting is applied to all the cells having any text or values and we do not want that.

select custom formula is option from format rules tab

  • Then, in the Value or formula box type in the following formula:
=$C5="Robert"

insert custom formula to highlight row using conditional formatting

Formula Explanation:

  • The first part of the formula $C5 determines where the name Robert is in the table and tells Sheets to start matching from that cell. Notice how C is locked with a dollar($) sign and 5 is not. This is because we do not want the column value to change but to keep the row value flexible when conditional formatting looks across the entire table.
  • The second part of the formula “Robert” is the condition the Conditional formatting has to meet to highlight. If it is an exact match then the entire row will be highlighted, otherwise it will remain as is.
  • You can format the highlighted table further in the Formatting style You can select a custom font size and even select a preferred color. We select light orange 2 for our example.

select custom format for conditional formatting with custom formula

  • Finally, press Done and you can see the rows highlighted.

press done to complete conditional formatting using custom formula

  • This is how the final dataset looks:

output for conditional formatting using custom formula to highlight row if cell contains text

Read More: Google Sheets: Conditional Formatting Row Based on Cell

2. Applying REGEXMATCH Function for Partial Text Match

REGEXMATCH can be used with Conditional formatting to highlight row if the cell contains text without much hassle. For this, we use a data table with employee ID, Names, and monthly Sales amount. We will apply the REGEXMATCH function to highlight the row for all the first names containing Robert.

dataset for conditional formatting with regexmatch

Follow these steps:

  • First, open the spreadsheet and select the range of tables where you want the highlight to take place. We select the range B5:D14.
  • Then, go to Menu bar > Format > Conditional formatting.

select conditional formatting from format panel

  • You will see a Conditional formatting sidebar appear from the right side.
  • The sidebar specifies where it is applying the Conditional formatting in the Apply to range In our example, it says B5:D14.

conditional format sidebar with range for regexmatch

  • Next, from the Format rules tab from Format cells if box, select Custom Formula is option because by default the Conditional formatting is applied to all the cells having any text or values and we do not want that.

select custom formula is option from format rules tab

  • Then, in the Value or formula box type in the following formula:
=REGEXMATCH($C5, "\ARobert\s")

insert regexmatch formula to highlight rows using conditional formatting

Formula Explanation:

  • The first part of the formula $C5 determines where the name Robert is in the table and tells Sheets to start matching from that cell. You can notice that C is locked with a dollar($) sign and 5 is not. This is because we do not want the column value to change but to keep the row value flexible when conditional formatting looks across the entire table.
  • The second part of the formula “\ARobert\s” is the condition the Conditional formatting has to meet to highlight. \A matches any letters from A-Z. If it matches the words Robert, then the entire row will be highlighted, otherwise, it will remain as is.
  • Notice how Conditional formatting does not look for the full name, but rather the first name Robert. REGEXMATCH made this possible
  • \s matches a whitespace character. In our case, the space between the first and last name.
  • You can format the highlighted table further in the Formatting style You can select custom font size and even select a preferred color. We select light orange 2 for our example.

select custom format for conditional formatting using regexmatch

  • Finally, press Done and you should see the rows highlighted.

press done to complete conditional formatting using regexmatch

  • This is how the final dataset looks:

output for conditional formatting using regexmatch to highlight row

  • You can use the REGEXMATCH function to highlight the row for all the last names containing Smith For this, just type in the following formula:
=REGEXMATCH($C5, "\sSmith")
  • Follow the same steps as you did for the first name, only this time the \s is before the potential last name, in our case which is Smith.

regexmatch for conditional formatting for last name

Read More: Change Row Color Based on Cell Value in Google Sheets (4 Ways)

Potential Problems, Solutions, and Tips

While trying to highlight a row with particular text, if you are not careful then you may not get the desired outcome.

  • Carefully select cells and use cell references properly. Use absolute cell references ($) when possible.
  • Make sure there is no other Conditional formatting overlapping the existing one.
  • Remove any unwanted extra spaces between words. For this, you can use the TRIM function.

Conclusion

In this article, we showed you how to use Conditional formatting to highlight row if a cell contains text in Google Sheets. We hope this article was useful to you. Keep practicing the methods that we have shown here to get a good grip on the concept.

Also, check out other articles on OfficeWheel to keep on improving your Google Sheets work knowledge.


Related Articles

Maruf Niaz

Maruf Niaz

My Name is Niaz. As a regular Google Sheets user, I have a good understanding of the users' needs and bad experiences. In my articles, I will share my knowledge which I hope, will be helpful to you. My latest educational degree was BSc in Textile Engineering from Bangladesh University of Textiles.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo