How to work with conditional formatting in Google Sheets

In this post, we'll take a closer look at conditional formatting in Google Sheets and learn the quickest and most effective ways of setting it up. We'll consider several examples to see how to create conditional formatting with one or several conditions, and how to color cells or change font color by custom criteria. We'll pay particular attention to conditional formatting based on other cells.

What is Google Sheets conditional formatting?

Why do we need conditional formatting in a table? Isn't it easier to format cells manually?

Highlighting particular data with color is a great way to draw attention to the records. Many of us do this all the time. If cell values meet our conditions, e.g. they are greater or less than some value, they are the greatest or the smallest, or perhaps they contain certain characters or words, then we find such cells and change their font, font color, or background color.

Wouldn't it be great if such changes to formatting occurred automatically and drew yet more attention to such cells? We would save a lot of time.

This is where conditional formatting comes in handy. Google Sheets can do this work for us, all we need is to explain what we want to get. Let's look at some examples together and see how simple and effective it is.

How to add a formatting rule with one condition

Suppose we have chocolate sales data in our table. Each row in the table contains an order we got from a particular customer. We used drop-down lists in column G to specify if it was completed.
A typical Google spreadsheet with sales data.

What can be interesting for us to see here? First, we can highlight those orders that exceed $200 in total sales. We have these records in column F, so we'll use our mouse to select the range of values with the order amount: F2:F22.

Then find the Format menu item and click on Conditional formatting.
Find Conditional formatting item in the standard Google Sheets menu

To begin with, let's consider Google Sheets conditional formatting using a single color.

Click Format cells if..., select the option "Greater than or equal to" in the drop-down list that you see, and enter "200" in the field below. This means that within the range we selected, all cells with values that are greater than or equal to 200 will be highlighted using the format we set right at the same place: bold red font in the yellow background.
Set up conditional formatting rule in Google spreadsheet

We can see our formatting rule applied right away: all the necessary cells changed their appearance.

You have the choice of setting up conditional formatting not only with one hue but using a color scale. To do this, select Color scale in the conditional format rules sidebar and use ready sets of color. You can also pick hues for the minimum and maximum points, as well as for the midpoint if necessary.
Use a color scale for the values to format in Google Sheets

Here we created a color scale where the cells get lighter as the order amount gets smaller, and darker as the sum increases.

Format cells in Google Sheets by multiple conditions

If the color scale seems too bright to you, you can create several conditions under the "Single color" tab and specify a format for each condition separately. To do this, click "Add another rule".

Let's highlight the orders that are over $200 in Total sales, and those that are under $100.

As you can see, we have two formatting conditions here. The first one is for values that are greater than 200, the second one concerns values that are less than 100.
Conditional formatting rules in Google spreadsheet

Tip. You can add as many conditional formatting rules in Google Sheets as you need. To delete it, just point to it and click the Remove icon.

Google Sheets conditional formatting with custom formulas

The suggested list of the conditions that we can apply to our data range is quite vast. However, it may still not be enough. Sooner or later you will need to create a condition that can't be described using the standard means.

That's why Google Sheets provide the possibility to enter your own formula as a condition. This formula lets you describe your requirements using standard functions and operators. In other words, the result of the formula must be either "True" or "False".

Use the last item in the drop-down list to enter your formula: "Custom formula is".
Google spreadsheet lets you create conditional formatting rules with a custom formula

Let's see how it works.

Say we want to know which among our orders were made during the weekend. None of the standard conditions works for us.

We will select the range of dates in A2:A22, go to the Format menu and click Conditional formatting. Select the "Custom formula is" item in the "Format cells if" drop-down list and enter the logical formula that will help us identify the day of the week by the date.

=WEEKDAY(A2:A22,2)>5

If the number is greater than 5, then it's Saturday or Sunday. In this case, the formatting we set below will be applied to the cell.
Highlight data in Google Sheets by custom condition

As you can see, all weekends are highlighted with color now.

Here is another example. Let's bring out the orders for dark chocolate with the help of a different format. We follow the same steps to do this: select the data range with the types of chocolate (D2:D22) and use the following condition:

=REGEXMATCH(D2:D22;"Dark")

This function will return "True" if the name of the chocolate type contains the word "Dark".
Highlight cells based on the text they contain

Look what we got: the orders for Dark Chocolate as well as for Extra Dark Chocolate became emphasized. No need to look through hundreds of rows to find them now.

Use wildcard characters with conditional formatting in Google spreadsheets

If we want to format text values, then the standard "Text contains" condition is essential.
Format text values by the text they contain

You can use special wildcard characters to add some flexibility to the search condition.

Tip. Wildcard characters can be used in the "Text contains" and "Text does not contain" fields as well as in your custom formulas.

There are two most commonly used characters: the question sign (?) and an asterisk (*).

The question sign corresponds to any single character. For example, as you can see in the screenshot, the text rule that contains "??d" formats cells with such values as "Red", but not such as "Dark".

"??d" means that letter "d" should come third from the beginning of the word.

Use an asterisk to omit zero to any number of characters. For example, a rule that contains "*d*" should format both cells: with "Red" as well as with "Dark" values.

For the question and asterisk characters not to be perceived as wildcard characters in your text values, a tilde (~) is usually added before them. E.g. the text rule that contains "Re?" in our example formats the cells with "Red", while the rule with "Re~?" won't find any cells as it will be looking for the value "Re?".

How to use Google Sheets conditional formatting to highlight entire rows

In the examples we described above, we applied conditional formatting to certain cells of a column. Perhaps you thought: "It would be so nice if we could apply this to the whole table!". And you can!

Let's try to highlight any unfulfilled orders with a special color. To do this, we need to use the formatting condition for the data in column G where we specified if the order was completed, and we shall format the entire table.
Format the entire row based on the condition in one column

Note. Please note that we applied formatting to the entire table A1:G22.

Then we used our custom formula where we specified that:

=$G1="No"

Tip. You need to use the dollar sign ($) before the name of the column. This creates an absolute reference to it, so the formula will always refer to this particular column, while the row number can change.

In other words, we ask it to move down within the column starting with the first row and look for all cells with the value "No".

As you can see, not only the cells that we checked for our condition became formatted. Conditional formatting is now applied to entire rows.

So, let's remember 3 basic rules to conditionally format rows in a table:

  • The range to be formatted is the entire table
  • We use conditional formatting with a custom formula
  • We must use the $ character before the column name

Google Sheets conditional formatting based on another cell

We often hear the question "How do we apply conditional formatting and make it easy to change the condition?" This is not difficult at all.

Just use your own formula with a reference to the cell where you specify the necessary condition.

Let's go back to our sample data with the orders for chocolate in Google Sheets. Suppose we are interested in the orders with fewer than 50 and more than 100 items. We'll go ahead and enter these conditions in column H next to our table.
Use custom formulas to quickly edit the conditions

Now we shall create conditional formatting rules for the table of orders.

We set the range to format to "A2:G22" to keep the table header as it is.

Then we follow the steps you know and use our formula.
Refer to the cells with conditions in custom formatting rules

Here is how conditional formatting formula for the orders with over 100 items looks:

=$E2>=$H$3

Note. Please note that you need to use absolute references ($) when using cells outside the table.

A dollar sign before the column name means the absolute reference to the column. If the dollar sign is before the row number, then the absolute reference goes for the row. For more info, please check out this detailed discussion of cell references.

$H$3 in our example means an absolute reference to the cell, i.e. whatever you do with the table, the formula will still refer to this cell.

Note. We need to use an absolute reference to column E and an absolute reference to cell H3 where we have our limit of 100. If we don't do this, the formula won't work!

Now let's add the second condition to highlight the orders with fewer than 50 items. Click "Add another rule" and add another condition just like we did for the first one.
Add multiple custom conditions

Please see the formula we use in our conditional formatting rule:

=$E2<=$H$2

The largest and the smallest orders are now highlighted with color. The task is accomplished. However, it's not nice that we got extra numbers in our sheet, which may be confusing and ruin the way the table looks.

Placing auxiliary data in a separate sheet would be a better way to go. I will describe it in more detail in my next post when we learn how to create drop-down lists.

Let's switch to sheet 2 and enter these new conditions there.
Enter the conditions in a separate-sheet

Now we can create conditional formatting rules for the table of orders by referring to these limits.

Here is where we may face an issue. If we simply use the address of the cell from sheet 2 in the formula, we'll get an error.
You may see

Note. Direct cell references in the formulas for conditional formatting are possible only from the current sheet.

So, what shall we do now? The INDIRECT function will help. It lets you get the cell reference by writing its address as text. Here is how the cell reference within a conditional formatting formula will look like:

=$E2>=INDIRECT("2!G2")
use INDIRECT function to refer to cells in other sheets

Here is the second formula:

=$E2<=INDIRECT("2!G1")

As a result, we get the same outcome as before, but our sheet is not cluttered with additional records.
Bring conditions to other sheets

Now we can change formatting conditions without updating the rule settings. It's enough to simply change the records in the cells, and you get a new table.

Google Sheets and conditional formatting based on another cell text

We have learned how to apply conditional formatting rules by using numeric data from a certain cell. What if we want to base our condition on a cell with text? Let's see how we can do this together.

We'll try to find the orders for dark chocolate:

In cell G5 of Sheet 2, we enter our condition: "Dark".

Then we return to Sheet 1 with the table and select the range to format again: A2:G22.

Then we select Format menu, pick Conditional formatting, and enter the following formula into the Custom formula is field:

=REGEXMATCH($D2:$D22,INDIRECT("2!$G$5"))

Tip. Remember that you need to enter absolute references to the range you need to check for the word "Dark" (D2:D22).

The function INDIRECT("2!$G$5") enables us to get the value from cell G5 of Sheet2, i.e. the word "Dark".
Format entire rows based on the value in another cell in Google Sheets

Thus, we have highlighted the orders that have the word from cell G5 of Sheet 2 as a part of the product name.

We could make it easier, of course. Our formula would look this way:

=REGEXMATCH($D2:$D22,"Dark")

However, in case we wanted to find a different product, we would have to edit the conditional formatting rule. This takes a little longer than simply updating the value in cell G5.

Remove conditional formatting from your Google spreadsheet

You may certainly need to remove all conditional formats from your table.

To do this, first, select the range of cells where you have conditional formatting applied.

You will see all the rules that you created in the sidebar.
Delete conditional formatting rules from Google sheets

Point your mouse to the condition that needs to be deleted and click the "Remove" icon. Conditional formatting will be cleared.

If you don't remember the exact cell range you formatted, or if you want to get rid of formats as quickly as possible, then select the cell range and go to the Format menu - Clear formatting. You can also use the combination of keys Ctrl + \.

Note. Remember that not only conditional formatting, but all other formats used in your table will be cleared in this case.

We hope that applying conditional formatting in Google Sheets will simplify your work and make the results more graphic.

Table of contents

156 comments

  1. Hello! I am working with data across multiple sheets. I have first and last names on a master list, and if the same names appears on another list I need them to filter off or highlight to avoid duplicates in my data. Do you have a suggestion on how to filter this data. Since the information is dynamic I am not sure how to approach this.
    Thank you in advance!

  2. Hello!
    Is it possible to use conditional formatting to identify if the numbers in 2 cells are different from each other based on only the first 2 digits of the numbers?
    Example:
    Column A. Column B
    492110 488510
    334419 334417

    I would need for row 1 to be indicated, but not row 2 (only the first 2 digits of the number matter). I'm trying to do this with a custom formula in conditional formatting because the data itself is being brought into the sheet using a query+unique combo.

    Thank you!

  3. Not sure if my previous comment went through - can I please get some help on conditional formatting for two conditions. The AND function doesn't seem to make a difference. (I've shared my sheet)

    • Cindy, I've just looked into your spreadsheet. The problem is not in the formula but in the zero values returned to the cells. You see, they're returned as text, not as numbers, that's why the formulas in the conditional formatting rules don't work.

      For example, your C4 returns 0 from the John Toomey sheet. But on that sheet, you return 0 with this formula: =IFERROR(AVERAGE(E12:E14), "0")
      Double quotes are used for text in Google Sheets, hence this 0 is a text. Remove double quotes to return 0 as numbers. Then your conditional formatting rules will be able to compare your numbers with these zeros and color everything correctly:
      =AND($B4<=C4,C4>0)

      I didn't look at other cells though, but seeing how many zeros are colored green, I'd suggest you to look through all your formulas and remove all double quotes from all zeros.

  4. Hi, I'm looking for a formula for conditional formatting that will colour my cells only if they meet the criteria AND if they are more than 0. I've tried =AND($B4<=C4, C40) but that doesn't make any difference to =$B4<=C4. There are formulas in my cells but the ones that have 0 have no data to reference as yet.

  5. I am creating a Superbowl Squares on google sheet. I am trying to get the conditional formatting color to show up in the squares from the winner cell.

    I use this formula in the winner cell for each quarter, this formula is the first winner from the squares:
    =IF(OR(O32="",M32="")," ",INDEX($K$10:$T$29, MATCH(MOD(M32,10),$J$10:$J$29,0),MATCH(MOD(O32,10),$K$9:$T$9,0)))

    But I want the conditional Formatting squares to change to fill in that square cell in Blue and its not happening as I will have to manually input he blue color in the square is indicate the winner of the 1st quarter of the game.

    • Hello Robert,

      For me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) with your data and conditional formatting rule that doesn't work. Please also include an extra sheet showing what you're trying to get. I'll look into it.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

    • Anyone alive in this thread?? Any can't help me with this situation? ;(

      • Natalia,

        Sorry I didnt see your comments on my end till I refreshed my browser. Yes I send a share file that you asked me to do as I added you on.
        Let me know if you have access to my google sheet file.

        • I've got your file, Robert.

          As far as I can see, you use the formula mentioned earlier in Q32:R35 to return the winners' names. Please specify what cells exactly (Q32:R35 or the names in K10:T29) should be filled with blue and under what conditions. You can also include a copy of this sheet showing what you're trying to color and when.

          • In the first quarter the names in K10:T29 should be filled with Blue same goes for the reverse winner.
            In the first quarter the names in K10:T29 should be filled with Orange same goes for the reverse winner.
            In the first quarter the names in K10:T29 should be filled with Purple same goes for the reverse winner.
            In the first quarter the names in K10:T29 should be filled with Light Yellow same goes for the reverse winner.

            • Thank you for the details, Robert.

              Since I don't have the right to edit your file, I will leave the instructions for your conditional rules here.

              The 1st quarter and blue colour:
              Apply to range:
              J8:T28
              The custom formula for the rule:
              =OR(ADDRESS(ROW(),COLUMN(),4)=CHAR(64+MATCH(MOD($M$31,10),$A$8:$8,0))&MATCH(MOD($O$31,10),$J$1:$J,0),ADDRESS(ROW(),COLUMN(),4)=CHAR(64+MATCH(MOD($O$31,10),$A$8:$8,0))&MATCH(MOD($M$31,10),$J$1:$J,0))

              The 2nd quarter and orange color:
              Apply to range:
              I7:T28
              The custom formula for the rule:
              =OR(ADDRESS(ROW(),COLUMN(),4)=CHAR(64+MATCH(MOD($M$32,10),$A$7:$7,0))&MATCH(MOD($O$32,10),$I$1:$I,0),ADDRESS(ROW(),COLUMN(),4)=CHAR(64+MATCH(MOD($O$32,10),$A$7:$7,0))&MATCH(MOD($M$32,10),$I$1:$I,0))

              You can see a pattern here.
              Each Apply to range takes the row of the required quarter into account.
              There are two formulas (for the winner + for the reverse winner) that start with the ADDRESS function.
              Both these formulas are nested in OR so both of them are checked at the same time.
              The range in the first MATCH in each formula starts with a row of the required quarter.
              The range of the second MATCH – with the column of the required quarter.

              I believe you'll be able to adapt these for the last two quarters :)

  6. Hi i'm working on a cantt chart to show the task duration

    start date is column H
    days needed is column I
    end date is column J

    so when start date column (H) and number of days needed column (I) are inserted
    then the end date column (J) will automatically be calculated based on a this formula =IF(NOT(ISBLANK(I7)),H7+I7-1, "")
    and when the two dates are provided, the calendar chart on the side will be filled with blue color to make the duration dates visually clear based on conditional formatting :
    {
    apply to range:
    L6:CQ110,CR7:CR15,CT7:CT15,CV7:CV15,CU9:CU15,CS10:CS15,CR17:CV110

    Format rules
    Custom formula is:
    =AND(L$4>=$H6,L$4<=$J6)

    formatting style
    fille color:blue
    }

    but if there is no dates inserted yet, the chart area is also filled but instead of certain dates duration, the blue fill is in all row cells.

    I tried isblank but since the column J has formula it won't respond

    so how can i make the area of chart (L6:CQ110,CR7:CR15,CT7:CT15,CV7:CV15,CU9:CU15,CS10:CS15,CR17:CV110)
    be either filled only when the end date column has text in it
    or how to prevent it from applying the conditional formatting when the end date column has no text on it

    i'm really stuck here

    thank you in advance

    • Hi Nour,

      please consider sharing an editable copy of your spreadsheet with us: support@apps4gs.com

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.
      If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      I will look into your task and try to help.

      • done, i shared it with the provided link

      • sorry i meant with the provided email above

        • Thank you for sharing the file, Nour.

          Yet I can't reproduce the rows filled with blue only when the date is missing. Can you please double check the rows/formulas (remove the dates and make other necessary adjustments) so I could see the behavior right away?

  7. Hello,

    Thank you for your explanation.

    Could I please get some advice on how to construct the following as a formula: C2 goes green if C3:C13 are all green.

    I've tried various ways and none have worked. Thanks for your help!

  8. Hi! Super useful article! I am in need of some help with a conditional formatting formula.

    I have a set of data, one column shows a submitted on date (dd-month-yyyy format), and another column shows a status. There are 4 statuses, one of which is 'data sent'. I'd like to highlight rows where the status column is set to 'Data Sent' and the date in the Submitted on column is more than a month ago from the current date. Is that possible? I've been trying this, but it's not working. =AND($G2="DATA SENT"),C2=DATE(today(),-1)

    • Hi Hannah,

      Thank you for your feedback.

      If you put your formula to any cell, it'll hint on the problem.

      Try this formula instead:
      =AND($G2="DATA SENT",DATEDIF($C2,TODAY(),"M")>=1)

  9. Hi Natalia,
    I'm so sorry to trouble you, but I'm stuck. I have shared a sample sheet with you. I am trying to highlight on sheet 1 the first week that the withdrawal on sheet 2 goes above £1000. Is that possible?
    Many thanks.

  10. Hi Natalia,

    I would like to colour my cells based true or false result in an if statement, but without taking into account the value if true or value if false. For example:

    Column A Column B Column C
    Row 1 1 a =if(A1 = 1, B1, "Bad") Since A1 = 1, C1 = B1 = a.
    Row 2 1 b =if(A2 = 1, B2, "Bad") Since A2 = 1, C2 = B2 = b.
    Row 3 1 b =if(A3 = 1, B3, "Bad") Since A3 = 1, C3 = Bad.

    I would like to colour C1 and C2 based on the TRUE result of the correspondent if statements, without actually using the info "a" or "b".

    Is this possible? If so, could you tell me how to do it?

    Many thanks,
    Joana

    • Just to correct and clarify the example dataset:

      _________Column A______ Column B__________Column C (formula) ______Column C (result)
      Row 1______1_______________a_______________ =if(A1 = 1, B1, "Bad")___________a
      Row 2______1_______________b_______________ =if(A2 = 1, B2, "Bad") __________ b
      Row 3______2_______________b_______________ =if(A3 = 1, B3, "Bad") __________Bad

      • Hello Joana,

        If I understand your task correctly, you need to use the following custom formula in conditional formatting:
        =AND($B1=$C1,ISBLANK($C1)=False)

        As a range to color, select the entire column C. This formula will also ignore blank rows.

  11. I want to highlight the cell for following query = "when duplicates found in column a & column b is not blank"

    • Hello Rahul,

      I'm really sorry, I somehow missed your comment.
      If you still need help with your task, please describe it in detail since the current description is not clear.

  12. How to apply conditional formatting in a row if it has a sentence every row and you wanted to color it yellow if one row has this two names in a sentence?

  13. I have a list of order statuses in Column A - including "Ready to Complete" and "Incomplete" and numerical data in Column L.

    I need a conditional formatting formula for:

    If the status in Column A is "Ready to Complete" and the cell in Column L is blank than the blank cell turns blue

    I've been able to do one or the other, but not both. I tried =$A6 = "Ready to Complete" - but it would trigger for Incomplete and not Ready to complete.

  14. Hi I'm trying to make names that come on the spreadsheet go bold if there is more than one, the names come onto the spreadsheet via form responses, so what I want to do is the moment 2 responses was submitted with the same username both names must go bold

  15. Hello,
    I am trying to use conditional formatting to highlight a whole column if there is an "X" in it.

    The cells may or may not contain an "X" based if the Day & hour from another sheet match.

    I've tried various text formulas and I have not been able to get any to work. I've also tried =REGEXMATCH.

    The closest I have been able to get is using the =OR formula but it stops highlighting the column once it finds the "X"

    Feel like if sheets had the option to uncheck "stop if true" then I wouldn't have this issue.

    I shared the sample doc i made called "cond form stops when true"

    Thanks for the great info, crossing my fingers you can help with this one

    • Hello Tom,

      The formula you need for your conditional formatting is:
      =COUNTIF($D$4:$D$23,"X")>0

      I've edited 4 conditional formatting rules in your spreadsheet: for columns D-G. Just change the rest of the rules accordingly and other columns will be coloured correctly as well :)

      • Wow !

        Thanks for the quick response! I think that was one of the first I tried but I must not have had the absolute references.

        Thank you!!

  16. Hello, thanks for a great article, and especially for YEARS of replying to the questions people have in the comments section!

    I have a Google Sheet that I use to track stock prices. I want two columns - one to track the %-value of a stock's price change, and the other to track the $-value of a stock's price change. I have set up conditional formatting for the % cells to go from +3% (green) to 0% (white) to -3% (red). I want to transfer that conditional formatting to the $ change cells, instead of setting the range directly for those cells. (A $10 change matters a lot when the stock is priced at $50, but it barely matters at all if the stock is priced at $3,000).

    In the above examples, you taught how to do this with single color rules, but I cannot figure out how to do this with a color scale, or if it is even possible.

    • Hello Mike,

      For us to be able to help you better, please consider sharing a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: 1) your example data 2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. You can replace any important information with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

      We'll look into your task. Thank you.

      • Thank you for checking in on this. I made a sheet with just the relevant cells, and formatted the cells for % change. The cells for $ change I did not do any formatting. I published the google sheet to the web and emailed you a link to it :)

  17. how to highlighted the highest value cell in a multi range (A1:N40)?
    Tks in advance

  18. Hello,
    Column H has numbers, Column I has numbers. I wish to have a formula that tells me if the number in Column I is greater or less Column H. What would I be best to do?

    • Hello!
      If I understand your task correctly, the formula below to solve your task

      =if(I1>=H1,"More","Less")

      After that, you can copy this formula down along the column.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)