Use formulas

To control more precisely what cells will be formatted, you can use formulas to apply conditional formatting.

In this example, I am going to format the cells in the Product column if the corresponding cell in the In stock column is greater than 300.

I select the cells I want to conditionally format.

When you select a range of cells, the first cell you select is the active cell.

In this example, I selected from B2 through B10, so B2 is the active cell. We’ll need to know that shortly.

Create a new rule, select Use a formula to determine which cells to format. Since B2 is the active cell, I type =E2>300.

Note that in the formula, I used the relative cell reference E2 to make sure the formula adjusts to correctly format the other cells in column B.

I click the Format button and choose how I want to format the cells. I am going to use a blue fill.

Click OK to accept the color; click OK again to apply the format.

And the cells in the Product column, where the corresponding cell in column E is greater than 300, are conditionally formatted.

When I change a value in column E to greater than 300, the conditional formatting in the Product column automatically applies.

You can create multiple rules that apply to the same cells.

In this example, I want different fill colors for different ranges of scores.

I select the cells I want to apply a rule to, create a new rule that uses the rule type Use a formula to determine which cells to format.

I want to format a cell if its value is greater than or equal to 90.

The active cell is B2, so I enter the formula =B2>=90.

And configure the rule to apply a green fill when the formula is true for a cell. The cell that has a value greater than or equal to 90 is filled with green.

I create another rule for the same cells, but this time, I want to format a cell if its value is greater than or equal to 80 and less than 90.

The formula is =AND(B2>=80,B2<90).

And I choose a different fill color.

I create similar rules for 70 and 60. The last rule is for values less than 60.

The cells are now a rainbow of colors, and these are the rules we just created to enable this.

Up next, Manage conditional formatting.