Conditional formatting formulas in Excel

First principles. Conditional formatting (CF) applies a format to a cell, whether that's a fill colour, a font colour, a border, only while a rule about it is true. It's live, so when you change the underlying data the paint follows. Data validation stops bad data getting in. CF makes whatever is in the range visible. Outliers, breaches, duplicates all stand out, so a human eye goes straight to the cells that need attention.

Reach it through Home ▸ Conditional Formatting. The menu offers quick built-ins: Highlight Cells Rules → greater-than, between, equal-to, text-that-contains, duplicate values; Top/Bottom Rules; Data Bars; Color Scales; Icon Sets. At the bottom sits New Rule ▸ Use a formula to determine which cells to format. That one is the professional's workhorse, because a formula rule can reference other cells and so highlight a whole row based on one column's value.

How do you highlight a whole row with a formula rule?

The relative-anchor rule. Formula CF stands or falls on this. When you select a range and write a formula rule, Excel evaluates the formula for the top-left cell of the selection. Then it relativises it across every other cell, one by one, the way it would a copied formula. So the $ locks decide which cell each row's rule actually reads. Here's the standard pattern for highlighting an entire row when one column breaches a threshold:

1. Select the whole data body, say A2:D11, with A2 as the active (top-left) cell.
2. New Rule ▸ Use a formula, and enter =$D2>100000.
3. The column D is locked ($D) so every cell in the row looks at column D. The row 2 stays relative, so row 3's rule reads =$D3>100000, row 4's reads =$D4>100000, and so on. The result is that any row whose column-D value exceeds 1,00,000 gets painted across all four columns.

Get the anchoring wrong and the damage is hard to spot. =D2>100000, with no lock, makes each cell test its own column against the threshold, so only column D lights up and the text columns never do. =$D$2>100000, with both parts locked, tests every cell against the single value in D2, so either the whole block is painted or none of it is. This mis-anchored rule is common mistake #2. It's why a colour flag you trust may be pointing at the wrong rows.

Which built-in rules are worth knowing?

Built-in rules worth knowing. Highlight Cells ▸ Duplicate Values paints every value that appears more than once in the selection. It's an instant visual duplicate finder, and unlike Remove Duplicates it's non-destructive, so you can inspect before you delete. Color scales map a two- or three-colour gradient across a range by value, giving you red-yellow-green heatmaps. Data bars draw an in-cell bar proportional to the value.

When not to use color scales and data bars. These gradient tools are for exploration. They give you a quick feel for where the big and small values cluster in a large block. For decision thresholds they do real harm, because a smooth gradient hides the one line that matters. Say the business rule is "flag any margin below 20%". A red-to-green color scale will show the low-margin rows as merely a slightly different shade of the sea around them, and the eye can't find the breach. A single sharp formula rule, =$E2<0.20 with a solid red fill, draws a hard, unmissable line at the number that matters. So use gradients to feel the shape of data, and a discrete formula rule to enforce a threshold. Using a color scale where a threshold rule belongs is common mistake #3.

How do you manage conditional formatting rules?

Managing rules. Conditional Formatting ▸ Manage Rules lists every rule, its range (Applies to) and its precedence. Rules are evaluated from the top down. The Stop If True checkbox halts further rules for a cell once one fires, and you need it when a "highlight breaches" rule must override a color scale beneath it. When CF seems to "not work", open Manage Rules first. The bug is nearly always there, usually an overlapping rule higher in the list or an Applies to range that no longer covers your data.