How to Highlight Cells based on multiple Conditions in Google Sheets

0
314
Highlight Cells based on multiple Conditions in Google Sheets

I know the title is bit confusing for you. So let me first explain what I meant by saying Highlight Cells based on multiple Conditions in Google Sheets. Here I meant to say two aspects of Google Sheets Conditional Formatting.

1. 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. Here you can either depend a custom formula or simply add multiple conditional formatting rules in Google Sheets.

For example, I have names of few popular authors in a column, where I want to highlight particular authors. I’ll tell you how to perform such conditional formatting with or without a formula.

2. Highlight a Column Based on Multiple Conditions in Same Column as well as Different Columns.

Here you should use a custom formula. For example, here in one column I have title of few books. I want to highlight particular books in that column based on publication date in another column.

Hope you could understand the above point. So let’s go to the tutorial part.

Steps to Highlight Cells based on multiple Conditions in Google Sheets

1. Highlight a Column Based on Multiple Conditions in the Same Column.

Sample Data:

sample data for single column multiple highlight

Here in this set of data, column A contains names of few of the famous Authors. Here I want to just highlight the names of two authors. For example, here I’m highlighting “Agatha Christie” and “Sir Arthur Conan Doyle”. How to do that?

With Custom Formula:

Here you can depend a custom formula that performs an OR logical test. You can tailor the formula in two ways. Any of the below formula can do this type of conditional formatting.

Formula 1:

=(A1=”Agatha Christie”)+(A1=”Sir Arthur Conan Doyle”)

Formula 2:

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

To apply this custom conditional formatting rules in Google Sheets, first select the range A1:A10 or whatever the range, then go to the menu Format and select Conditional formatting.

custom formula for single column multiple condition highlight

Here apply any of the above formula and choose the colour to fill the cells. This way you can Highlight Cells based on multiple Conditions in Google Sheets. If you want to include more than two conditions, you can simply develop the formula as follows.

=(A1=”Agatha Christie”)+(A1=”Sir Arthur Conan Doyle”)+(A1=”Leo Tolstoy”)

Without Custom Formula:

Same column, multiple criteria conditional formatting is possible without a formula in Google Sheets. Here you just need to apply multiple rules as below. Here I’m highlighting Author’s name in Column A that are matching to “Helen Keller” or “Leo Tolstoy”.

multiple criteria formatting using format rules

First select the range, from the Format menu select Conditional formatting. Now set the first rule as above and click “Add another rule” and add another author’s name.

2. Highlight a Column Based on Multiple Conditions in Same Column as well as Different Columns.

Here is our sample data. The content in this data is not accurate. I just filled name of some random books and fictional publication dates for our example purpose.

Here I want to highlight particular books title in Column B of which the publication date should be on 15/10/2010. The books are “The Open Door” and “Anna Karenina”. The publication dates are in Column C.

multi column dependent highlighting in google sheets

Select the column range B2:B16. This’s important. No need to select column C. Here  I am using the custom formula as below. It’s AND, OR logical test.

=AND(OR(B2=”Anna Karenina”,B2=”The Open Door”),C2=DATE(2010,10,15))

This way you can Highlight Cells based on multiple Conditions in Google Sheets. Hope you enjoyed?

LEAVE A REPLY

Please enter your comment!
Please enter your name here