Google Sheets: Highlight Entire Rows With Conditional Formatting

Conditional formatting makes it easy to highlight cells in Google Sheets. It’s a little more difficult, however, to highlight an entire row in a data set that has multiple columns.

In this guide, we’ll show you, with examples, how to highlight an entire row or rows in Google Sheets using conditional formatting formulas.

Take a look at one of the examples in the illustration below:

Without any further ado, let’s get started.

How can you Highlight An entire row with Conditional formatting in Google Sheets (The Concept)

I’d love to theoretically explain things before getting into the meat of the matter. This way, you’ll have a better understanding and be less likely to face difficulties while attempting to highlight rows with conditional formatting in Google Sheets.

If you want to skip ahead to the examples and explanations, use the table of contents above.

To begin, it is critical to understand that the preset conditional formatting rules are extremely limited when it comes to selecting entire rows or columns. It does an excellent job of highlighting cells but not entire rows.

So, before you keep looking for ways to highlight rows with preset rules, stop and instead learn how to do it with the steps and examples in this guide

You can only apply conditional formatting to the entire row using custom formulas.

Not any formula, but one that must return either TRUE or FALSE. This way, they can perfectly serve as a trigger for the conditional formatting you set.

Also, remember to use cell efferences with respect to the upper-left cell in the selection when applying conditional formatting to a range of cells. This allows the conditional formatting formula to be applied to each cell in the selection while cell references are updated.

Don’t be concerned if you get lost. After you’ve gone through the examples, it will all make sense.

How To Apply Conditional Formatting To Entire Row (The Steps)

There’s only one simple way to apply conditional formatting in Google Sheets.

However, depending on your data and the complexity of the rule, there are numerous formulas you can use to accomplish this.

To apply conditional formatting to the entire row in Google Sheets, first, select the cells to be examined and formatted. Next, go to Format > Conditional formatting. Under the Format rules, select “Custom formula is” from the list. Specify your custom formula in the given box and set your formatting. Then click Done.

Let’s now get into some real-world examples.

I hope that some of these examples will help you. If not, I’m sure they will inspire you to make your own formula-based conditional formatting in Google sheets.

Example 1: Change Row Color Based On A Corresponding Cell Value

We’ll begin with a simple example that highlights rows based on cell values in a specific column.

As seen in the screenshot below, the conditional formatting rule is set to highlight any row whose cell value in the Weight Tracking column exceeds or equals 300.

Formula used:

=$D3>=300

Step-By-Step Guide and Explanation:

To dive into the steps, fire up your browser, navigate to Google Sheets, and open or create a spreadsheet with a table of data where you want to highlight rows using conditional formatting.

Step 1: Select the Cells for the conditional formatting

This is a no-brainer.

Click and drag your mouse pointer over all the cells you want the conditional formatting to apply to.

In this case, we’ll select the range A3:D18.

This step is very important as it allows formatting to apply to the entire row whenever a condition is true.

The number of columns your selection includes determines how Google sheets will highlight the row. For example, if we select range A3:C18, it will only have three columns and the conditional formatting will apply to the rows that include only the three columns in the selection leaving out the last column.

Take a look at the screenshot below to see how the fourth column is excluded from the formatting.

As a result, when selecting, make sure to include all columns of your data or table to ensure that the entire row is highlighted.

Step 2: Go to Format > Conditional formatting

Here’s another easy step.

Simply move your cursor to the Google Sheets Menus and select the Format menu.

This action will reveal all of the Format menu items, including Conditional formatting. Simply click on it to open it on the right.

Step 3: Under the Format rules, click on the drop-down and select “Custom formula is” from the list.

You can add formula-based conditional formatting using the Custom formula rule.

To do so, first, under the Format rules, click on the drop-down and select Custom formula is from the list.

Entring Custom formula in Google Sheets conditional formatting

Step 4: Enter the formula: =$D3>=300

You’ll then be given a text box to enter your value, kindly enter =$D3>=300.

Adjust the cell reference and value according to your data.

In case you want to apply this to your own data, simply change the $D3 part of the formula to reference the cell that begins the column you are using for the condition.

Remember to include the dollar signs in your cell reference for the column letter. This will lock the column, preventing it from referencing other columns.

Step 5: Set the Formatting

There are a lot of formatting styles you can apply to the rows when they meet the condition you set.

You can do that by using the font controls under the Formatting style.

There are several preset formatting you can choose from (including green background, yellow background, red background, green text, yellow text, and red text)

Click on the Default button under the Formatting style to reveal all the available formatting presets.

Step 6: Click Done

As soon as you click the done button, Google Sheets will now evaluate the condition and set the formatting as needed.

When you change the values in the Weight Tracking column, Google Sheets evaluates the condition and adjusts formatting as needed.

Example 2: Format Entire Row Based On Another Cell Value

This example applies conditional formatting to rows based on the text another cell contains.

The illustration below has a spreadsheet of a company’s monthly sales for three countries. When a month is entered in cell C2, conditional formatting checks to see if there is a matching text in column A and then formats the entire row or does nothing based on the results.

Formula Used:

=$C$2=$A5

Step-By-Step Guide:

The below steps will show you how to achieve the above functionality in your spreadsheets.

Step 1: Select the range of cells for the Conditional Formatting

Step 2: Go to Format > Conditional formatting

Step 3: Enter the Conditional Formatting Formula: =$C$2=$A5

To enter the formula, first, under the Format rules, click on the drop-down. Then select “Custom formula is” from the list.

You will then be presented with a text box in which to enter your formula, enter =$C$2=$A5.

Step 4: Set the formatting you want to apply to rows that meet this condition.

Step 5: Click Done

When you’ve completed all of the steps correctly, your spreadsheet should be able to highlight rows based on the content in cell C2.

For cell A5, a mix reference is used to make the column part of the reference absolute; thus, the comparison is always performed using the cell values in column A.

Example 3: Use Conditional formatting to Highlight rows based on a checkbox

Using checkboxes, you can present your data in an easy-to-understand format.

In this example, you’ll learn how to do that using conditional formatting to highlight rows.

We’ll create a spreadsheet with the names of some employees, their salaries, and their departments. Then, using checkboxes, we’ll highlight employee names based on departments and salary level.

The animation below depicts this example.

This spreadsheet uses checkbox-based conditional formatting. When a checkbox is selected, it indicates that a condition is true and hence triggering the formatting. If a false condition exists, there will be no formatting.

You’ll learn how to make a spreadsheet like this one in the steps below.

Step 1: Open your spreadsheet in Google sheets

Step 2: Design the layout of your spreadsheet

Simply type the employee names in one column, followed by the departments and salary in the following columns, as shown in this example.

See the screenshot below for some inspiration on how to design your spreadsheet data.

Don’t forget about the checkboxes on the right. We’ll connect these checkboxes to the main data using conditional formatting. This way, when we click one of the checkboxes, our data is highlighted as needed.

Learn how to use checkboxes in Google Sheets.

Step 3: Select all the data.

Step 4: Go to Format > Conditional Formatting

Step 5: Click the drop-down under Format rules in the conditional formatting pane.

Step 6: From the list, scroll to the bottom and select “Custom formula is”.

Step 7: Using the Custom formula field, enter the following formulas

=AND($G$5,$C3=”Marketing Dept”)
=AND($G$6,$C3=”Finance Dept”)
=AND($G$7,$C3=”HR Dept”)
=AND($G$8,$D3>9000)
=AND($G$9,$D3<6000)

Note: After entering the first formula, specify the formatting you want for the respective row when a checkbox is selected. Then click done and add more rules to include all of the above-given formulas.

More light on these formulas

The above-mentioned formulas are simply conditions that are set to trigger the formatting.

All of these formulas contain two conditions, requiring the use of the AND function.

If all of the arguments are logically true, the AND function returns true; otherwise, it returns false. In this case, this is the best function to use.

Let us examine one of the formulas to see how it works. Understanding the anatomy of one formula will lay the groundwork for understanding the others and even creating your own.

Here goes the first formula:

=AND($G$5,$C3=”Marketing Dept”)

The arguments in the AND function are $G$5 and $C3=”Marketing Dept”.

These arguments in the function are stating a rule that says that if cell G5 is true (i.e., checked) and cell C3 contains Marketing Dept, only if these two conditions are met, highlight the entire row.`

This is the most basic way to explain these formulas.