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!".
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:
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
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.
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.
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.
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.
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.
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)
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
~Yagi
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?
Hi Mike,
Were you looking at something like this?
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…
Are you able to take a screen shot of your conditional formatting sidebar?
This is so helpful, I think this is the most well-explained I’ve seen this
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
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.
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
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
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
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!
Thanks, Doug,
I’m glad you found it helpful.
cheers,
Yaig
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?
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.
Hey Yagi,
Your tutorials are awesome! Do you have a discord channel or a Facebook community where beginner to experts can join?
I am having trouble using the custom formula when I’m looking for a cell with a 0. I am trying to highlight cells based on the text of one specific cell. I am using the formula =$J$12=0. It is assuming that if the cell is blank, that it is 0 because it has a value of 0. Is there any way around this?
I have a sheet where I have a list of 177 colours in columns G, J and M. In columns H, K and N, there’s a count of how many times that colour occurs elsewhere in the Sheet – this is actually a Sort formula, so that the colours that appear most often appear at the top of the list with the colours that don’t appear below them, with many colours not being used at all (but it’s a set list of options, so they are all there).
What I want is to highlight the cells with the colour that they represent, which I’ve done. But, what I’d really like to do is have it so that both the Colour cell and the Count cell format, and that no formatting is added if the colour does not appear (count is 0).
I believe I’ve got that working using your tips above – which are awesome and clear, thank you! – using something like =AND($G2=”Maize”,$H4>0) – but when I try to apply that over the three different ranges (G2:H178, J2:K178 and M2:N178) it doesn’t work because it’s applying the same thing to all the ranges, which is to look only at Columns G and H. This is further complicated by the fact that the lists change order, so a colour might be in row 5 in one range and row 8 in another). I could just do each range separately, but with 177 colours in three ranges, that becomes 531 separate rules, which is a bit much.
Am I missing something or is it not possible to highlight cells based on 2 conditions in relative cells, over multiple ranges?
Hopefully you understand what I’m saying!!
With conditional formatting, can you compare a cell in sheet X to another cell in sheet Y. For example if Data!A1 > Source!A1, then I want it green.
Hi Kevin,
Yes, you can but it is a little tricky. Try using the INDIRECT function on your cell reference. For example
=INDIRECT("Source!A1") > A1~Yagi
Hi Yagi, thanks for explaining things so clearly, it has helped me a lot. I do get stuck with one thing, perhaps you know the answer: I want to format a cell based on the value in another sheet, but this doesn’t seem to be allowed. If my condition is C3<>”” then things work fine, but when I change it to second_sheet!C3 I get “Invalid formula”. Any ideas? Thx!
Hi Edwin,
Funnily enough, I have only recently responded to a question just like this from Kevin Anderson:
Incidentally, if you also want to determine your conditional formatting from data in another Google Sheet workbook, you could create a hidden Sheet tab in your current doc and use IMPORTRANGE to import the relevant columns. Then you can use the method above to reference them.
Hope this helps.
~Yagi
Hi,
I am working with two different work groups and need to create a Conditional formating as follows:
Group A
Score Conditional Formating Color
90% Green
Between 85% and 89.99% Yellow
Group B
Score Conditional Formating Color
85% Green
Between 80% and 84.99% Yellow
I am able to get the format for the Green and Red Formating using the formula in Example 6, but get stuck with trying to set the Yellow formating for their scores so as they appear in the same spreadsheet. Can you please help me?
Hi Jose,
From what I understand of your comment, it sounds like you have a column with percentage data. You want to give set yellow to any % that is either 90% or 85% and green for any % between either 85%-89.99% and 80%-84.00%.
For this scenario, I would use the standard conditions and set the two ‘isexactly’ conditions first (this will give them dominance over the formatting. Then set the ‘isbetween’ values. Essentially creating 4 separate conditions. This way your overlap Green 85% will be displayed.
Apologies if I misunderstood your query. Feel free to share a read-only link to your doc or a sample of what you would like to achieve and I or one of my readers may be able to guide you.
~Yagi.
Hi Yagi,
Thank you for your reply. I guess what I am trying to do is use a single spreadsheet for two different groups (group A and group B). Each of these groups have different goals but I don’t have to seperate them in a separate spreadsheet.
Here is a sample spreadsheet: https://docs.google.com/spreadsheets/d/1p9IcBcqvLxJPjVU-DTjaZdnZDM64iitL0qwKMha-Nog/edit#gid=0
KEY for Conditional Formating
GROUP A
GROUP B
I think I have managed to create the conditional formating with the function in Example 6 for the > and < values, but am having trouble with the between value for each group.
Thank you for your help!
Jose,
Ah, I understand. For more complicated conditional formatting, I recommend working it out in a cell first and then pasting it in. This is much easier to test.
So you could do some custom conditional formatting for all six items:
KEY
GROUP A
GROUP B
If you want to be tricky at the sake or readability, you could do something like this:
=OR(AND($B3 = “Group A”, $D3>90%),AND($B3 = “Group B”, $D3>85%))
Here is a copy of your sheet with the completed formatting:
https://docs.google.com/spreadsheets/d/10FJ66aGtDC7OJD_6GMFrxaZOjlgC7DuAxo02oB0N3TA/edit?usp=sharing
Go to File > Make a copy for you to inspect the formatting.
~Yagi
I’ve created drop down queue list with separate formats for each selection “Spoke”, “Speaking”, and “Up Next”. I want to make sure that only one person can be marked as “Speaking” at a time, so whenever I change one box to “Speaking”, the previous one marked as “Speaking” will automatically change to “Spoke”. This would be simple if each person on the list is speaking in order, but we sometimes have to move on and circle back to people. What formula can I use?
Hi Taya,
This is a little outside the scope of this tutorial and wouldn’t be handled with conditional formatting. Your best bet would be to use a bit of Google Apps Script to achieve this.
~Yagi