Highlight an entire row based on one cell's value

Create the rule

A formula rule must return TRUE or FALSE. Excel writes the rule for the active cell, the first one in your selection, and then adjusts it for every other cell the same way a formula adjusts when you copy it. That is why you write row 2 even though the rule covers rows 2 to 9.

1. Select A2:E9. Start the selection in A2 so A2 is the active cell, and leave out the header row.
2. Home > Conditional Formatting > New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Type =$D2="Cancelled" in the box.
5. Click Format, choose a fill (and a strikethrough on the Font tab if you like), then OK twice.

Why $D2 and not D2 or $D$2

$D2 is a mixed reference. The $ locks the column, so every cell in a row looks at column D. The row number has no $, so row 5 checks D5 and row 9 checks D9.

Without the $, =D2 in A2 means "the cell three columns to the right". Excel shifts it for B2 to check E2 and for C2 to check F2, so only part of the row colors, or the wrong part. With $D$2, every cell in every row checks D2, so the whole table is colored or none of it is. The check below is what the rule calculates for row 4.

Text comparisons in rules are not case-sensitive, so cancelled in row 9 matches too.

Highlight the row if a cell contains a word

An equals test needs an exact match. To catch "URGENT - site down" and "Urgent" alike, search inside the cell. SEARCH returns the position of the text, or an error when it is missing, and ISNUMBER turns that into TRUE or FALSE.

SEARCH ignores case. Use FIND instead if case matters. Apply it the same way: select A2:E9 and enter =ISNUMBER(SEARCH("urgent",$C2)) as a new rule.

Numbers and two conditions together

Any formula that returns TRUE or FALSE works. To flag large orders that are still waiting, combine two tests with AND, each locked to its column.

Conditional formatting cannot be counted or filtered by formula, so count with the same logic using COUNTIFS. For this table, =COUNTIFS(D2:D9,"Pending",E2:E9,">=1000") gives 2: SO-5502 and SO-5507.

One color per status, or a status you pick

Add one rule per value, such as green for Shipped, amber for Pending and grey for Cancelled. All of them use the same A2:E9 range. When two rules set the same format, the one higher in Home > Conditional Formatting > Manage Rules wins, and you can reorder them with the arrows there.

To highlight whatever status someone chooses, point the rule at an input cell and lock it completely: =$D2=$G$2. Add a drop-down to G2 with Data > Data Validation > List so people cannot misspell it.

Highlight rows with missing information

COUNTBLANK across the row finds incomplete records. Lock the columns with $ and leave the row free, just like the single-cell rules.

Only include columns that must be filled. A2:E2 would also flag Copper Kettle, whose Notes cell is empty on purpose. Here =COUNTBLANK($A2:$B2)+COUNTBLANK($D2:$E2)>0 skips the Notes column.