Conditional highlighting

In this inventory table, two cells are red, even though nobody colored them by hand. The color depends on the number in the cell:

This is how conditional formatting works. The column has a rule: color stock levels below 10 red. The rule doesn't remember a list of cells, it remembers the condition, and it checks it after every change. You can see right away which product needs to be reordered, without reading all the numbers one by one.

A rule has three parts

Every highlighting rule has three parts:

• the cells to check;
• the condition, for example "less than 10" or "greater than 100,000";
• the color for the cells that match.

The color is picked by meaning, like a traffic light:

• red for something that needs action;
• yellow for "take a closer look";
• green for a good result.

One column can have several rules, each with its own color. Try it on the managers' sales: mark the best ones green and the lagging ones red.

Text that contains

Besides comparisons with a number, the same menu has Equal To… for an exact match and Text that Contains… for finding a word inside a cell. The second one is handy for addresses, names and comments: it looks for the letters in a row anywhere in the cell, and it treats uppercase and lowercase letters as the same.

Duplicate values

Eight people signed up for a webinar, but someone sent the form twice. It's easy to miss a repeat in a column of emails by eye. The same menu has a rule that needs neither a number nor text: Duplicate Values… colors everything that appears more than once in the selected cells.

Four emails are colored, that is, two pairs. The rule colors all the copies, not just the second one, so a person decides which sign-up to keep. Uppercase and lowercase letters are the same for it, so Emma's two emails matched. The emails of the two Jakes differ by one letter, and the rule left them alone: it compares whole values.

Bonuses on one sheet

Highlighting works well together with formulas. Below is the whole bonus table:

• the bonus threshold is in B9;
• IF in column C decides who gets a bonus;
• COUNTIF and SUMIF below the table add up the results;
• the rule Equal To… Bonus colors those cells green.

Highlighting changes only how a cell looks, not its value. Formulas don't see color: COUNTIF in B10 counts the word "Bonus", not green cells.