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:
- Apply Conditional Formatting across a whole range based on a value in one or two columns.
- Apply Conditional Formatting across one column based on values in other columns.
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:
- Select the range that you want formatted.
- Click on the Format menu.
- Navigate the dropdown menu to near the bottom and click Conditional formatting…
- The Conditional Formatting sidebar will appear
- Under the Format rules, select the dropdown and scroll to the bottom to select Custom formula is…
- 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!".
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:
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.
Example 1: Conditional Formatting a Whole Range Based on One Column’s Cell Values
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:
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.
Example 2: Conditional Formatting a Whole Range Based on Selected Values and 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.
Example 3: Conditional Formatting a Whole Range Based on Multiple Column Values.
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:
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.
Example 4: Conditional Formatting of a single range Based on Another Column Value.
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.
Example 5: Conditional Format a single column range based on a value in another column – Multiple times.
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
Example 6: Conditional Formatting a Single Column Based on Two Values.
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.
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:
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)
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:
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.