2.8 Formula-based Conditional Formatting
For rules that depend on another column or a whole row, use a formula. The formula must return TRUE/FALSE and is written for the top-left cell of the selection.
Steps in Excel – highlight the entire row of cancelled orders
- Select A2:O5000 (whole data, without header).
- Home › Styles › Conditional Formatting › New Rule… › Use a formula to determine which cells to format.
- Formula:
=$N2="Cancelled"(column fixed with$, row relative). - Format… › Fill light grey, font strikethrough › OK › OK.
More formula rules on our data:
| Goal | Formula (top-left cell A2) |
|---|---|
| Late Pune deliveries | =AND($D2="Pune",$M2>15) |
| Weekend orders | =WEEKDAY($B2,2)>5 |
| Orders in Diwali week (fictional dates) | =AND($B2>=DATE(2026,11,6),$B2<=DATE(2026,11,12)) |
| Every alternate row (zebra) | =MOD(ROW(),2)=0 |
| Value above its city's average | =$K2>AVERAGEIF($D:$D,$D2,$K:$K) |
| Row matches a selected city in cell R1 | =$D2=$R$1 |
Ravindra Bagale's Tip
Formula rule madhe sagalyat common chuk mhanje $ chukicha lavne. Poora row rangavaycha asel tar column la $ lava ($N2), row la nahi. $N$2 lihila tar sagli rows fakt row 2 var adharit hotat aani sagla sheet ekach rangat yeto. Formula nehmi select kelelya range chya pahilya row sathi liha.
Practice task
Highlight whole rows where Platform is Amazon Now and Amount > ₹500. Then make a "selected city" cell R1 with a drop-down and highlight rows of that city only.
Thodkyaat sangaycha tar (quick recap)
AutoFill aani Series ne pattern bhara, Flash Fill one-time split/combine sathi, data validation aani drop-downs ne chukiche values thamba, dependent drop-down sathi INDIRECT kiwa FILTER, aani conditional formatting ne mahatvache aakde lagech dakhva – formula rules madhe $ lakshat theva. Aata pudhe jaauya – formulas chya jagat!