Conditional formatting with one variable input

In this video, we're going to extend conditional formatting rule with a variable, so that you can change behavior without editing the rule.

Let's take a look.

Here we have the table of random numbers. Let's first build a conditional format that uses a formula to highlight cells greater than 500.

As before, select the full set of values, then choose New Rule from the Conditional Formatting menu, then choose the option for formula.

To highlight number greater than 500, we'll need a formula that returns true when a cell value is greater than 500. We can do using:

=B4 > 500

Remember to use the address of the active cell.

For format, I'll use a green fill.

Now all cells with values greater than 500 are highlighted.

But notice that the number 500 is currently hard-coded into the formula. So, if you want to change that number, you'll have to edit the rule.

It would be a lot more convenient to expose that number on the worksheet as a variable.

To do that, I'll first create an input cell and give it a label.

Next, I need to edit the rule and replace the number 500 with the address of the input cell. It's easiest to select 500 and then click directly onto the worksheet.

Notice that Excel uses an absolute address. This is exactly what we want - the address of the input cell won't change as the formula is applied to each cell in the selection.

Once I save the rule, all cells are highlighted, this is because the input cell is blank, which is the same as zero. But once I enter 500, the cells with values over 500 are highlighted again.

Now the rule is generally more useful, since it will use whatever value we enter into the input cell.

For example, we can easily highlight values over 250, values over 700, or any other number that makes sense.

You can use this same approach whenever you want to make a Conditional formatting rule more flexible.