Level 4 · Highlight rules, one TRUE or FALSE per row

Conditional formatting colours a cell when a test on it is true. Behind every rule, built-in or custom, Excel evaluates a TRUE/FALSE for each cell. That’s useful to know, because it means you can write the same test in a column, check it row by row, count it, and sort or filter by it. This level does exactly that with Anna’s three rules.

Highlight Cells Rules in Excel

Select the line totals (E2:E38), then Home → Conditional Formatting → Highlight Cells Rules → Greater Than…, type 150 (or click K1 so the rule follows the input cell) and pick Green Fill with Dark Green Text. Repeat with Less Than…, 70, Light Red Fill. The same menu has Between, Equal To, Text that Contains, A Date Occurring and Duplicate Values. Google Sheets: Format → Conditional formatting, “Format cells if… Greater than”.

The written-out version of the two rules:

F2   =E2>$K$1
G2   =E2<$K$2

Row 4, the Bramble Percale Quilt at 417.00, is TRUE in F: green. Row 6, a Delancey Brass Vase at 19.99, is TRUE in G: red. Filled down, 8 lines come out green and 19 red. Watch the edges: four lines sit at 70.99 (rows 3, 17, 20 and 27). They are not under 70, so they get no colour. “Greater Than” and “Less Than” are strict: a line of exactly 150.00 would stay white. If Anna means “150 or more”, the rule is >=.

Top/Bottom and Above Average

Conditional Formatting → Top/Bottom Rules has Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average and Below Average. Above Average compares each cell with the average of the whole selection:

H2   =E2>AVERAGE($E$2:$E$38)

The average of the 37 lines is 111.34, so 9 lines are above it. The range is locked with $ on both ends: every row must compare with the same average. Notice how few lines beat the average: a handful of big lines (629.94, 417.00) pull it up. That’s normal for order values, and a reason to look at the median too. A Top 10% rule ranks the items and colours the largest tenth of them by count; it does not mean “lines above 90% of the maximum”.

Data bars, colour scales and icon sets

These don’t test; they draw.

• Data Bars put a bar inside each cell, longest for the largest value. Good for quantities at a glance. By default the shortest bar is the minimum, so check that a small value doesn’t look like zero.
• Color Scales shade from one colour to another, low to high.
• Icon Sets put an arrow or traffic light in each cell. The defaults split the range by percent; for a rating scale, edit the rule (Manage Rules → Edit) and set the type to Number: green when ≥ 5, yellow when ≥ 3, red otherwise. Written out, that is =IFS(E2>=5,"green",E2>=3,"yellow",TRUE,"red").

Manage Rules shows every rule on the sheet, the range it applies to and the order they run in. When two rules colour the same cell, the one higher in the list wins; tick Stop If True to end the checks there.

Common mistakes

• Unlocked thresholds or ranges in a written-out rule: AVERAGE(E2:E38) filled down slides with the row.
• Strict versus inclusive edges: “over 150” is >, “150 or more” is >=.
• Typing the threshold into the rule instead of pointing at a cell, then forgetting to update one of three rules when it changes.
• Formatting text that looks like numbers: "120" stored as text fails >150 and <70 both ways, so it never gets a colour.