Google Sheets: Conditional Formatting Row Based on Cell

Conditional formatting is quite a versatile function in any spreadsheet application. And today we will see the conditional formatting of a row/rows based on a cell in Google Sheets.

Basics of Conditional Formatting a Row in Google Sheets

We start by understanding some key points and steps involved when conditionally formatting a row in Google Sheets.

Here we have a typical dataset of different values which will allow us to check for different types of conditions:

dataset for conditional formatting row based on cell in google sheets

For our example, we will highlight all the rows that contain the region, Florida.

Step 1: Navigate to Conditional Formatting from the Format tab.

navigating to conditional formatting from the format tab

Step 2: The first thing we do in the Conditional format rules menu is to determine our formatting range (if you haven’t already done it). Click on the grid icon in the Apply to range section to open the Select a data range window. Simply input or select the range of cells from your worksheet. Click OK  to apply.

applying data range for conditional formatting

Step 3: Next is the most important part of our process, the formatting rules, namely the formulas we are going to apply. To inout our  custom formulas in conditional formatting, click the drop-down menu under the Format cells if… section to find the Custom formula is option at the bottom of the list.

navigating to the custom formula is option

Step 4: Our highlight condition was: Highlight all rows from the Florida Region. So, our formula will be:


basics of conditional formatting row based on cell in google sheets

The absolute ($) added in front of the column E reference means that the column is fixed whereas the row references are free to move, thus the formatting is painted across the entire row.

Step 5: Choose the type of formatting you want to apply and click Done to finish.

To learn more about conditional formatting rows according to different types of cell values, please visit our Change Row Color Based on Cell Value in Google Sheets article.

With the basics out of the way, let’s see some scenarios where conditional formatting based on cell comes in handy.

Read More: Conditional Formatting with Multiple Conditions Using Custom Formulas in Google Sheets

Other Scenarios of Conditional Formatting Row Based on Cell in Google Sheets

1. Conditional Formatting Row With a Value Based on a Different Cell

Continuing from our last example, we will once again try to highlight rows according to region, but this time, our Region name will come from another cell in the worksheet.

the condition cell is now within the worksheet

The drop-down list, located in cell E14, contains all the region names of our dataset. We will be using this cell as a reference in our custom formula.


conditional formatting row based on cell reference in google sheets

Note: You must apply absolute cell reference to the condition cell (cell E14 in our case, $E$14) otherwise the rows will not be highlighted properly.

Our custom formula in action:

conditional formatting row based on cell reference in google sheets animated

Please visit our Conditional Formatting Based on Another Cell in Google Sheets to learn more and see more examples.

Read More: Highlight Row If Cell Contains Text with Conditional Formatting in Google Sheets

2. Using Regular Expressions

We can already see the use of custom formulas in conditional formatting in Google Sheets. We can take it a step further with these formulas to lookup certain strings within the selected range and highlight the row matches.

For example, we could look up partial text matches, like the name “Bill” from the dataset.

looking for the string Bill in our dataset

However, with a general formula, this won’t be possible since Google Sheets cannot recognize partial text. For that, we have to use Regular Expressions, and by extension, the REGEXMATCH formula.

=REGEXMATCH($C3, "\ABill\s*([^\n\r]*)")

using regular expression to conditionally format row in google sheets

Read More: Use REGEXMATCH Function for Multiple Criteria in Google Sheets

Similar Readings

3. Based on Checkboxes

Let’s move to something much simpler in our next example. Data validation techniques like checkboxes are widely used in many spreadsheets.

What if we want to go a step further and use conditional formatting to highlight the rows that have been checked?

The custom formula for it is quite simple:


Since C3 contains our condition and we are checking if the condition is TRUE or FALSE. If TRUE, the conditional formatting will highlight the row.

using checkbox to conditionally format a row

Read More: Conditional Formatting with Checkbox in Google Sheets

4. Based on Multiple conditions

In our final example, we will try to highlight an entire row based on multiple conditions. We already know that conditional formatting in Google Sheets works in Boolean, meaning if the condition stated by the user is TRUE, we get a highlighted cell.

Understanding that, for multiple conditions, we can approach our custom formula by making sure that all conditions are TRUE (AND format) or if just one condition is TRUE (OR format)

Luckily, we have AND and OR functions in Google Sheets to help us with that.

For now, let’s highlight rows for the following conditions:

  1. Region: New York
  2. Sales: More than 400 units

Since we want both conditions to be TRUE, we will use the AND function. Thus, our formula to highlight the rows satisfying these conditions will be:

=AND($E3="New York",$D3>400)

multiple conditions for conditional formatting row based on cell in google sheets

Again, to highlight the entire row, our cell reference needs only to have the column references locked with absolutes ($).

Read More: Google Sheets: Conditional Formatting with Multiple Conditions

Final Words

That should cover how we can conditionally format a row based on cell in Google Sheets with some extra examples to clarify its potential. We hope that the discussion of the various scenarios comes in handy for your tasks.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply