Conditional formatting based on a different cell

In this video, we'll look at how to apply conditional formatting to one cell based on values in another, using a formula.

Let's take a look.

The easiest way to apply conditional formatting is to apply rules directly to the cells you want to format. For example, if I want to highlight the average test scores in column H that are below 80, I can just select the cells and apply a Conditional Formatting rule with  that logic.

But what if you want to highlight the names in column B when the average is below 80 in column H.

In that case, you'll need to apply conditional formatting with a formula.

Start by selecting the cells you want to format.

Next, select "New Rule" from the Conditional Formatting menu on the home tab of the ribbon, and select the option to use a formula.

For the formula itself, start with an equal sign. Then add the logical test you need.

In this case, we can use H6 < 80. This will return true when the average is less than 80 and trigger the rule.

Note that H6 corresponds to the active cell in this selection, which is B6.

I don't need to lock the column in this case, because the relative position of names in column B to the averages in column H won't be changing.

When I save the rule, names where the average in column H is less than 80 are highlighted

You can see that the highlighted names match the average scores highlighted by the first rule.

Now, if I don't need the first rule, I can simply remove it.

This example is quite simple example, but the approach is the same for more complex rules. First, select the cells you want to format. Then enter a formula that will return TRUE for conditions you're testing.