How to Highlight Cells Based on Multiple Conditions in Google Sheets

To highlight cells based on multiple conditions, you can use either a custom formula or the built-in rules in Google Sheets. The formula is more flexible, whereas the custom rule is more user-friendly. However, the latter may not be useful in all cases.

In this tutorial, I’ll show you how to highlight a column:

Based on multiple conditions in the same column.
Based on multiple conditions in the same column as well as in different columns.

Highlight a Column Based on Multiple Conditions in the Same Column

Suppose you want to highlight cells in a single column based on any of the given conditions. You can either use a custom formula or add multiple conditional formatting rules.

For example, I have the names of a few legendary authors in column A and their works in column B. I want to highlight particular authors. I’ll show you how to perform such conditional formatting with or without a formula.

Sample Data:

I want to highlight the names of two authors, “Agatha Christie” and “Sir Arthur Conan Doyle.” How can I do that?

As mentioned, there are two methods: using a formula and not using a formula.

Option 1: Custom Formula

You can use a custom formula that performs an OR logical test:

=OR($A1="Agatha Christie", $A1= "Sir Arthur Conan Doyle")

When you conditional format a column with multiple conditions, keep the following in mind:

Write the formula for the first cell in the range.
The highlighting area must start from the first cell in the range.
Use a smaller range (closed range) and specify the entire column only if necessary. This can improve performance.

To apply this formula, select the range A1:A15 and click Format > Conditional formatting.

Under Format cells if, select Custom formula is and enter the above formula in the given field.

Custom formula for highlighting a single column with multiple conditions

Select your desired formatting style and click Done.

This way, you can highlight cells based on multiple conditions in Google Sheets.

This formula contains two conditions. If you want to add more, place them comma-separated as follows:

=OR($A1="Agatha Christie", $A1="Sir Arthur Conan Doyle", $A1="Helen Keller")

Option 2: Using Built-in Rules

You can achieve conditional formatting with multiple criteria in the same column without using a formula in Google Sheets. In this case, you simply need to apply multiple rules as follows:

Steps:

Select the range A1:A15.
Go to the Format menu and select Conditional formatting.
Under Format cells if, select Text is exactly and enter “Agatha Christie”.
Click “+ Add another rule” and enter “Sir Arthur Conan Doyle”.
Click Done.

Single Column Multiple Criteria Highlighting with Built-in Rules

This is a simple way to highlight cells based on multiple conditions in Google Sheets.

However, it has a drawback: you cannot match a value in a column and highlight the entire row. This is possible with the formula approach by extending the Apply to range from A1:A15 to A1:Z15.

In short, the custom formula approach is generally more powerful and versatile, so I suggest you stick with that.

Highlight a Column Based on Multiple Conditions in Different Columns

Built-in rules are not suitable for this task, so you must rely on a custom formula.

For example, in column A, you have author names, in column B, their book titles, and in column C, publication dates. The data is structured with a header row (A1:C1) containing field labels.

Highlight a Column Based on Multiple Conditions in Different Columns

To highlight book titles in column B that match specific books and have a specific publication date in column C, follow these steps:

Steps:

Select the column range B2:B16.
Click Format > Conditional formatting.
Under Format cells if, select Custom formula is and enter the following formula:
=AND(OR($B2="Anna Karenina", $B2="The Open Door"), $C2=DATE(2010, 10, 15))
Apply the desired formatting and click Done.

This formula combines the AND and OR functions.

The OR function checks if the book title in B2 matches “Anna Karenina” or “The Open Door”. It returns TRUE if either condition is met.

The AND function returns TRUE if the OR function is TRUE and the publication date in C2 matches October 15, 2010.

The DATE function is used to specify the date in the format DATE(year, month, day).

Note: You can adjust the formula for different criteria and date formats.

This method allows you to highlight cells based on multiple conditions in Google Sheets.

Resources

51 COMMENTS

Paulina J November 11, 2022 At 12:47 am

Hi Prashanth,

Thank you for taking a look.

Here’s an example sheet – I have made it editable. So feel free to play around with it.

– URL removed by Admin –

Currently, the conditional formatting is ok for the green and light pink, but it’s the magenta/darker pink I’m trying to edit.

It’s currently just set to highlight duplicates in col E, but I want to add a condition so that it only highlights the row with the duplicate value in col E and also has a row with a W in col H.

If it’s not doable, then please don’t hesitate to say!

Thanks again!

- Prashanth November 11, 2022 At 1:51 pm
  
  Hi, Paulina J,
  
  Added the formula in your sheet, and the tab name is kvp_test.
  
  You may also like – How to Highlight Conditional Duplicates in Google Sheets.
  
  - Paulina J November 14, 2022 At 3:18 pm
    
    Hi Prashanth!
    
    Amazing! thank you so much for this!
    
    Very grateful for your time and help. All the best.
    
Paulina J November 9, 2022 At 11:57 pm

Hi Prashanth,

Cheers for the post, it’s informative, but I’m still a bit stuck.

Is it possible to add a single formula to look at duplicates in a column, and if there’s a duplicate, highlight the entire row based on whether or not there’s a “W” in Col H?

Happy to make an example if needed.

Cheers.

- Prashanth November 10, 2022 At 12:11 pm
  
  Hi, Paulina J,
  
  Please, feel free to share an example sheet (URL) in your reply.
  
  I won’t publish it.
  
Adam September 1, 2022 At 7:19 pm

Hi,

I have a range of data (A1:D4) containing random numbers.

There is another range in A10:D14, which has another random numbers section.

Question: how can I highlight cells in A1:D4 if numbers match any from A10:D14?

- Prashanth September 2, 2022 At 8:46 am
  
  Hi, Adam,
  
  You can try the below Match formula.
  
  =and(len(A1),match(A1,flatten($A$10:$D$14),0))
  
  Select the range A1:D4 and apply the formula in conditional formatting.
  
  - Adam September 3, 2022 At 3:36 pm
    
    Prashanth,
    
    You are a genius. Thank you so much!
    
John August 7, 2022 At 8:41 am

Hi Prashanth,

Thank you for the great tutorial.

What I am trying to do is if the value of cell A1 is “Promo” and F1 is “Cancelled,” I want the background of A1 to be blue.

I would appreciate it if you have any insight into how I could do this.

- Prashanth August 8, 2022 At 11:27 am
  
  Hi, Johna,
  
  You can use either of the below custom formula rules in cell A1.
  
  Option # 1:
  
  =A1&F1="PromoCancelled"
  
  Option # 2:
  
  =and(A1="Promo",F1="Cancelled")
  
Prashant Kushwah June 14, 2022 At 8:25 pm

Hi Prashanth,

I am a trader in equities.

I am using google sheets in my day-to-day use to track stocks. I stuck up at one point.

My format is something like this below :

Stock — Sector / Industry — Updated date & Time — Mkt. Price — Last Closing — Open — High — Low

I want to highlight the name of the stock, which rises by (let’s say ) 2 % up or down from its open price.

How can I reset my Google sheet to achieve the same?

Regards,

- Prashanth June 15, 2022 At 11:54 am
  
  Hi, Prashant Kushwah,
  
  Please make a sample sheet and include that file’s URL in your reply below.
  
Maria Maria November 30, 2021 At 1:17 am

Hi Prashanth,

I need help. I’m creating a tracker, and it’s a non-numerical value.

Status 1:- My first condition is =$I2="Take Home Assessment" the entire row will be highlighted yellow (this one worked).

But I have another condition. If the candidate failed the Take Home Assessment-

Status 2: =$L2="Unsuccessful" the entire row should turn from yellow to grey.

How can I overwrite the color/highlight of Status 1?

- Prashanth November 30, 2021 At 8:31 am
  
  Hi, Maria Maria,
  
  As you may know, you require to add both the rules. The order of them must be as follows.
  
  Rule 1: =$L2="Unsuccessful"
  Rule 2: =$I2="Take Home Assessment"
  
  Note:- You can drag and drop the rules to change their order.
  
  - Maria Maria November 30, 2021 At 9:34 am
    
    Hi Prashanth,
    
    Glad to hear from you.
    
    Rule 1: =$L2="Unsuccessful" (Grey) – this conditional format works if $I2 is empty.
    
    But if =$I2="Take Home Assessment" is applied, Rule 1 doesn’t work. I can select “Unsuccessful,” but it doesn’t turn the row to Grey 🙁
    
    - Prashanth November 30, 2021 At 11:17 am
      
      Hi, Maria Maria,
      
      The rules work for me.
      
      I think it’s high time to share an example sheet. You can leave the URL below. I won’t publish it.
      
      Thanks.
      
      - Maria Maria November 30, 2021 At 5:30 pm
        
        Hi Prashanth,
        
        Thank you so much for your time and all your help! Thank you.
        
Amy Smith July 15, 2021 At 6:08 am

Hi Prashanth, thank you so much for your great article!

I’m trying to get column ‘E’ to highlight if columns AC/AE/AG/AG all contain ‘X’ but am struggling, what is the best way to address this? And/OR? IF?

- Prashanth July 15, 2021 At 10:22 am
  
  Hi, Amy Smith,
  
  It’s as simple as this.
  
  Select E2:E, then apply the below rule.
  
  =AC2&AE2&AG2="xxx"
  
  - Amy Smith July 16, 2021 At 3:46 am
    
    Thanks for the reply Prashanth,
    
    I tried inputting that in my google sheets conditional formatting for column ‘E’ as a custom formula and it seems to be highlighting at random and everything but the lines that contain all 4 X in AC/AE/AG/AJ.
    
    - Prashanth July 16, 2021 At 9:52 am
      
      Hi, Amy Smith,
      
      Apply to range: E2:E, not (entire column E)
      
      I haven’t considered column AJ. So, here is the modified formula.
      
      =AC2&AE2&AG2&AJ2="xxxx"
      
      If the above doesn’t help, please share a sample Sheet via comment which won’t be published.
      
Davida July 14, 2021 At 6:30 am

I am wondering if you can help. I want to do something similar to this. From A1:B26, I have a list of criteria. Let’s say in Column A, I have a name, and in column B, I have a color, which can change based on other criteria and formulas I have set up.

From A27:B, I have a list of data points. Let’s say I only want to highlight the data points that match A1 with the color listed in B2.

- Prashanth July 14, 2021 At 4:27 pm
  
  Hi, Davida,
  
  Formula rule for the range A27:Z
  
  =$B27=vlookup("Red",{$B$1:$B$26,$A$1:$A$26},2,0)
  
  Lookup criterion “Red” in B1:B26 and return the name from A1:A26. If that name matches in B27:B, it highlights.
  
  If this doesn’t help, you may share a sample of your sheet.
  
Desmond November 22, 2020 At 12:09 pm

Hi, Prashanth,

I tried your formula above for more than 2 criteria. But it doesn’t seem to work. Don’t know where is my mistake. The formula is;
=AND(OR(H3>8.01,H3 and <12.00 highlight RED. If C1=OUT, Value in H3:AM100 <17.3 highlight RED

- Prashanth November 23, 2020 At 10:59 am
  
  Hi, Desmond,
  
  Thanks for sharing your example sheet. I accessed it and added the below two multiple criteria highlight rules.
  
  RED: If the drop-down in cell C1 has the value “IN”:
  
  =and(H3>8.01,H3<12,$C$1="IN")
  
  ORANGE: If the drop-down in cell C1 has the value "OUT".
  
  =and(len(H3),H3<17.3,$C$1="OUT")
  
  When you change "IN" to "OUT", you may be required to wait a few seconds for the cells to highlight as your sheet is slow responsive due to heavy formulas.
  
Rachyl May 28, 2020 At 10:46 pm

Hello,

I have been trying to put my scenario into sample formulas shown above and cannot figure it out.

I have two columns that contain checkboxes – columns E & H. The text is Red at the beginning for any data entry, and there is a conditional formula coloring a row based on Column C (Last Name).

So, when Column E is checked, I would like it to remove the row coloring. Then, when Column H is checked, I want it to change the text to Black.

So, I can get EITHER of these rules individually but not both at the same time. Can anyone help?

- Prashanth May 29, 2020 At 10:25 am
  
  Hi, Rachyl,
  
  Please leave a sample sheet with EDIT access.
  
  Best,
  
Garrett Fahrbach February 14, 2020 At 8:03 am

Hey there,

I’m messing around with a Google Sheet’s conditional formatting and checkboxes. I’m wanting to change the color of a row based on what checkbox is checked (aka ‘cell’=TRUE).

My range is A2:Y1503. I have my entire first row as a header. I’m using a custom formula and here it is:

=$I2=TRUE

I have columns I:N strictly for checkboxes. I want a row to change to another color if I2 and J2 are checked or only J2 is checked.

What’s the correct formatting for the AND/OR?

- Prashanth February 14, 2020 At 1:59 pm
  
  Hi, Garrett Fahrbach,
  
  Use the below AND/OR formula.
  
  =or(and($I2=TRUE,$J2=TRUE),$J2=TRUE)
  
Harry Jameson January 19, 2020 At 9:03 pm

Hello, I was wondering if it would be possible to highlight a row based on two cells in the row when one of the cells let’s say G hits the same or less quantity as J as a reorder point?

- Prashanth January 20, 2020 At 9:24 am
  
  Hi, Harry Jameson,
  
  Range: A1:Z1000
  
  Formula;
  
  =and(lte($G1,$J1),gt(len($G1)*len($J1),0))
  
  Try this, please.
  
Alexk October 24, 2019 At 7:00 pm

Hi,

I have been looking for days to solve a problem with shading and filtering.

I have a large spreadsheet with all sorts of data in it and I have added a help column to allow me to shade alternate group of rows based on a column’s data using the formula =MOD(IF(ROW()=2,0,IF(D2=D1,J1, J1+1)), 2)

The problem is a need to filter the data all the time losing the alternate shading since the formula still counts hidden rows giving me same shade if the group in between is hidden.

Please help to keep alternate shading grouped by column data regardless of filtering.

I prepared a simplified copy of the spreadsheet in the link below.

Note: Link copied then removed by admin.

Thanks in advance!!

- Prashanth October 25, 2019 At 5:22 pm
  
  Hi, Alex,
  
  I have only a workaround for this problem. I don’t know whether it will suit your needs.
  
  See column K a new helper column on your ‘Product’ tab. Using that column as the criterion, I have filtered the data to a new tab named ‘Product Copy’ and there applied the formatting.
  
  Filter Formula: ={Product!A1:I1;filter(Product!A1:I,Product!K1:K=1)}
  
  Use the ‘Product’ tab for your all kind of filtering. But refer to the helper tab ‘Product Copy’ for the highlighting.
  
  Best,
  
- Prashanth October 26, 2019 At 9:38 pm
  
  Now I have a solution to this problem! I will post it soon.
  
- Prashanth October 28, 2019 At 6:07 pm
  
  Hi, Alexk,
  
  If you are still looking for the solution, try this one.
  
  Alternating Colors for Groups and Filter Issue in Google Sheets.
  
  Best,
  
  - Alexk October 30, 2019 At 8:38 pm
    
    Dear Prashanth,
    
    Although it requires 2 helper columns and 1 helper sheet, it is exactly the result I was looking for! Hopefully, in the future such operations will become integrated into the sheet functionalities as more and more data is needed to be filtered and processed by humans.
    
    I thank you so very much for the solution you found!!
    
    Best Regards,
    
    - Prashanth October 31, 2019 At 8:52 am
      
      Thanks for your feedback!
      
Leah September 4, 2019 At 11:10 pm

Hi,

Hoping for some help with conditional formatting of checkboxes.

Column B is a checkbox for Priority tasks and will highlight the column C task if it is checked. Column D is a Completed checkbox that I am attempting to “strikethrough” column C task if it is checked.

Basically, two different formulas in two different columns affecting a single third column.

I can get them to work independently but if I have one of the checkboxes in column B checked (which causes column corresponding task in column C to be highlighted), then click on the checkbox in column D to indicate that the task is completed, it won’t produce the strikethrough that I need.

Help, please!

- Prashanth September 6, 2019 At 9:18 am
  
  Hi, Leah,
  
  I guess I have correctly interpreted your query.
  
  You have tick boxes in B2:B and D2:D, and the tasks to highlight are in C2:C, right?
  
  You want to highlight the tasks in C2:C in two ways (using tick boxes in two columns).
  
  Conditional Format Rule 1 for the Range C2:C.
  
  =AND(B2=TRUE,D2=TRUE)
  
  Formatting Style: Fill Color & Strike-through.
  
  Conditional Format Rule 2 for the Range C2:C.
  
  =B2=TRUE
  
  Formatting Style: Fill Color.
  
  Please make sure that the highlighting rules are positioned as per my order above. If not it won’t work.
  
Allan August 15, 2019 At 7:38 pm

Can you please help me with my issue?

I trying to do an alternate color formatting. I’ve added a new column (Column D) with values of 1 and 0 (0 change to the grey background while 1 retains a white background).

So far that works, but I need another formatting conditions wherein if Column C is Yes, the entire row will be bolded and change to blue color?

How can I achieve that? Is there a way I can send you screenshot/snapshot so you can better see the issue.

Thanks.

- Allan August 15, 2019 At 8:52 pm
  
  Forgot to mention that the alternating colors apply in every 2 rows.
  
- Prashanth August 16, 2019 At 10:51 am
  
  Hi, Allen,
  
  Instead of sharing a screenshot, please share a demo Sheet. Make an example Sheet and use the Green “Share” button. There click on “Get shareable link”. Then change the settings to “Anyone with the link can edit”. Copy the link and share it in the comment.
  
  I won’t publish the link.
  
  - Allan August 16, 2019 At 7:43 pm
    
    Sure.
    
    - Prashanth August 17, 2019 At 9:38 am
      
      Hi, Allan,
      
      I have added the conditional formatting rules in your Sheet.
      
      Since the Sheet only contains mockup data, I have made a copy of the same and here it is. I hope this can be useful for other users with a similar query.
      
      https://docs.google.com/spreadsheets/d/1NkOG5Xqi_uC2BxTYuOeOn3hoG2171cNXOr5GK2j5-OM/copy
      
      - Allan August 17, 2019 At 6:52 pm
        
        You are a genius. Thank you so much for your help.
        
        
        Prashanth August 18, 2019 At 8:40 am
        
        Welcome!
        
Tasha Redden July 17, 2019 At 10:41 am

Ok. Scenario – I have a Spreadsheet in Google Drive that I have the conditional format in to change the entire row Blue if it contains a 1 in the Column K. (=$K2="1") – Range is A2:CJ3077 if it matters.

I want to add another layer that says if the number in Column CJ is above 90 then change the color to Red.

Is this possible?

- Prashanth July 17, 2019 At 7:24 pm
  
  Hi, Tasha,
  
  I could understand that the data range is A2:CJ. Follow the below formatting rules.
  
  Red Color (it must the the first rule):
  
  First select this range. In the conditional formatting use the custom formula =and($K2=1,$CJ2>=90). I guess that column A contains the number 1, not the string “1”. If it’s string change the formula as =and($K2="1",$CJ2>=90)
  
  Blue Color (second rule):
  
  Then add one more rule. In that, you can use another custom formula =$K2=1 or =$K2="1"
  
  Best,
  
vaibhav srivastava September 19, 2018 At 12:53 am

It solved my problem! Thanks.

Denise May 18, 2018 At 7:18 pm

Is it possible to highlight cells in column A based on criteria in both column B & C?

- Prashanth May 18, 2018 At 9:41 pm
  
  Hi Denise,
  
  A Custom formula like the one below will highlight all the cells in column A if the values in B=25 and C=30.
  
  =and(B1:B=25,C1:C=30)
  
  You can play around with this formula.