Google Sheets: Conditional Formatting with Custom Formula

Feature inner image credit: Samuel King Jr. 

Google Sheets – Conditional Formatting

Conditional formatting in Google Sheets is a powerful and useful tool to change fonts and backgrounds based on certain rules.

This tutorial assumes that you already have a basic knowledge of Conditional Formatting but would like to uncover the mysteries of the Custom Formula option.

In this post, I will guide you through the steps to build your own custom formulas in oder to:

  1. Apply Conditional Formatting across a whole range based on a value in one or two columns.
    1. Example 1: Conditional Formatting a Whole Range Based on One Column’s Cell Values.
    2. Example 2: Conditional Formatting a Whole Range Based on Selected Values and Formulas.
    3. Example 3: Conditional Formatting a Whole Range Based on Multiple Column Values.
  2. Apply Conditional Formatting across one column based on values in other columns.
    1. Example 4: Conditional Formatting of a single range Based on Another Column Value.
    2. Example 5: Conditional Format a single column range based on a value in another column – Multiple times.
    3. Example 6: Conditional Formatting a Single Column Based on Two Values.

Throughout the examples, we’ll look at various aspects of using Custom Formula to match, use formula functions and apply multiple conditions.

Before we hit the examples, let’s briefly go over accessing the Custom Formula in Google Sheet’s Conditional Formatting.

Getting to Custom Formulas in Conditional Formatting

To access the Custom Formulas in Google Sheets Conditional Formatting:

  1. Select the range that you want formatted.
  2. Click on the Format menu.
  3. Navigate the dropdown menu to near the bottom and click Conditional formatting…
  4. The Conditional Formatting sidebar will appear
  5. Under the Format rules, select the dropdown and scroll to the bottom to select Custom formula is…
  6. In the text box below, we will enter our custom formula.

From this point, the Google Sheets docs on Conditional Formatting are not entirely awesome at demonstrating how to use Custom formulas.

All custom formulas start with an equal sign (=).  They run in a similar way to an if function in Sheets.  The essence of the formula follows this statement:

Format cell if custom formula is …[begin formula here]

This means that your formula does not need to start with =if, . Rather it can jump straight to the condition. For example, =A2 = "Yay!".

Unlike an if function there is no need to provide a value if true or false. This is handled by the Formatting Styles rules.

A final important thing to note is that your condition is based on the first row selected to be formatted. This is essentially the example of how all other rows should be conditionally formatted.

A most basic example would be this:

Say we want to make Column B red if the word “Yay!” appears in the adjacent row. We would select column B and in our custom formula we would type:

= A2 = “Yay!”

It would look like this:

Conditional Formatting Custom Formula Google Sheets

Let’s move on to some detailed examples.

Apply Conditional Formatting across a whole range based on a value in one or two columns

To conditional format a whole range of data so that the entire row is highlighted, you will need to first select all the data you want to apply conditional formatting to.
Top

Example 1: Conditional Formatting a Whole Range Based on One Column’s Cell Values

Conditional Format Entire Row Google Sheets

In this first example, we have a sales list by month, state and value. We want to highlight the entire row of states Queensland in maroon and New South Wales in Blue.

To do this, we first select the entire range of all the data we want to apply conditional formatting to. In our case, this is cells A4:C15.

We then enter the conditional formatting sidebar and select custom formula. Column B has the different State values we want to use to format our rows. In the custom formula input field, we write the condition based on the first row value, row 4. Let’s do New South Wales first:

= $B4 = "New South Wales"

This formula states that if B4 is equal to New South Wales, then add the selected formatting to the entire row.

We then select the appropriate background colour to identify the State.  In this case, blue.

Next, we add the formatting for Queensland:

= $B4 = "Queensland"

Again, we add the formatting colour maroon and change the text colour to white for the entire row.

You can see, that by adding the value for the first selected row of the range, the formatting will continue for all subsequent ranges.

But what’s with the dollar ($) sign at the start?…

Absolute vs. Relative References

If you are unfamiliar with the function of the dollar ($) sign on cell ranges,  I recommend you check out my tutorial:

How do I Lock Certain Cells in a Formula in Google Sheets?

Essentially, when the dollar sign is placed next to the row or column value, it locks those values (absolute reference). When we enter our custom formula, conditional formatting then goes ahead and does the same job as if we dragged a cell across all of the values.

This means that if our formula was this:

= B4 = "Queensland"

…and our range is A4:C15, then the custom formula would look for Queensland in the entire range in locations like A4, C10, B14, A8 etc. The formula is relative to the cell reference. We need to lock that B column in with our dollar ($) sign – $B4 so that it only looks in column B.
Top

Example 2: Conditional Formatting a Whole Range Based on Selected Values and Formulas.

Conditional Format Entire Row Google Sheets with formulas

In this example, We have a list of people by age, height, and weight. We want to conditionally format the entire row of those people who are considered overweight based on their Body Mass Index (BMI).

This example highlights (hurh hurh hurh, pun) the use of formulas based on cell values.

On our first row, we are using the BMI formula Weight(kg)/Height Squared (m2). Our weight is in column D and our height is in centimeters in column C. Any BMI value greater than or equal to 25 is considered overweight. So, our formula would be:

= $D4 /($C4/100 * 2) >= 25

Remember we need to ensure that we lock in the correct columns with the dollar sign ($).

Again, once the first row is defined, conditional formatting will automatically update consecutive selected rows.
Top

Example 3: Conditional Formatting a Whole Range Based on Multiple Column Values.

Conditional Format Entire Row Google Sheets with functions

In this example, we look at using functions in a custom formula to format an entire row.

On our Potato Chip Rating Google Sheet, we want to highlight those rows that are not Thin chips and have an overall rating greater than 22.5 out of 25.

Note: You might see in the image above that the end of the formula reads 2 instead of 22.5. This is because the formula is so long that is missing out the last few digits. 

In this example, we have to meet two conditions. To achieve this we are using the Google Sheets AND function. AND takes multiple conditions – or logical expressions – that all must be met before the conditional formatting is implemented.

Our first condition is that Type in column A must not be Thin. Not equal to is stated by the <> characters. As such, we represent this condition in the following way:

$A4 <> "Thin",

Our second condition is the sum of the values of each row from column D to column H. We use the SUM function to accomplish this. If the sum of these values is greater than 22.5, we meet the second condition. This is how we represent it:

, SUM($D4:$H4) > 22.5)

Putting these two conditions together in our AND function we get:

=AND($A4<>"Thin",SUM($D4:$H4) >22.5)

Remember to lock those columns with your dollar sign ($).

Apply Conditional Formatting across one column based on values in other columns.

Another common use of custom formulas in conditional formatting in Google Sheets is to format a cell value in a column based on criteria in other columns not selected.

In preparation for this use case, you select only the column you want to highlight and reference the other columns in your custom formula.
Top

Example 4: Conditional Formatting of a single range Based on Another Column Value.

Conditional Format Row Google Sheets based on other column values

This example looks at the number of returns over a two week period and if the returns are greater than two, it highlights the day of the week.

For this conditional format, we only want the day of the week column B to be highlighted based on our condition. This means that we only select the range, B4:B24.

Our custom formula would then simply be:

=$C4 > 2

Here, locking the Column does not matter as much because we are only referring to one cell to format and another to determine the condition. However, it is a good habit to get into.
Top

Example 5: Conditional Format a single column range based on a value in another column – Multiple times.

Conditional Format Custom Formula based on other col vals

This example I use regularly for work.

In this example, I want to black out all the cells that do not require the user to add a grade into. This is based on the selections of the checkboxes on the left.

My custom formula is quite simple:

= B5 = False

This is applied to the first to cell F5.

You will note that my selected range is F5:I14. Here, I really am making use of the relative reference by NOT using the dollar sign ($).

When conditional formatting looks at my original formula it will see that it is should change the row and column number relative to the cell it is conditional formatting. For example:

  • G5 formula would be = C5 = False
  • I9  formula would be = E9 = False
  • H12 formula would be = D12 = False

Top

Example 6: Conditional Formatting a Single Column Based on Two Values.

Conditional Format Row Google Sheets based on other column values 2

In the final example, we want to highlight the cell in column B if the cell value is greater than 50 and the Region is either East or West.

To do this we are going to make use of the Google Sheets AND and OR functions.

Let’s build this custom step-by-step.

Our first condition is that we want the value in column B to be greater than 50:

$B4 >50)

We then want the value of column A to be either East or West. We’ll put these two options in our OR function:

OR($A4 = "East",$A4 = "West"),

Finally, we want the two conditions, East or West, and greater than 50 inside our AND function:

=AND(OR($A4 = "East",$A4 = "West"), $B4 >50) 

Top

Conclusion

You can use custom functions to achieve a wide variety of conditional formatting tasks. You can make use of complex formulas, a variety of conditions using AND and OR functions, and use a number of other functions.

Try adapting the tools and examples provided above to your own unique case. It would be awesome to see what you came up with.

In the meantime, check out the examples again below:

Or take the link to the Google Sheet:

Conditional Formatting: Custom Formula

Want a solid step-by-step course to become a pro at Google Sheets? Udemy has some professional courses that will turn you into an admin ninja!

I’m a huge fan of Justin Mares, Mastering Google Sheets course.*

 

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

 

 

16 thoughts on “Google Sheets: Conditional Formatting with Custom Formula”

  1. Hey, this is really helpful, thanks for sharing! I’ve taken some info from your blog and tried to create a conditional format for my own sheet, but getting a little stuck.

    The formula is =OR($I2<today()+28,$L2<today()+28,$O2<today()+28,$P2<today()+28,$Q2<today()+28,$R2<today()+28,$S2<today()+28).

    Basically, if any of the dates in those cells are less than 28 days from todays date, cell A2 is formatted. This works fine, but when I apply it to the rest of column A, it continues to look at row 2, rather than referencing the row that the formatted cell is in.

    I almost need the relative equivalent of the $ sign to force the formula to by relative rather than always looking to row 2. Any ideas?

      1. Yes, but for some reason when I apply it to a range, it only looks at the cells in column 2. Our formulas seem to have the same format, but yours seems to apply to each row…

  2. Hi, this was very useful, but I want to have Column A be highlighted Green if column B contains either of 2 values as well as if column C contains either of those values simultaneously. i.e., if Column B has a 1 or 2, AND Column C has 1 or 2, color Column A green, but if only 1 of the 2 columns has the value, dont color it

    1. Hi Justin,

      Have a play around with the AND and OR formulas inside conditional formatting, with a little trial and error this should do the trick.

  3. Dear Yagisanatode,
    your article helped me colourize my partner sheet, so we can easily visualize which partners agreed to work with us. Thanks! Gratefully, The Simplemoves.ca team

  4. Hi! So right now I have a stock ticker thing on a google sheet and I have the percent gain/loss all color-coded the same. But I cannot do the same to the actual price gain because they don’t all go up and down at the same value. (EX: on a good day BAC could go up $1, but that would be a bad day for AMZN) How could I get the color on the percent change, to be the same as the actual price change? (Is there any way to just mimic the color?)

    Here is the link to show you what I am working on:
    https://docs.google.com/spreadsheets/d/1fXilwGZrA7lg1RBzma6ArBu2bZVnScNUCyJEkMhqNRc/edit?usp=sharing

    1. Hi Tim,

      If I get you correctly, you want to extend the conditional formatting across two columns. If you get a price change in say column C you want it also reflected in column D for example. You can do this by making the initial column C absolute.

      Let’s say your range is C2:D200. If the number in col C is greater than 0 then you want to mark the row for columns C and D green.
      1. Select both columns in the conditional format.
      2. add the formula like this =$C2 > 0 <=== the $ sign markes column C as absolute, effectively locking the column. For conditional formatting, this means that if the condition is met in this column, then you can format the row.

      I hope this helps.

      ~Yagi

  5. I am so grateful for this excellent article. I have struggled to understand all the different ways you could use formula in conditional formatting. You have unlocked a wealth of new capabilities.
    Thank you, thank you, thank YOU!

  6. Hi. I have an issue where using the custom formula I need to trigger the formatting based on a keyword within a text phrase. This works fine using the regular “format cells” and other is selected.

    Unfortunately, I haven’t found a way for the custom formula to look in one cell that has the following phrase: “Rewards earned: 0.02 ~ Category: Other”

    I want conditional formatting to highlight the entire line based on the word “Other” inside the cells that also contains “Rewards earned: 0.02 ~ Category: Other”

    Is this possible?

  7. Can you help me understand why my range selection is not working properly. I’m trying to change my text color in range

    E3:E1001,F3:f1001,G3:G1001

    to red text if

    =($K3:K1001=”NOF”)+($K3:K1001=”INCOMPLETE”)+($L1:L1001=”NOF”)+($L3:L1001=”INCOMPLETE”)+($M3:M1001=”NOF”)

    holds true.

    The formula works great when the range is only through one column E3:E1001, but doesn’t work when trying to expand it over a range of cells.

Leave a Reply