Day 11: Conditional Formatting

Conditional formatting automatically applies visual formatting (colour fill, font colour, borders, icons, bars) to cells based on their value or a formula condition. Main uses: highlight cells above or below a threshold, apply a colour scale to show relative magnitude across a range, use data bars as in-cell bar charts, flag duplicate values, identify errors, and use icon sets (arrows, traffic lights, stars) for status indicators.

Select the column → Home → Conditional Formatting → Top/Bottom Rules → Top 10 Items. Change the number to however many you want and choose the formatting. For "top 10%" instead of top 10 items, choose "Top 10%". This dynamically adjusts as data changes — if you add new rows and refresh, the top 10 designation updates automatically.

Select the range to format → Conditional Formatting → New Rule → "Use a formula to determine which cells to format". Enter a formula that evaluates to TRUE or FALSE. Example: to highlight an entire row where the Region column (B) is "North" — select A2:F100 → New Rule → formula: =$B2="North". The $ before B locks the column but not the row, so the condition checks column B for every row correctly.

A colour scale applies a gradient of colours across a range based on relative values — typically green for high, red for low, yellow for middle (or any colours you choose). Appropriate when: the relative comparison across many values is what matters (e.g. showing which months have highest/lowest sales across a table). Not appropriate when: the absolute value matters more than the relative one, or when printing in black and white. Apply via Conditional Formatting → Colour Scales.

Conditional Formatting → Manage Rules shows all rules applied to the selected range. Rules are evaluated from top to bottom — the first rule that is true is applied, and lower rules are skipped for that cell (unless "Stop If True" is unchecked). Reorder rules by using the up/down arrows. Common issue: a broad rule at the top overrides a more specific rule below it — always put more specific rules at the top.