How to work with conditional formatting in Google Sheets

Learn how to use conditional formatting in Google Sheets. This guide covers everything from setting basic formatting rules based on numbers, text, dates, and other cell values, to using custom formulas for cases like deadlines or specific values. By setting up format rules tailored to your unique needs, you’ll save time, reduce errors, and make the most important details stand out.

What is Google Sheets conditional formatting?

Highlighting specific data with color is a great way to emphasize key information — many of us do this all the time.

But if you have data that changes frequently, like values that rise or fall above a certain number, or cells containing particular keywords, manually formatting each cell can be time-consuming and prone to errors.

Wouldn't it be great if such changes to formatting occurred automatically?

Conditional formatting in Google Sheets does just that. It automatically updates cell colors based on rules you set so that important cells stand out immediately.

Let’s explore some examples so you see how easily Google Sheets can apply and adjust formats based on data in real-time.

How to add a conditional formatting rule

Whatever conditional formatting rule you'd like to add in Google Sheets, it all starts the same — with a few clicks:

  1. Select the cells you want to apply the rule to.
  2. Go to Format > Conditional formatting in the Google Sheets menu: Where to find the option in the spreadsheet menu.
  3. A panel will appear on the right side of the screen where you’ll set the details of the rule:
    • edit the range to apply the rule to if needed
    • set up the condition that best fits your needs
    • choose how you want the cells to look like when the rule is met: set a background color or/and font color; make the text bold, italic, underlined or strikethrough.
    All settings you'll need to adjust to do conditional formatting in Google Sheets.
  4. Once everything's set, click Done to save the rule.

You can add more rules by clicking Add another rule on the same pane. And you can easily modify or remove existing rules anytime: Manage formatting rules from one pane.

With this general setup, you’re ready to create specific formatting rules for various use cases. Let’s jump right in 🙂

Set up simple formatting rule for numbers

Conditional formatting in Google Sheets is a fantastic way to visually highlight numerical data based on specific values. So let's suppose you want to color orders greater than $200 in total sales (column F):

  1. Select column F (a column with your numbers).
  2. Go to Format > Conditional formatting.
  3. Choose a condition from the dropdown menu: Greater than or equal to, and enter 200 in the provided field: Pick numeric criterion.
  4. Set the formatting style you prefer. For instance, use bold red text on a yellow fill color to make the cells stand out: How to do conditional formatting by numbers in Google Sheets.

The style will be immediately applied to all cells meeting your condition: they all change colors accordingly.

As an alternative, for a more nuanced visualization, you can use a color scale:

  1. In the Conditional format rules sidebar, switch to the Color scale tab.
  2. Pick one of the existing sets of colors.
  3. Or customize colors for the minimum, maximum and (optionally) midpoint values. For example, set light green for the smallest numbers and dark green for the largest so that cells gradually darken as values increase: Use color scales to format numbers in Google Sheets.

These are simple yet effective ways to draw attention to numerical patterns in your data 🙂

Format cells in Google Sheets by multiple conditions

In some cases, you may want to apply different formats based on different conditions to the cells in the same column. Let's say you need to color orders over $200 in green and orders under $100 in red to draw attention to these specific amounts.

If color scale is not your best bet, you can try creating several formatting rules, each specific to one criterion:

  • Greater than or equal to 200.
  • Less than or equal to 100.
Google Sheets applies conditional formatting by multiple conditions.

All your rules will be applied at the same time. Thus, multiple rules let you create a more tailored view of your data, spot key patterns and take action accordingly.

Google Sheets conditional formatting with custom formulas

While Google Sheets offers standard options to format cells, they may sometimes fall short of covering specific or complex cases where a custom approach is needed. That’s why there's this option to use custom formulas as conditions.

Custom formulas let you build your own rules using standard functions and operators. You'll be able to specify whether a cell meets a particular condition. The result of the formula should be either TRUE or FALSE — if TRUE, the formatting will be applied.

To get started, choose Custom formula is from the drop-down menu in the Conditional format sidebar: Google Sheets lets you create conditional formatting rules using custom formulas.

Let me cover some useful examples so you see how this can work, from highlighting the highest values to marking minimum values while excluding zeros.

Custom formula to highlight max value

To color only the max number in a range, use this custom formula in conditional formatting in Google Sheets:

=F2=MAX($F$2:$F$50)

It checks if each cell in the range is equal to the maximum value and applies formatting accordingly: Conditional formatting for max value in Google Sheets.

Color top N values

If you want to color the top three (or other N) values in a range, create as many formatting rules as you need values, with the following custom formulas for each position:

=F2=LARGE($F$2:$F$50, 2)

you will need to replace 1 with 2, 3, etc. for each next value you'd like to color: Format top 3 values in Google Sheets.

Highlight the lowest value

To highlight the lowest number in a range, use the MIN function in your custom formula:

=F2=MIN($F$2:$F$50) Custom formula to highlight min value.

Exclude zeros for minimum value

If you prefer don't count on zeros as lowest values, use MIN along with FILTER to exclude zeros and color the actual minimum value:

=F2=MIN(FILTER($F$2:$F$50, $F$2:$F$50>0)) Conditional formatting for min value except for zeros.

Color bottom N values

For cases where you need to highlight N lowest numbers, use the SMALL function indicating the position of the number (1st or 2nd small, etc):

=F2=SMALL($F$2:$F$50, 1) Format bottom 3 values in Google Sheets using custom formulas.

As you can see, custom formulas add flexibility to conditional formatting in Google Sheets. I’ll return to them often throughout this article to solve other specific formatting cases!

Conditional formatting if cell contains text

To conditionally format cells with text in Google Sheets, use the related rule: Text contains Conditional formatting rule if cell contains text in Google Sheets.

In addition to just entering the word itself, you can incorporate wildcard characters to enable broader pattern matching for the search:

  • Asterisk (*) matches any number of characters. Thus, d* pattern will find dark chocolates.
  • Question mark (?) matches exactly one character. Thus, d??? pattern will find dark chocolates.
Use wildcards in Google Sheets conditional formatting to see if cell contains text.

Tip. To find the actual asterisks and question marks in your cells, add a tilde (~) right before them. For example, Dark~? will look for cell that actualy contain string Dark?

Custom formulas with REGEXMATCH will also be a good fit here for highly specific searches:

=REGEXMATCH(D2:D50,"Dark") Use custom formulas in conditional formatting to see if Google Sheets cells contain text.

Google Sheets conditional formatting with dates

Managing deadlines or tracking orders is yet another task you can cover with conditional formatting. Google Sheets lets you set up custom rules to highlight dates within specific time frames or to signal when deadlines have passed.

Example 1. Google Sheets conditional formatting if date is within 7 days

Let's highlight those arrival dates that are expected to happen within the next 7 days.

The arrival dates are in column G. If you break down the logic, you will need to check that the date in column G is either today or within the next 7 days. This custom formula will do the trick:

=AND(G2>=TODAY(), G2<=TODAY()+7) Google Sheets conditional formatting for dates within 7 days.

If the starting date is not today though, simply replace TODAY with the DATE function specifying the date of interest:

=AND(G2>=DATE(2024,11,25), G2<=DATE(2024,11,25)+7) Specify the date with the DATE function.

As a result, all dates within the upcoming week from the intended dates will be instantly spotted providing a quick view of the closest order arrivals.

Example 2: Highlight past due dates

Identifying overdue dates is useful for tracking deadlines or incomplete tasks.

To create such a rule, you need a formula that will check for any date that is before the expected end date. Additionally, there may be cells without the actual end dates (blanks) meaning they've never been completed.

Both these criteria fit nicely into one custom formula for your format rule:

=OR(H2>G2,H2="") Format rule for past due dates.

Tip. If the end date is not in the table, do the same as in the example above and specify it using the DATE function:

=OR(H2>DATE(2024,10,26),H2="")

How to format blank cells

Highlighting cells based on text or numbers is helpful, but what about blank cells? Formatting based on blanks can be useful for tracking data completion or signaling about cells that still need attention.

If you only need to check whether a cell is blank, simply select Is empty or Is not empty in the conditional formatting rules.

But if you want to combine this with another condition, you'll need a custom formula. Here are 2 basic formulas for your conditional formatting in Google Sheets:

  • To format blank cells: =""
  • To format non-blank cells: <>""

Let's suppose you have an order status in column G and corresponding details in column H. If the status is Ready to Complete but the details are missing, you want to color that blank cell in column H blue to remind you to fill in the necessary information. 

Here's a custom formula for this:

=AND(G2="Ready to Complete",H2="") Format cells if blank and if other cells contain required text.

This formula checks if the status is Ready to Complete and if column H is empty. When both conditions are met, the formatting will apply.

Use this approach with empty/non-empty cells in other parts of your sheet to highlight missing values or track completion status.

Conditional formatting based on checkboxes in Google Sheets

Checkboxes can help you dynamically change formatting when checked (TRUE) or unchecked (FALSE). This is particularly useful for tracking tasks, to-do lists, and other datasets that rely on a checked/uncheked status.

You'll need custom formulas for this task:

  • To format cells where a checkbox is checked, use a formula with TRUE:

    =G2=TRUE

    make sure to apply it to a column with checkboxes Google Sheets checkboxes formatted by conditional formatting.

  • To format cells where a checkbox is unchecked, use a formula with FALSE:

    =G2=FALSE Conditional formatting rules for empty checkboxes in Google Sheets.

How to use Google Sheets conditional formatting to highlight entire row

Conditional formatting in Google Sheets can also format entire rows in your table. Let's see how it works by the example of checkboxes. I'll highlight all related data in a row whenever a checkbox is checked.

  1. Select the range you want to format. This should be your entire table (except for header) if you want to color the entire row: A2:G50
  2. In your custom formula, you’ll want to create an absolute reference to the column containing the checkbox (e.g., $G) so the formatting rule always refers to this one column. As for the row, let it remain flexible (2) so the rule is applied to each row individually:

    =$G2=TRUE

Google Sheets conditional formatting to highlight whole rows.

This automatically formats entire rows whenever the checkbox in column G is checked.

Note. So remember, 3 things are key for formatting an entire row instead of a single cell:

  • apply the format to the entire table
  • use an absolute reference for the column ($G)
  • and a relative reference for the row (2)

Google Sheets conditional formatting based on another cell

Did you know that you can format certain cells based on values in different cells? This lets you easily change the condition itself (in a cell) without updating the conditional formatting rule directly.

Suppose you want to color rows where Qty per order is less than 50 or more than 100:

  1. Start by entering those values in helper cells (for example, in column I next to your table): Create helper cells that will contain your criteria for conditional formatting.
  2. Open Conditional format rules and set up your whole table (but its header) as a range to format: A2:G50
  3. Then goes the formula for your first rule — the one that will highlight orders with quantities over 100:

    =$E2>=$I$3

    Note. Use absolute references ($) when referencing cells outside the table: $I$3. They will make sure that whatever you do with the table, the formula will still refer to this cell.

    Create conditional formatting in Google Sheets based on another cell value.
  4. Add a second rule to color order with fewer than 50 items. Click Add another rule at the bottom and change your custom formula to:

    =$E2<=$I$2

    Multiple rules for conditional formatting based on multiple other cells in Google Sheets.

This two-rule approach will highlight largest and the smallest orders based on different conditions, allowing quick updates just by changing values in column I.

Conditional formatting based on another sheet

Placing condition cells like I2 and I3 on a separate sheet can help keep the main sheet tidy. However, Google Sheets doesn’t support direct references to other sheets in conditional formatting rules. Named ranges also won't help here. To solve this riddle, use the INDIRECT function to reference cells across sheets.

Here’s how you set up conditional formatting based on another sheet:

  1. Move your condition cells (with the min and max order Qty.) to another sheet. Let’s say they’re now in Sheet2 in cells C2 and C3: Place criteria to another Google sheet.
  2. In your rule settings, select your original table on Sheet1 as a range to format: A2:G50
  3. As for the custom formula, use INDIRECT to reference C2 in Sheet2:

    =$E2<=INDIRECT("Sheet2!C2")

    The INDIRECT function will convert your text string "Sheet2!C2" into a cell reference that your formatting rule can read: Use the INDIRECT function to refer to cells in another sheet.

  4. Similarly, for the upper limit, add another rule:

    =$E2>=INDIRECT("Sheet2!C3") How conditional formatting looks when your criteria cells are on another Google sheet.

With these formulas, you can update conditions on Sheet2, and the conditional formatting on your main sheet will adjust automatically. And no clutter on your main sheet 😉

How to remove conditional formatting in Google Sheets

Removing conditional formatting from your table is just as easy as adding it:

  1. Select any cell within the range where you’ve applied conditional formatting.
  2. Go to Format > Conditional formatting.
  3. On the sidebar that appears, you'll see all the rules applied to the range.
  4. Hover over the rule you want to remove and click the trash icon.
How to remove conditional formatting in Google Sheets.

This will delete that specific rule without affecting other rules or formats.

With all these tips in mind, Google Sheets conditional formatting will play a key role in managing your data. From highlighting values, dates, and text patterns to setting up adaptable rules with custom formulas, you will bring clarity to your data. Now you’re ready to bring your data to life in Google Sheets! 😊

Table of contents

156 comments

  1. My question is how can I conditionally format a ROW of cells for 1) a color based on a value of one cell AND 2) have it do a color scale based on the value of another cell. Example: I have the follow row
    Species Quantity Endangered Severity Organization

    If I want to create a chart that will color all rows pink if the organization is "alpha," all rows blue if the organization is "beta," all rows green if the organization is "Charlie," but have it be a lighter pink, blue or green if the severity is 1, and a darker color if the severity is 5. How can I put that formula in?

    Thank you!

    • Hello Lara,

      I'm afraid you won't be able to put all these conditions into one formula for a single rule.
      For me to be able to help you out, please share a small sample spreadsheet with us (support@4-bits.com) with 2 sheets: your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. Once you share the file, just confirm by replying here.

      Thank you.

  2. Excellent article team.
    The variation I am looking for is how do I highlight more than one text item; i.e. if my range includes ANY of "red", "white" or "blue", I want to highlight (at least the cell, maybe the row).
    (Would your suggestion be case-insensitive?)
    Would your response also work for filtering?

    • Thank you for the feedback, Colin!

      You can create three separate rules for when Text contains: red / white / blue (case-insensitive).

      Or use three custom formulas like this to color entire rows (case-sensitive):
      =REGEXMATCH($B1:$B10,"white")
      In this case, you apply the rule to the entire table but use only the column where words appear in the rule. This case is described here.

      The rules work only for visible cells.

  3. Hello there. Is it possible to have 1 rule "handling" the color of the text and 1 rule handling the fill color of the cell?
    For example, I have in the range A2:Z300, a rule (=$B2="Something") that determines the fill color of the entire row.
    Then I have another rule (=A2="somethingElse"), again in the same range that determines the text color of the cell that contains "somethingElse". To me it seems that the rules are working the same way it works when you have the "stop if true" enabled in excel. Is there a way to disable this?
    Ty.

    • Hello Dimitris,

      As for your first rule, make sure it is applied to the entire range with your data. Then all rows where B is "Something" should be coloured.
      As for the second rule, try picking Text is exactly instead of Custom formula and enter ="somethingElse". Again, make sure to apply it to the entire range. Then all cells with "somethingElse" will change the font colour.

      If you're still having difficulties, please share your file with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.
      Then confirm sharing by replying here and I'll look into it.

      • Good day
        How can i highlight a entire row as soon as col A has text in it instead of a number

        • Good day,

          Please try this formula in your conditional formatting:
          =COUNTIF(A1,"*")

          Make sure to apply the rule to the entire table.

  4. Is it possible to exclude $0 value in colour scale? e.g. e7:p48 contain monthly spends per client, where client has not started spending or does not spend value comes in as zero. I need the colour scale to show lowest monthly spend and highest, excluding zero value - is this possible?

    • Hi Lara,

      the combination of MIN and IF lets you find the lowest value excluding zeros:
      =ARRAYFORMULA(MIN(IF($E$7:$P$48<>0,$E$7:$P$48)))

      for the highest excluding zero, try this one:
      =ARRAYFORMULA(MAX(IF($B$1:$B$9<>0,$B$1:$B$9)))

  5. HI,
    I'm trying to create a search box that will allow users to enter a name and the items that start with the same first letter will be highlighted and all brought to the top of the sheet or open in a new window.

  6. Hi,
    I am currently doing a column-date based formatting in which when a certain task is done passed the deadline, it will highlight the cell red and green if it is done before or exact date of the deadline.
    Example:
    Cell F11(Deadline) = June 30
    Cell G11(Date Done) = July 1(Passed deadline)
    and what I want is for G11 to highlight in red or green(depends on the date) accordingly to the value if F11 without changing the value within the formula on the conditional format rules as to we have different values for the deadline.

    Thanks in advance.

    • Hi Roque,

      If I get your task clear, you need to create two conditional formatting rules:
      1) To color cells with green:
      =AND(ISBLANK($G11)=false,G11<=F11)

      2) To color cells with red:
      =G11>F11

      Make sure to apply the rules to the range with your table.

  7. HELLO,
    I am trying to format a column base on the date of the case is less than today + 7 weeks.
    do you have a solution.

    Thank you.

    • Hello Idir,

      For us to be able to help you better, please share a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.
      Note. We keep that email for file sharing only and don't monitor its Inbox. Once you share the file, just confirm by replying here.
      Thank you.

  8. Hi Natalia!
    I need your help!
    I below is a small illustration of my table. the 1st row "1 2 3 4" runs till 31 and represents the days of the month. So my objective is to highlight entire column based on the current day of the month, and also highlight the name of the person where their value is "1" on current day of month

    1 2 3 4
    TOM 1 0 1 0
    JACK 0 1 1 0
    JILL 0 0 0 0

    =$1:$1=DAY(NOW())
    This works for highlighting the column based on day of month, but I cant get the formula to high the name base on the value in the current day.
    What I have so far:
    =INDIRECT(CHAR(DAY(NOW())+64)&":"&CHAR(DAY(NOW())+64))=1

    Thank you so much!

    • Hi Amos,

      What if you try the following?
      =AND(2:2=1,$1:$1=DAY(NOW()))

      This highlights all cells with "1" based on the day of the month (today).

      • Hi Natalia,
        Thank you so much for your reply. I managed to solve it in a different way.
        I created an extra column (hidden) with the formula =HLOOKUP(DAY(TODAY()),/*my data range*/,6). Then in my 1st column where I have the names, my custom formula for the conditional formatting was to check and highlight the name if that hidden column contains a '1' in that row. But thanks a lot anyways :)

  9. I've shared a sample sheet as I cannot get it to compare

    Just to confirm, I'd like to highlight any entries in column H that apear on sheet IP in column A

    • Thank you for the file, Daniel.

      I wasn't able to make the formula work on your data though it perfectly runs on mine.
      However, I've come up with another formula for you to try in conditional formatting:
      =AND($H3<>"",MATCH($H3,INDIRECT("IPList!A:A"),0))

      Please let me know if it works.

      • Thanks Natalia, that works perfectly

  10. I am having the same issue as Natalie
    I would like to format row H, if the value matches any cell from sheet IP in row A
    This is what I have, but it does not seem to work
    =$H3=INDIRECT("IP!A3:A")

    If I compare to only 1 cell from sheet IP then all is well
    =$H3=INDIRECT("IP!$A$3")

    Is this possible?

    • The formula like you wrote does work for me. I even mention this way in this article.
      Please make sure you apply the rule to the correct range, for example, H2:H20.

      If this doesn't work, please share a small sample spreadsheet with us (support@4-bits.com) with your source data and the rules you use. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep support@4-bits.com for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm in this message thread.
      I'll look into your task and do my best to help.

  11. Hi,
    I would like to ask 1 question. when doing a conditional formating,
    for the format rules, can i use text that contains "a range"
    for example, my data range is D2:D50, and i want to highlight cells that has duplicates/ names that are found in range D60:D70.
    Is that possible??? have been trying to solve this for 2 days
    thank you so much...

  12. I am trying to format an entire row to become a color if a particular cell is not empty. My research indicates that the formula that is supposed to work for this is =$H4034" ", (4034 is the row I'm in at the moment) but it isn't working, possibly because there is already another formula (for how a date needs to look) for the whole spreadsheet...? Because of this, I've tried any number of formulas based on how a date looks when entered, using numbers, using the "/" symbol, using "?" & "*", and I can't get any of them to work. Please help. I've been trying to figure this out for a week, and I'm at my wits end.

    • Alex,

      you should be able to color entire rows where H contains data with this formula for conditional formatting:
      =$H1<>""

      Make sure to apply the rule to your entire table, for example, $A1:$H5000.

  13. I would like to be able to colour a whole row based on a date in one cell. I want the row highlighted if the date in one of the cells is before 3 years ago today.
    Thanks.

    • Suppose your table is in A2:J11, with dates in column J. Here's the formula for your conditional formatting rule:
      =DATEDIF($J2,NOW(),"y")=3

      Pick the color to highlight rows and apply the rule to the range &A2:&J11.

  14. Do you know of a way to make a list from formatted cells? I am trying to create a new list from highlighted cells in a column.

    • Hi JD,

      Unfortunately, you won't be able to do that with the standard Google Sheets features. You need to create a script or look for a special add-on. But I'm afraid we don't have a tool for this task.

  15. Hello,

    I am trying to organize a table of data with cells that contain both numbers and words. Each cell has datum similar to: "25.5 Minutes". I would like to highlight all cells that have more than 120 Minutes, but cant seem to find a way. Any help would be most appreciated!

    • Hello Dillon,

      Assuming your values are in column A, use the following formula in the Conditional formatting rule:
      =(LEFT(A1:A3,FIND(" ",A1:A3)-1)*1)>=120

  16. Excellent article !!

  17. I got lost about "$3" of the "=$E2>=$H$3" I understand the "if E2>= H" but what the "$3" means? Why not simply "=$E2>=$H2"?

    • We compare all cells in column H with one and the same cell - H3. The dollar sign makes sure of that: it doesn't allow the reference to change the number to H4, for example, when comparing with E4.
      You can read more about types of cell references here.

  18. I need to find and remove duplicates from my google sheet. The formula provided does not seem to work. The instructions are confusing. I wish someone could explain it to me like I was a 12-year-old. Very frustrating. =countif(A:A,A1)>1. I need it for column C so =countif(A:A,C1)>1?

    • Hi Rob,

      What formula did you use? If I'm correct, none of them is for finding duplicates in this article.
      Your first formula will check if a value from A1 appears in column A. Your second formula will scan column A for the record from C1.

      Perhaps, this article will answer your question in a way. If not, feel free to check the tutorial for Excel. Most of the formulas will work for Google Sheets as well.

  19. How do I format a cell to change colour if it is still empty after a certain day.
    For example I want it to turn red 28days after 07/01/2019 if the cell is still empty.

    • Supposing a cell to change the color is A2. Try this formula in your conditional formatting:
      =AND(ISBLANK(A2),(TODAY()>(DATE(2019,7,1)+28)))

  20. Hi,

    I have a gantt chart and I'm trying to show coverage in main headers for the gantt chart when there is a different color cell. So for example,

    row D5:BD7 and I want to highlight row 5 when there is a colored cell in rows 6 and 7. So let's assume that row 5 will be orange, and there is orange colored in G6:P6, AM6:AQ6 and there is orange colored I7:M7, R7:Y7, AG7:BC7.

    How do I put in conditional formatting to make this happen in row 5?

    Thanks!

    • Hi,

      I'm sorry, I don't believe it's possible with standard Google Sheets tools. This would require formulas to identify colors in cells.
      I think you'll need to use scripts to solve this task.

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 :)