How to use a formula with conditional formatting

The most powerful and flexible way to apply Conditional Formatting is with a formula. Using a formula allows you to check more conditions, and check more than one condition at the same time.

Let's take a look.

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

Start by selecting all values in the table. Then choose New Rule from the Conditional Formatting menu. Excel displays a window with a number of starting points. We want to use a formula, so select "Use a formula to determine which cells to format".

Now we need to specify the formula and the format. Let's enter a formula that checks to see if a value is greater than 500. For conditional formatting, formulas need to return true to trigger a rule. In this case, we can use the formula =B4 > 500. Don't forget the equal sign.

Note that B4 is a relative address, and the first cell in our selection. Excel will update this formula to use the address of each cell in the selection when the rule is created. As a result, this formula will return true for each cell value > 500 and false for any value of 500 or less.

Now let's add a light yellow fill for the format.

Click OK to create and apply the rule.

To make this rule more useful, let's move the test value out onto the worksheet. First, let's create an input cell and give it the same fill, and a label.

Now, use Manage Rules 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. It won't change as it's evaluated for each cell in the selection.

Click OK to update and apply the rule.

Now the rule is generally more useful and will evaluate any value entered into the input cell.

Because the rule is based on a formula, it can be easily extended to handle conditions not covered by built-in presets. For example, we can update the formula to also check if the value is odd.

As you can see, using formulas allows you to build more powerful and flexible conditional formats.