How to use a formula with conditional formatting

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.

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.

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.

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.

For example, we can update the formula to also peek if the value is odd.