Conditional formatting is a fantastic way to quickly visualize data in a spreadsheet.

However, you’re free to also create rules with your own custom formulas.

Formulas give you maximum power and flexibility.

A rule to highlight x, y, or z

Sure, you could create a rule for each value, but that’s a lot of trouble.

Create a conditional formatting rule, and pick the Formula option

3.

Enter a formula that returns TRUE or FALSE.

A rule to highlight x, y, or z

Set formatting options and save the rule.

If you struggle with this, see the section onDummy Formulasbelow.

This page hasdetails and a full explanation.

A rule to highlight x, y, or z

First, see to it you started the formula with an equals sign (=).

If you forget this step, Excel will silently convert your entire formula to text, rendering it useless.

This can be a big time-saver when you’re struggling to get cell references working correctly.

A rule to highlight x, y, or z

For a detailed explanation,see this article.

If you are trying to use an array constant, try creating a named range instead.

More CF formula resources

A rule to highlight x, y, or z

Conditional formatting with the OR function

Select the cells to format

Select the formula option

Enter the formula relative to the active cell

Set formatting options

The ISODD function returns TRUE for odd numbers, triggering the rule

Use a formula to highlight rows where state = “TX”

Conditional formatting to highlight dates in the next 30 days

Conditional formatting to compare columns

Highlight missing values with conditional formatting

Conditional formatting to highlight property listings

Dynamic conditional formatting for top values

Using conditional formatting to create a Gantt chart

Conditional formatting search box

Use dummy formulas to check conditional formatting formulas