Conditional Formatting – Multiple Conditions (And) – Excel & Google Sheets

This tutorial will demonstrate how to highlight cells if multiple conditions are met using Conditional Formatting in Excel and Google Sheets.

 

multiple conditions master

 

Conditional Formatting With Multiple Conditions

To highlight cells according to multiple conditions being met, you can use the IF and AND Functions within a conditional formatting rule.

  1. Select the range you want to apply formatting to.
  2. In the Ribbon, select Home > Conditional Formatting > New Rule.

 

multiple conditions menu

  1. Select Use a formula to determine which cells to format, and enter the following formula:

  1. Click on the Format button.

 

multiple conditions formula

 

  1. Set a format. For example, an orange fill color.

 

Conditional formatting based on another cell formatting

 

  1. Click OK, then OK again to return to the Conditional Formatting Rules Manager.

 

multiple conditions rule manager

 

  1. Click Apply to apply the formatting to your selected range and then click Close.

 

multiple conditions master final

 

Every cell in the range selected that has a value greater than 5 and less than 10 will have its background color changed to orange.

Multiple Conditions in Google Sheets

The process to highlight cells based on the value contained in that cell in Google Sheets is similar to the process in Excel.

  1. Highlight the cells you wish to format, and then click on Format > Conditional Formatting.

 

google sheets multiple conditions menu

 

  1. The Apply to Range section will already be filled in.

 

google sheets multiple conditions range

 

  1. From the Format Rules section, select Custom Formula.

 

google sheets multiple conditions formula

 

  1. Select the fill style for the cells that meet the criteria.

 

google sheets conditional formatting greater less than format

 

  1. Click Done to apply the rule.

 

google sheets multiple conditions final