How To Apply Conditional Formatting Across An Entire Row In Google Sheets

Conditional formatting in Google Sheets can be a great time saver to highlight cells or text based on conditional logic automatically.

Conditional logic was covered in the IF Function tutorial, so check out this post for full details. Conditional formatting works in the same manner where a value is evaluated as either true or false; you decide what happens to your cell or text based on the conditional logic applied.

Highlighting a single cell is relatively straightforward, but highlighting an entire row only takes a slight adjustment. The benefit is that you can draw attention to all the data in that row rather than just a part.

We'll use a table of the best-selling books from January to June 2022 to highlight only the rows that we want to draw attention to, but you can use this logic in various settings to achieve the same thing.

For example, when the Author in column C is 'Colleen Hoover,' we can change the background formatting for the entire row.

Choosing the range to format

First, we need to define the range to which we wish to apply conditional formatting. Then, if you click anywhere in the dataset, you can use the shortcut Cmd+A on a Mac or Ctrl+A on a PC to select all the data. Alternatively, you can simply click on the first cell (A1) and drag your cursor to the last cell (F21) to highlight it manually.

So we have this range selected and ready to apply conditional formatting to

A1:F21

Applying Conditional Formatting

As shown below, you can bring up the conditional formatting editing side-pane using the main menu Format > Conditional formatting.

Another way to bring up the conditional formatting editing side-pane is to right-click anywhere in the highlighted data to bring up the contextual menu where you can choose from the menu View more cell actions > Conditional formatting. Either way works just fine, so whatever works best for you.

The conditional formatting editing side-pane will open up on the far right-hand side of the screen with a default setup to highlight any cell that is not empty. Don't worry; we're going to change this in a moment.

This conditional formatting editing side pane has three main settings to consider:

1. Where to apply the formatting

2. The formatting rules

3. The formatting style

Where to apply the formatting

We've already defined the range since we selected all of the data in the table, so this part can be left alone, but you can adjust it if you need to, and we'll touch on this later.

The formatting rules

You can apply formatting rules to look for specific text and the typical comparison operators, as shown below. We can compare numeric values as well as date and time values.

• = Equal to

• <> Not Equal to

• < Less than

• > Greater than

• <= Less than or Equal to

• >= Greater than or Equal to

Text Equals to

We'll apply a custom formula in the Format rules, so click on the drop-down menu and scroll to the bottom to find the option 'Custom formula is.'

When you choose this, a little text input field will appear underneath; this is where we can apply our conditional logic.

To begin, we'll add the condition to look for "Colleen Hoover" in column C; it looks like this:

=C1="Colleen Hoover"

You must begin the custom formula with an equals sign, then specify where to start looking for the condition; in our case, we will begin in column C, row 1 (C1).

=C1

It's pretty apparent we won't find 'Colleen Hoover' in row 1, but since we stated that the range begins from A1, this lookup must match the starting row. I'll demonstrate later what happens when the starting range and lookup do not align, so for now, don't worry about this part.

Next, we need to state what to look for. Finally, we need to add another equals sign followed by the text string wrapped in double quotes.

="Colleen Hoover"

Essentially, we're saying IF C1 equals "Colleen Hoover".

I always think of the first equals sign as an IF statement because that's what's actually happening; this is conditional logic, after all. If this evaluates to true, our formatting style will be applied, but if it's false, it won't.

You'll notice at this point that only one cell on each row where this is true has been formatted. So, for example, rows 2, 4, 7, 8 and 11 all contain 'Colleen Hoover' in column C, but we're highlighting only column A; why?

This is because the range we specified at the beginning starts in column A. If we change the range to C1:F21 in step 1 for 'Where to apply the formatting', column C will be highlighted; see the second image below.

It can be confusing when you see this behaviour, which is why many people shy away from conditional formatting. However, you must understand that the range is essential as it determines where to begin applying the formatting.

We want to highlight the entire row, so starting from column C will not achieve this. Instead, we must begin from column A in our case.

There is just one small change we need to make to the formula to get it to apply across the whole range for each row respectively.

As you can see below, I've changed the range to A1:F21, and I've added a dollar sign ($) before the column reference in the formula.

This dollar sign ($) will lock the column on which we want to base our conditional formatting. This is the key to highlighting the entire row.

A1:F21

=$C1="Colleen Hoover"

Now, the entire row is highlighted from column A to column F, where column C contains the text 'Colleen Hoover'.

Value Comparisons

Taking what we've just learnt, let's try applying conditional formatting with a comparison operator based on a value in column E for the number of pages in these top 20 books.

Let's assume we're only interested in highlighting rows where the number of pages is more than 400; in this case, we can use the expression below.

We need to adjust the range starting point and lookup starting cell to row two for this to work; see below.

A2:F21

=$E2>400

Now you can see we're only highlighting four rows where the number of pages is greater than 400.

If you need also to include 400 in this condition, you would need to modify this to look for greater than and equal to, like this:

=$E2>=400

Now you can see we have six rows highlighted.

You can play around trying different comparison operators to get the desired results, such as this:

=$D2<>"Berkley" 

This example will highlight everything except this text string, for example. If you recall, the comparison operator <> means not equals to.

Your imagination only limits you. Of course, it's possible even to include regular expression within this custom formula to isolate specific items, but that's beyond the scope of this article; we'll save that for another day.

The formatting style

The final step all comes down to personal preference and, ultimately, what it is you intend to achieve. For example, you may want to draw attention to or divert attention away from a particular thing.

You have various options to choose from to alter the text or the whole cell from this formatting style panel.

These are your options, and it should be noted that you can use a combination of them if you wish.

• Bold

• Italic

• Underline

• Strike-through

• Text colour

• Background colour

If you click on the main Default bar with the green background, this will bring up six generic options for a quick selection, but if these don't take your fancy, click on the 'Custom format' button to take you back to all options.

Clicking on the text or background colour options will bring up this colour selection panel. You can click on the 'None' option at the top to clear this format, reseting it to nothing, or choose the colour you want to set the style you're looking for.

I find it particularly useful to set rows with a light grey text colour if I want to divert attention away from them to make everything else stand out.

Doing this will give you this sort of effect on your data table (see below). That data is still there and can be read, but it does help to hone in on the other rows.

It's usually beneficial when you need to grey out the rows with zero values in a particular column, for example, because they may be less relevant.

In the main example at the beginning of this tutorial, I simply used the default light orange colour for the background.

But, as alluded to earlier, you can mix and match formatting styles like this:

Range and Lookup Alignment

As mentioned earlier, the starting point for the range and the starting point of your lookup column and row must begin on the same row.

If you recall, we set the range as A1:F21 and our starting lookup from C1, so since both start from row one, everything works as expected, but let's just explore what happens when these are different.

Leaving the range as A1:F21 but changing the custom formula start row to row two (C2) sounds logical, as we know we won't find "Colleen Hoover" in row one.

Doing this, however, does not give you the highlighted rows you'd expect, see below. The highlighting is shifted upward by one row.

The solution to this problem is ensuring that the range row and the formula row are at the same starting point, which can either be row 1 or row 2 in this case. I've adjusted this range, and formula to row 2 and everything works as it did when it was set to row 1 in both cases. When these are different, rows will be highlighted, but they won't be the correct ones, so bear this in mind.

The best practice is to ensure your range does not include the headings; subsequently, your formula will start on row two. Including the header row might produce unusual results, but in my first example, it was perfectly fine to include it, so bear this in mind if you highlight the header row, too; it might not be necessary.

I've laid out all the examples in this tutorial in this Conditional Formatting an Entire Row sheet, so please feel free to make a copy of it by clicking the link to add it to your Google Drive. This might come in handy for future reference.

I use this sort of conditional formatting to analyse percentage changes all the time in my sheets, as it can tell a story at a glance with zero effort once it's set up.

This uses conditional formatting, just as we've explored, but in this particular scenario, I'm looking for a CHAR character in the conditional logic. It's quite an interesting use case, so I'll add a tutorial on this example alone, as it's awesome when used in dashboards to showcase your results.