Can Google Sheets Highlight Rows Based on Cell Data in 2024?

Conditional formatting in Google Sheets can be a life saver. But often it will just highlight a single cell instead of the entire row. If you’ve been asking yourself “Can Google Sheets highlight rows based on cell values?” The short answer is yes. You simply have to use custom formulas and an absolute reference for the column.

For example, if you wanted to highlight an entire row if column C has a blank cell in it, you would just have to:

1. Highlight the data set
2. Navigate to Format > Conditional formatting
3. Select Custom formula is from the drop down menu
4. Enter =ISBLANK($C2) into the text box and click done

If that’s a little confusing, don’t worry. We’re going to break down conditional formatting rows based on single cell info into digestible chunks and provide several step-by-step examples. Follow along to learn more.

What Is Conditional Formatting?

Conditional formatting in Google Sheets is a feature that allows the user to add custom formatting to the cell or cell range that fulfill criteria specified by the user. The text in the cell can be emphasized or highlighted, or the cell’s color can be changed to highlight the data.

Conditional formatting can help bring the user’s attention to the important data in the spreadsheet, such as upcoming deadlines or tasks that need to be prioritized. It can also be used to notify the user of things that may be going over budget. It can even highlight the highest and lowest values. Using conditional formatting can save the user from the monotony of having to focus on every single cell one by one. Instead, they can take a quick glance and know which parts of the spreadsheet they should focus on.

Basics of Conditional Formatting

There are different ways you can use conditional formatting to highlight the data. This ranges from prebuilt methods in Google Sheets, or you can use custom formulas to create a formatting system of your own. You can even format cells based on information in other cells. Here are some of them:

• Presets: These rules are prebuilt into Google Sheets, and the user can select them from a dropdown menu. These rules are usually very simple, and include parameters such as Less than, Greater than, and Date is.
• Custom Formulas: The presets can be a good starting point, but these often aren’t the best solution which is why you can use custom formulas to get to a specific condition.
• Using Multiple Conditions: Google Sheets allows its users to apply multiple conditions to a cell range meaning that Google will check all the conditions to see if it can apply any or all of them of them to the cell. If a condition is true, it is applied to that cell.

Basics of How to Use Conditional Formatting in Google Sheets

Now that we have a basic idea of how Google Sheets conditional formatting works, let’s look at a few examples.

Conditional Formatting on a Dataset (Not Entire Rows)

Before we get into highlighting entire rows, let’s take a look at simple conditional formatting. If you’re familiar with it already, feel free to skip this section.

To demonstrate basic conditional formatting, we will apply it to a sample dataset that is missing data in a few cells. We will use conditional formatting to highlight the empty cells using a red tint. Here is how to use conditional formatting to highlight a row if a cell contains any data:

1. Click and drag your cursor to select the cells containing the data. This will be highlighted with a light blue tint across the cell range.

2. With the cells highlighted, click on The Format button in the main top bar.
3. Now, click on Conditional formatting in the dropdown menu. This will open the conditional formatting rules sidebar towards the right side of the screen.

4. Here, you can set the various rules to apply to the cell range. You can add the cell range on which you want to apply the conditional formatting in the Apply to range textbox. The cell range should be there already, as we selected the cells in the first step.
5. Now, in the Format rules section, click on the Format cells if option, which will open a menu allowing you to select the condition which needs to be fulfilled to change the formatting.
6. For this example, we will choose Is empty as we want the formatting to be applied if the cells are empty.
7. In the Formatting style settings, you can choose how to change the cell if the condition is met. You can select Bold, Italics, Underline, or Strikethrough the text or change the Text color or Fill color.
8. With the formatting options selected, you can click on the green Done button to save the changes.

By deleting some of the data in the cells, we can see that the empty cells instantly change to a red color. You can also have multiple rules that specify different conditions.

For example, let’s say you wish to indicate cells with some sort of data with a light green color while the cells that don’t have any data are highlighted in red. To do this:

1. Select the cell range again and go to the conditional formatting rules sidebar.
2. Here, you will see the formula you previously applied to that spreadsheet.

3. Click on the Add another rule option here.
4. This will open the conditional formatting menu. Make sure the cell range is added to the Apply to range cell.
5. Repeat the previous steps, but use Is not empty as the condition.

As you can see, this highlighted the entire row for non-blank cells, but the rows with a blank value only highlight the single cells. To format entire rows, we have to use custom formulas instead.

Can Google Sheets Highlight Rows Based on Cell Values?

To change the example above to highlight entire rows when one cell in the row is blank, you’d have to follow these steps:

1. Select the cells you wish to apply the conditional formatting to.
2. Navigate to Format > Conditional formatting
3. In the conditional formatting menu, change the Format cells if… drop down menu to Custom formula is.
4. Type =ISBLANK( into the box and then put the absolute column reference and the first row reference. In our example it would be:

=ISBLANK($C2)

To highlight the rows with an empty cell in the “Marks” column.

5. Click Done

Highlight Entire Rows with Multiple Conditions Using the OR Function

The above example highlighted the rows with blank cells in column C, but what if we wanted to highlight the rows that had a blank cell in any column? We could nest the ISBLANK function with the OR function in the custom formula. Like so:

=OR(ISBLANK($C2),ISBLANK($B2),ISBLANK($A2))

Related: 5 Simple Ways to Highlight Duplicates in Google Sheets

If you understood everything above, you can probably stop reading and get cracking in your own spreadsheet. If not, let’s take a look at a few more examples in greater detail.

Google Sheets Highlight Row Based on Cell Values

In this example, we wish to highlight the entire row for a student who scored less than 40 marks. Again, to apply conditional formatting to the entire row in Google Sheets, you have to use a custom formula. Here is how to apply conditional formatting to entire row using a custom formula:

1. Click and drag your cursor to select the cells containing the data. Make sure to select the entire cell range.
2. With the cells highlighted, click on The Format button in the main top bar.
3. Now, click on Conditional formatting in the dropdown menu. This will open the conditional formatting rules sidebar towards the right side of the screen.
4. The selected cell range will automatically be added to the Apply to range textbox. Here, we have to enter the entire cell range even though we only use the custom formula in the marks column to conditional format a row based on cell information.

5. In the Format cells if option, select Custom formula is.
6. Add an equals sign to initialize the formula and add the cell range you wish to check for the condition. Add a dollar sign before the column address to lock that column when basing conditional formatting. We add a less-than symbol and the marks you want the condition to check for. The finished formula in this example is =$C2<40.
7. Select the color in the Formatting style option and click the green Done button to apply the changes.

With the conditional formatting applied, if the marks of a student are less than 40, then the Google Sheets conditional formatting row based on cell value will be applied.

Highlight Cell Based on a Checkbox

Another way you can use Google Sheets is to represent the status of a checkbox using different colors. In this example, we want to mark the cells whose checkboxes are marked as green. One thing you should know about using checkboxes in Google Sheets is that the cells containing an empty checkbox are marked as FALSE, while the ones marked are TRUE.

Here is how to highlight cells based on a checkbox in Google Sheets:

1. Select the cell range on which you wish to apply conditional formatting and click on the Format button in the main bar (make sure you don’t select the header row).
2. Select Conditional formatting in the dropdown menu.
3. The cell range should be added automatically in the Apply to range box.
4. Click on the Format cells if option and select Custom formula is.
   

   

5. For our example, we typed =$D2, but you just need to change the column reference to whichever column contains your checkboxes.
6. Select the color in the Formatting style option and click Done.

Multiple Conditions With AND

A lot of times, we only wish to highlight a row that fulfills two or more conditions. In this example, we only want to highlight the row if the student’s last name is Elliot and they scored less than 40 marks. We will use the AND function as a custom formula to do this. Here is how to do this:

1. Select the cell range you wish to apply conditional formatting on.
2. Open the conditional formatting menu by clicking Format > Conditional formatting.
3. In the Format cells if option, select Custom formula is.
4. Type the equals signs and AND( then the conditions you wish to check for with a comma in between, in our example it would be:

=AND($C2<40, $B2="Elliot")

5. Select the color in the Formatting style option and click Done.

With the AND condition applied, the cell will be highlighted only if both conditions are met. Alternatively, like in our initial example, you can use the OR function if you want the formula to check for only one condition to be true. In the example above, if you were to use OR instead, either the last name must be Elliot, or the student should have less than 40 marks. The cell would be highlighted if either one of these conditions are met instead of both.

Three Conditions Using OR and AND Functions

Using the same dataset as before, we want to check for three conditions to be fulfilled. The first condition is that the student should have the first name “Cody” or the last name “Elliot”. This can be done using the OR function, which will return as true if either of the conditions is true. We are also checking that the student is studying “English”

This means that the student has to be studying English and have the first name “Patrick” or the last name “Elliot” for the condition to be true. Here is how to do this:

1. Select the cell range on which you wish to apply conditional formatting and click Format, then Conditional formatting.
2. The selected cell range will automatically be added to the Apply to range textbox.
3. Select Custom formula is in the Format cells if option.
   

   

4. Enter the nested AND and OR formula which is:

   =AND($D2="English",  OR($A2="Cody", $B2="Elliot"))

5. Select the color in the Formatting style option and click Done.

We added an extra row to the data above to show something important. Note that in the screenshot, the Math results for Cody are not highlighted because the AND condition of “English” has not been met.

Wrapping Up

We hope this answered your question “Can Google Sheets highlight rows based on cell values?” You just need to figure out the right custom formula to be able to do it. We’ve provided the building blocks above, but please let us know in the comments if you still have questions.

Related:

• Alternate Row Color in Google Sheets: Simple Guide
• How to Copy Conditional Formatting in Google Sheets
• How to Use an IF THEN Formula in Google Sheets
• Easily Apply Color Scale in Google Sheets
• How to Format Cells in Google Sheets