Video use formulas to apply conditional formatting

Overview Transcript



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

Want more?

Use conditional formatting

Manage conditional formatting rule precedence

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.