.png)
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.
2. Click Format followed by Conditional Formatting
From the file menu select click Format, a menu will display. Select Conditional formatting.
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.
In the section labelled Format rules expand the Format cells if.. dropdown menu. At the bottom of this menu select Custom formula is.
4. Input the custom formula =Cell_Ref=”Value”
An input box will appear, enter the formula:
=Cell_Ref=”Value”
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”
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.
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.
