Level 5 · A "Use a formula" rule across a whole grid

Mei’s grid has twenty lamps and four months. One fixed threshold would light up every month of the Monroe Glass Desk Lamp (41 to 79 units) and never a month of the Fern Rattan Desk Lamp (0 to 5). She wants each lamp compared with itself. No built-in rule does that; a formula rule does.

“Use a formula to determine which cells to format”

In Excel: select B2:E21 with B2 as the active cell, then Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format. Type a formula that would be TRUE for the top-left cell, pick a fill, OK:

=B2>AVERAGE($B2:$E2)

Excel applies the formula to every cell in the selection, shifting the references exactly as if you had copied it there. So the rule is an ordinary formula, and you can check it the same way: in a grid of the same shape. That’s what G2:J21 is: cell H5 tells you whether C5 would be coloured. Google Sheets: Format → Conditional formatting → Custom formula is.

Mixed references decide what moves

The rule has two parts that must move differently:

Part	Filled right (next month)	Filled down (next lamp)	Reference
the month’s units	moves	moves	B2
the lamp’s four months	stays	moves	$B2:$E2

$B2 locks the column and leaves the row free. Check one row by hand: the Sutton Brass Chandelier in row 4 sold 4, 21, 47 and 27 units. Its average is 24.75, so only November (47) and December (27) are TRUE: I4 and J4. The Monroe Glass Desk Lamp in row 3 averages 54, so only December (79) is above.

Whole rows and whole columns

The same idea colours an entire row. Select the order lines A2:F38 and use =$E2>150: the column is locked, so every cell in a row looks at that row’s line total, and the whole row lights up. Lock the row instead to compare down a column: over B2:E21, =B2=MAX(B$2:B$21) marks the best-selling lamp in each month.

Rule over a block	Highlights
=$E2>150	the whole row when column E is over 150
=B2>AVERAGE($B2:$E2)	cells above their own row’s average
=B2=MAX(B$2:B$21)	the top cell in each column

Common mistakes

• Writing the rule for a cell other than the top-left of the selection. The formula is read relative to the active cell; start from row 5 and every highlight lands three rows off.
• No dollar signs: AVERAGE(B2:E2) slides right to C2:F2 and drops a month.
• Too many dollar signs: $B$2:$E$2 compares every lamp with the first one.
• Months with 0 units count in the average. That’s right here (the lamp was on sale and sold nothing), but a blank for “not launched yet” would be ignored by AVERAGE, so know which you have.