How to Apply Conditional Formatting with Multiple Conditions in Google Sheets

Video google sheet conditional formatting and

Have you ever wondered how to apply conditional formatting with multiple conditions in Google Sheets? Look no further! In this article, we will show you step-by-step how to do just that. So let’s get started!

Conditional Formatting Using Multiple Conditions in a Custom Formula

In Google Sheets, you can write a single custom formula that specifies multiple conditions. Let’s take a look at an example where we will use this method to apply conditional formatting to cells that satisfy two conditions:

Paid = “Yes”
Amount Due: >30 (amount greater than 30)

To apply conditional formatting to cells that meet these two conditions, follow these simple steps:

Select the cell or range of cells to which you want to apply conditional formatting. In our example, we will select all cells containing data, except for the header row.
Click on “Format” in the “File” menu, followed by “Conditional formatting”. This will open the conditional formatting menu on the right side of the screen.
In the “Conditional format rules” section, expand the “If…” dropdown menu, and click on “Custom formula is”. This will allow you to enter a custom formula for your conditional formatting rule.
Enter the following custom formula:

=AND(Reference_Cell>Value, Reference_Cell=Value)

Let’s break down the custom formula:

AND/OR: The AND function specifies that ALL conditions must be met, while the OR function specifies that ANY of the conditions must be met for the formatting to be applied.
(): Formula references need to be enclosed in parentheses after the function.
Reference_Cell: This is the reference to the cell in the column that the formula will check, starting from the specified row number in your selection. It’s important to note that the row number should start with the same number as your selection. Use the $ symbol before the cell reference if you want to include the entire column.
>: This is the greater than operator, used to compare the Value with the value of the Reference_Cell.
Value: This is the value that the formula will check. Text-based values need to be enclosed in double quotes (""), while numeric values do not require quotes.

In our example, the custom formula would be:

=AND($B2>200, $C2="Yes")

This formula will highlight cells that meet both conditions: a value greater than 200 in column B AND the text value “Yes” in column C.

Apply formatting options. The formatting style section allows you to edit the data in various ways. You can make the text bold, italic, underlined, strikethrough, and change the text and cell color. In our example, we changed the cell background color to green.
Click “Done” to see the results. Once you are done with the formatting, click the “Done” button to apply the conditional formatting.

As shown in our example, the formatting was only applied to cells where the value in column B is greater than 200 and the value in column C is “Yes”.

Conditional Formatting Using Multiple Rules in Google Sheets

Another method of applying conditional formatting with multiple conditions is by using multiple rules. Let’s take a look at an example where we will use multiple conditions to apply formatting to cells where the text value in column C is “No” using a single rule, and then apply a second rule to cells where the text value in column A is “John”.

To apply conditional formatting using multiple rules, follow these steps:

Select the cell or range of cells to which you want to apply the conditional formatting. In our example, we will select all cells containing data, except for the header row.
Click on “Format” in the “File” menu, followed by “Conditional formatting”. This will open the conditional formatting menu on the right side of the screen.
In the “Conditional format rules” section, expand the “If…” dropdown menu, and click on “Custom formula is”. This will allow you to enter a custom formula for your conditional formatting rule.
Enter the following custom formula: =$Cell_Ref=Value.

The formula is broken down as follows:

$Cell_Ref: This is the reference to the cell in the column that the formula will check, starting from the specified row number in your selection. The row number should start with the same number as your selection. Use the $ symbol before the cell reference if you want to include the entire column.
=Value: This is the value that the formula will check. Text-based values need to be enclosed in double quotes (""), while numeric values do not require quotes.

In our example, we want to highlight all cells where the text value is “No” in column C. So our custom formula would be: =$C2="No".

Apply the desired formatting style. Use the formatting style section to edit the data in various ways. You can make the text bold, italic, underlined, strikethrough, and change the text and cell color. In our example, we changed the cell color to red.
Click on “Add another rule”. The formatting has been applied to cells where the value in column C contains the text “No”. Now we need to add another rule.
Enter the new custom formula in the format: =$Cell_Ref=Value.

The new formula will appear as a copy of the first one. In the text box, type the new custom formula in the following format: =$Cell_Ref=Value.

The formula is broken down in the same way as before.

In our example, we want to highlight all cells where the text value is “John” in column A. So our custom formula would be: =$A2="John".

Apply the desired formatting style. Use the formatting style section to edit the data in various ways. You can make the text bold, italic, underlined, strikethrough, and change the text and cell color. In our example, we changed the cell color to green to highlight these data compared to the other rule.
Click “Done” to see the results. Once you are finished with the formatting, click the “Done” button to apply the conditional formatting.

As shown in our example, the formatting from our first rule was applied to cells where the value in column C contains the text “Yes”. We can also see that the formatting from the second rule was applied to cells where the value in column A contains the text “John”.

So there you have it! You now know how to apply conditional formatting with multiple conditions in Google Sheets. Feel free to explore and experiment with these methods to make your spreadsheets more visually appealing and easier to understand.

If you want to learn more about Google Sheets and how to optimize your website, be sure to check out Crawlan.com. It’s a powerful tool that can help you explore and optimize your website. Give it a try today!

This article has been written for the brand BolaMarketing.com. No other branding or links have been included.