Highlight a Row Based on a Cell Value in Google Sheets

In this article we will show you how to highlight a row based on another cell value with conditional formatting in Google Sheets. Simply follow the steps below.

Highlight Row Based on a Cell Value

A sample sheet has been created for the purpose of this example, access this and follow along by clicking here.

In this example we will have a list of 9 people and the cities they live in. We want to highlight any rows of the people who live in New York. To do this we can use conditional formatting with a custom formula.

1. Select the entire range of rows

Select the range of rows by clicking and dragging over the rows to select them.

If you need to select rows that are separate from each other hold the Ctrl (Cmd ⌘ on Mac) key whilst clicking the individual rows.

In our example we want to apply the condition to the entire dataset so will select all rows that contain data.

Conditional formatting Google Sheets highlight row: Row selection

2. Click Format followed by Conditional Formatting

From the file menu select click Format, a menu will display. Select Conditional formatting.

Google Sheets highlight whole row conditional formatting: File menu‍

3. Set the format rule to “custom formula is”

The conditional formatting rules menu will now appear on the right hand side of the display.

Conditional formatting highlight row Google Sheets: Conditional format rules menu

In the section labelled Format rules expand the Format cells if.. dropdown menu. At the bottom of this menu select Custom formula is.

Google Sheets conditional formatting highlight row: Set format rule‍

4. Input the custom formula =Cell_Ref=”Value”

An input box will appear, enter the formula:


Formula Breakdown:

$Cell_Ref: This is the column you want the formula to search followed by the first row. It is important the row number matches the first row of your selection. The dollar sign ($) needs to prefix the cell reference to apply the search to the entire column.

=Value: This is the value you want the formula to check against. Any text values must be contained in double quotations (“  ”), numerical values do not need any quotations.

In our example we need to set our custom formula to search for the text value “New York” located in column D. Our selection starts from row number 4 therefore our complete formula will be:

=$D4=”New York”

Highlight row based on cell value Google Sheets: Custom formula

5. Set the format style

In the Formatting style section you can now set various format options including the ability to bold, italic, underline and strikethrough text. You can also change the text and cell background highlight.

In our example we will bold the text and turn the cell highlight purple. This will apply the highlight to the entire row.

Highlight row if cell contains Google Sheets: Formatting style

6. Click “Done” to apply the highlight

Click the Done button which will apply the custom formula and any row that meets the conditions set in the formula will highlight purple.

As can be seen in our example all the rows that contain the text value “New York” in column D are highlighted purple.

Google Sheets highlight row based on cell: Results

Lido is a new spreadsheet built for automation. Trigger emails, slack messages, and more directly from a spreadsheet.