Google Sheets conditional formatting custom formula

Conditional formatting is a super useful technique for formatting cells in your Google Sheets based on whether they meet certain conditions.

For example, you could use it to apply background colors to cells based on the value in the cell.

You can go further than this though, and apply the formatting across an entire row, based on the value in a single cell.

For example, if the continent is Africa in column C, you can apply the background formatting to the entire row (as shown by 1 and 2):

Five steps to apply conditional formatting across an entire row

Its actually relatively straightforward once you know the technique using the $ sign (Step 5).

Step 1. Highlight the data range you want to format

The first step is to highlight the range of data that you want to apply your conditional formatting to. In this case, Ive selected:

A2:C13

Step 2. Choose Format > Conditional formatting in the top menu

Open the conditional format editing side-pane, shown in this image, by choosing Format > Conditional formatting from the top menu:

Step 3. Choose Custom formula is rule

Google Sheets will default to applying the Cell is not empty rule, but we dont want this here.

Click on the Cell is not empty to open the drop-down menu:

Scroll down to the end of the items in the drop-down list and choose Custom formula is. This will add a new input box in the Format cells if section of your editor:

Step 4. Enter your formula, using the $ sign to lock your column reference

In this example, I want to highlight all the rows of data that have West in column A. In this new input box, enter the custom formula:

The key point to understand is that you lock the column you want to base your conditional formatting on by adding a $ (dollar sign) to the column reference.

I start inputting the first cell of my highlighted range:

= A2

Then I add the $ (dollar sign) in front of the A only:

= $A2

Then I add the test condition, in this case whether the cell equals West:

= $A2 = "West"

As the conditional formatting test is applied across each row, the value from the first cell in column A is used in the check.

More examples of conditional formatting across an entire row

Based on a threshold value

This is a super useful application of this technique, to dynamically highlight rows of data in your tables where a value exceeds some threshold.

In this example, Ive highlighted all of the students who scored less than 60 in class, using this formula in the custom formula field:

= $C2 < 60

Based on checkboxes

Google Sheets checkboxes are super useful. If you haven't heard of them or used them yet, you're missing out.

When a checkbox is selected it has the value TRUE, and when it is not selected the cell has the value FALSE. So we can use that property in our custom formula:

= $B2 = TRUE

Learn more
Check out my beginner course and master key techniques to become confident with Google Sheets: Google Sheets Essentials