Today's Goals

  • Highlight cells that break a threshold
  • Add data bars and color scales
  • Write formula-based rules and use $ correctly
  • Manage rules: view, reorder, clear

Key Concepts

1. The idea in one sentence

Conditional formatting = "when a cell meets a condition, apply a format automatically". Manual colors freeze the moment data changes; conditional colors follow the data — red when over the limit, fading back when fixed. It changes looks only, never data; delete the rules and the numbers are untouched.

2. Three built-in families

  • Highlight Cells Rules: greater than / less than / between / text contains
  • Data Bars: an in-cell bar whose length mirrors the value
  • Color Scales / Icon Sets: green-to-red gradients across a block

Select the range → Home → Conditional Formatting. Two clicks, zero formulas.

3. Formula rules: coloring the whole row

To redden entire rows when column E drops below 10, built-ins are not enough. New Rule → Use a formula:

  1. Select A2:E100, starting at the first data row
  2. Formula: =$E2<10
  3. Pick a fill color

$E2 locks the column but leaves the row free: every row checks its own E value. This is the classic mistake — $E$2 compares the whole block against one cell, so everything turns red at once or nothing does.

4. Managing rules

Conditional Formatting → Manage Rules lists every rule, its priority and its applies-to range. Rules run top-down; "Stop If True" blocks the ones below. Clearing rules (colors) is separate from clearing formats (borders and fills too).

Today's Code

Sample rule formulas — select the range first, then create the rule:

excel
=B2>100               → red when B passes 100
=AND($E2<10,$E2<>"")  → yellow row when E < 10 and not empty
=$F2="Overdue"        → red row when F reads "Overdue"

The <>"" guard in the second formula stops blanks from reading as zero and lighting up rows that were never filled in.

Exercises

  1. Make D-column values over 5000 bold red.
  2. Add data bars to the scores in column B.
  3. Turn the whole row red when column F says "Overdue".

Exercise Solutions

  1. Select D2:D100 → Highlight Cells Rules → Greater Than → 5000; format: red, bold.
  2. Select the B data range → Conditional Formatting → Data Bars; soften the style in Manage Rules if it is loud.
  3. Select A2:F100 → New Rule → formula =$F2="Overdue" → red fill. The first selected row and the formula row must match: both start at 2.

Real-World Scenario

A stock clerk scans 200 rows weekly for below-safety items — slow and error-prone by eye. Three alarm layers: whole-row light red via =$E2<$H$2 (H2 holds the safety line as a parameter); data bars on E for absolute volume; yellow on D via =TODAY()-$D2>7 when the last count is over a week old. Change H2 once and every alarm line moves — a parameter plus conditional formatting turns a static sheet into a monitor panel.

Common Errors and Troubleshooting

Symptom Cause Fix
Rule set but no colors First-row check returns FALSE; rows misaligned Align the selection start with the formula row
Whole block colors at once $E$2 locks both axis Drop the row $: use $E2
Blank rows light up Blanks read as 0 Add <>"" to the formula
Workbook slows down Rules applied to full columns Limit applies-to to real data rows
Colors will not go away Data cleared but rules remain Clear Rules → Selected cells

Extension Exercises

  1. Icon-set arrows on column E with custom thresholds: under 10 red, 10–50 yellow, over 50 green.
  2. Stack three rules — row red, data bars, date yellow — and reorder them in Manage Rules; watch which rule wins when one cell matches several.

Summary

Conditional formatting lets the sheet speak: built-ins in two clicks, formula rules for whole rows — remember "lock the column, free the row" ($E2); guard blanks with <>""; keep applies-to off full columns. Five skills are now in hand — formulas, functions, lookup, pivots, alarms. The last lesson runs the full check.

由在线工具箱(www.vba.net)整理制作