Highlight row based on cell values in Google Sheets

Top image for the lesson on how to highlight row based on cell value in Google Sheets by SpreadsheetClass.com

In Google Sheets, many know how to highlight a cell based on its value, by using conditional formatting. But did you know that you can use conditional formatting to highlight an entire row based on the value of a single cell? That’s what I’m going to show you how to do in this lesson.

To highlight a row based on a cell value, follow these steps:

  1. On the top toolbar, click “Format”, then click “Conditional formatting”
  2. Click “Add another rule”
  3. Under the “Format rules” section, under the drop-down that says “Format cells if”, select “Custom formula is”
  4. In the blank field below, enter the following formula =$A1=”Yes” (This will highlight the entire row if the value in cell A1 is “Yes”)
  5. Press “Enter” on the keyboard
  6. (Optional) Choose the color / format that you want the highlighted rows to be (Fill color)

Click here to get your free Google Sheets cheat sheet

Highlight row based on cell value

To highlight a row based on a cell value, we need to use the “Custom formula is” option in the conditional formatting menu. “Custom formula is” allows you to specify which cells to format based on a specified criteria / formula.

On the top toolbar, click “Format”

Then click “Conditional formatting” and a menu will pop up on the right

Click “Add another rule”.

In the “Apply to range” field, type A1:Z1000 and press “Enter” on the keyboard. If we had selected this range of cells before opening the conditional formatting menu, Google Sheets would have automatically filled in the cell range for us.

Click the “Format cells if” dropdown, and choose “Custom formula is”

Then type the formula =$D1=”A” and press “Enter” on the keyboard. This formula tells Google Sheets to format / highlight the rows where column D is equal to “A”. In other words, it highlights the rows where the letter “A” is found in column D.

As you can see in the example image further below, this highlights the rows for the students who received a letter grade of “A” on their assignment.

Apply to range: A1:Z1000

Custom formula is: =$D1=”A”

Example of how to highlight row based on cell value in Google Sheets by using conditional formatting menu with custom formula is option

The image above shows what the conditional formatting rule will look like, when the rule is open / being edited. The image below shows that the conditional formatting rule looks like in the main conditional formatting menu where all of the rules are displayed. Note that the bottom part of the rule displays the “Apply to range” field, and the top part shows the custom formula.

In this example I have chosen an orange “Fill color” / background color for this conditional formatting rule.

Example of how to highlight row based on cell value in Google Sheets by using custom formula is conditional formatting rule

After following the steps above, when the letter “A” is found within column D, that row will be highlighted, as shown in the image below.

Example of how to highlight row based on cell value in Google Sheets

Highlight row based on date

In this example we will highlight an entire row based on a date, where the rows will be highlighted if the date is greater than (later than) a specified date. We will use the same method from the previous example, but we will simply make a small change to our “Custom formula is” formula.

In this case, we want to highlight the row(s), if the dates in column A are greater than the date entered in cell E1.

Apply to range: A2:Z1000

Custom formula is: =$A2>$e$1

Follow the same steps from the previous example:

Open the data validation menu, add a new rule, select “Custom formula is”, then enter the following formula: =$A2>$e$1

In the “Apply to range” field, if the range A2:Z1000 is not already entered, type the range and then press “Enter”

Example of how to highlight row based on date in Google Sheets by using custom formula is conditional formatting rule

After following the steps above, in column A if the date in the cell is greater than / later than the date entered in cell E1, that row will be highlighted, as shown in the image below. For example, in row 4, the date in cell A4 is later than the date entered in cell E1, and so row 4 is highlighted.

Example of how to highlight row based on date in Google Sheets

Highlight row if cell is empty

Sometimes you might want to highlight an entire row if a cell in the row is empty. Again we will make a small change to the “Custom formula is” formula, to do this.

In this case we want to highlight the entire row wherever there is a blank cell in column C.

Apply to range: A1:Z1000

Custom formula is: =$C1=””

Follow the same steps from the previous example:

Open the data validation menu, add a new rule, select “Custom formula is”, then enter the following formula: =$C1=””

In the “Apply to range” field, if the range A1:Z1000 is not already entered, type the range and then press “Enter”

Example of how to highlight row if cell is empty in Google Sheets by using custom formula is conditional formatting rule

After following the steps above, wherever there is a blank cell in column C, that row will be highlighted, as shown in the image below.

Example of how to highlight row if cell is empty in Google Sheets

Highlight row if cell is NOT empty

Now let’s do the opposite of the last example. Sometimes you might want to highlight an entire row if a cell in the row is NOT empty. We will make a slight change to the “Custom formula is” formula, to do this.

In this case we want to highlight the entire row wherever there is NOT a blank cell in column C, i.e. if the cell has anything inside it, it will be highlighted.

Apply to range: A2:Z1000

Custom formula is: =$C2<>””

Follow the same steps from the previous example:

Open the data validation menu, add a new rule, select “Custom formula is”, then enter the following formula: =$C2<>””

In the “Apply to range” field, if the range A2:Z1000 is not already entered, type the range and then press “Enter”

Example of how to highlight row if cell is not empty in Google Sheets by using custom formula is conditional formatting rule

After following the steps above, in any row that has a non-blank cell in columnC, that row will be highlighted, as shown in the image below.

Example of how to highlight row if cell is not empty in Google Sheets by SpreadsheetClass.com

Highlight row if cell is empty (Ignore blank rows)

In this last example, we will go over how to highlight a row if a cell is empty, but we will ignore rows that are completely blank. In this example we will assume that if a cell in column A is blank, the entire row is blank.

We are going to tell Google Sheets to highlight rows where column A is not blank, and where column C is blank.

Apply to range: A1:Z1000

Custom formula is: =and($C1=””,$A1<>””)

Follow the same steps from the previous example:

Open the data validation menu, add a new rule, select “Custom formula is”, then enter the following formula: =and($C1=””,$A1<>””)

In the “Apply to range” field, if the range A1:Z1000 is not already entered, type the range and then press “Enter”

Example of how to highlight row if cell is empty in Google Sheets and ignore blank rows by using custom formula is conditional formatting rule

After following the steps above, in the rows where column C is blank and column A is not blank, those rows are highlighted, as shown in the image below. This will prevent completely blank rows from being highlighted when you are only interested in rows that actually have data in them.

Example of how to highlight row if cell is empty in Google Sheets and ignore blank rows

Now you know how to highlight entire rows based on cell values in Google Sheets!

Click here to get your free Google Sheets cheat sheet

This content was originally created by Corey Bustos / SpreadsheetClass.com