Abstract
Transcript
In this video, we'll look at how to use a formula to apply conditional formatting. Formulas allow you to make powerful and flexible conditional formatting rules that highlight just the data you want.
Let's take a look.
Excel provides a large number of conditional formatting presets, but there are many situations where you'll want to use a formula to trigger a rule.
The main requirement is that the formula return either TRUE or FALSE. When a formula returns TRUE, the rule is triggered and the formatting is applied.
To illustrate, let's build a conditional formatting rule to flag the odd numbers in this set of random values.
There is no preset for this, so we'll need to use a formula.
The easiest way to test for odd numbers is to use a special function called ISODD. ISODD takes just one argument, a number.
If I supply a reference to a cell that contains an odd number, the formula returns TRUE. If the number is even, the formula returns FALSE.
This will work perfectly for conditional formatting.
Start off by selecting the cells you want to format and then select New Rule from the Conditional Formatting menu on the home tab of the ribbon.
Then, to use a formula, select the last option: "Use a formula to determine which cells to format"
Now we can enter a formula. As always, the formula must begin with an equal sign.
For the reference, look at the active cell in your selection. This is the address you'll want to use in the formula.
When we create the rule, Excel will adjust all references to be relative to the active cell.
In this case, we want to use B4.
For the format, I'll use an orange fill and finish the rule.
As you can see that all odd numbers are now highlighted. If I change an odd number to an even number, we'll see it highlighted as well.
Once you have a formula, you can revise it as you like.
For example, to flag all even numbers, I can just edit the rule and change the formula to use the ISEVEN function.
Now the formatting is applied to all even numbers.
