What is conditional formatting and how would you use it effectively?

Conditional formatting applies formatting based on a rule, so the formatting updates automatically as values change.

The built-in types: highlight rules (greater than, between, text contains, duplicate values), top and bottom rules, data bars, colour scales, and icon sets.

Formula-based rules are where the real power is. Under "Use a formula to determine which cells to format", the formula is written for the top-left cell of the selection and applied relatively to the rest. Two patterns cover most needs:

• Highlight an entire row based on one column: select the whole range and use =$E2="Overdue". The column is locked with a dollar sign so every cell in the row tests column E; the row is not, so it moves down.
• Compare against another cell: =B2>$C$2 to flag anything above a threshold held in one place.

Using it effectively:

• Highlight exceptions, not everything. If most of the sheet is coloured, nothing stands out and the formatting has failed.
• Do not rely on colour alone — around one in twelve men has some colour vision deficiency. Pair colour with an icon or text.
• Keep rules few and manage them centrally through Manage Rules; overlapping rules applied ad hoc become impossible to reason about, and a large number of them slows the workbook noticeably.