Conditional Formatting Formula: Write Rules That Work
· 9 min read
A conditional formatting formula is a formula that returns TRUE or FALSE. The spreadsheet works it out once for every cell in the range you selected, and formats the cells where it returns TRUE. You write it as if it were in the top-left cell of that range, and the references shift for every other cell exactly as they would if you filled the formula across and down.
So =$C2<$D2, applied to A2:E9, means "highlight the row when the stock in C is below the reorder level in D". Everything about writing good rules comes down to two questions: does the formula return TRUE for the right rows, and are the $ signs in the right places?
The blog cannot show colours, so this guide does what analysts do when a rule misbehaves: it puts each rule formula in a helper column next to the data. Wherever a helper cell shows TRUE, the rule would format that cell or row.
The example data
Eight products in a kitchen stock list, with a reorder level for each and a minimum stock limit in J1.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Code | Product | Stock | Reorder at | Below reorder | AI's rule | Under limit | Both | Stock limit | 20 |
| 2 | K-101 | Paper cups | 140 | 100 | FALSE | FALSE | FALSE | FALSE | ||
| 3 | K-102 | Coffee beans | 18 | 25 | TRUE | FALSE | TRUE | TRUE | ||
| 4 | K-103 | Sugar sticks | 300 | 150 | FALSE | FALSE | FALSE | FALSE | ||
| 5 | K-104 | Tea bags | 40 | 60 | TRUE | FALSE | FALSE | FALSE | ||
| 6 | K-105 | Hand soap | 12 | 30 | TRUE | FALSE | TRUE | TRUE | ||
| 7 | K-106 | Dish sponges | 75 | 50 | FALSE | FALSE | FALSE | FALSE | ||
| 8 | K-107 | Bin bags | 22 | 20 | FALSE | FALSE | FALSE | FALSE | ||
| 9 | K-108 | Paper towels | 9 | 40 | TRUE | FALSE | TRUE | TRUE |
Step 1: write the rule for the first row
Start with the question for one row. "Is Paper cups' stock below its reorder level?" In row 2 that is:
=$C2<$D2It returns FALSE for Paper cups (140 is not below 100). Fill it down, as in column E, and you get TRUE for Coffee beans, Tea bags, Hand soap and Paper towels. Those four rows are the ones the rule would highlight.
That is the whole method: write the rule in a spare column next to the first row, fill it down, check that TRUE appears exactly where you expect, then copy the formula into the formatting rule. Writing it in a cell first is faster than editing a rule in a small dialog box, and you can see every row's answer at once.
The formula does not need IF. =IF($C2<$D2, TRUE, FALSE) works, but the comparison already returns TRUE or FALSE. Any number also works as a result: 0 counts as FALSE and every other number as TRUE.
Step 2: get the $ signs right
The rule is written for the top-left cell of the range it applies to, then shifted for every other cell. The $ signs decide what shifts.
| Rule | Applied to | What it compares | Result |
|---|---|---|---|
=$C2<$D2 | A2:E9 | Each row's own C and D, for every cell in the row | Whole rows highlighted |
=C2<D2 | C2:C9 | Each stock cell with the cell to its right | Only the stock cell highlighted |
=C2<D2 | A2:E9 | A2 with B2, B2 with C2, and so on | Random-looking cells |
=$C$2<$D$2 | A2:E9 | Row 2 only, for every cell | All or nothing |
Highlight a whole row based on one cell
To highlight the entire row, select A2:E9 (or the whole table) and lock the column but not the row: $C2. Every cell in row 5 then reads C5 and D5, whichever column it is in, so the whole row lights up together. Leave the row number unlocked so that row 6 reads C6 and D6.
The "all or nothing" bug
Column F shows a rule an AI assistant might suggest:
=$C$2<$D$2Locked in both directions, every cell in the range looks at row 2. Paper cups is not below its reorder level, so the rule is FALSE for all eight rows and nothing is highlighted, even though four products need ordering. Change C2 to 50 and the opposite happens: every row turns TRUE. When a rule highlights everything or nothing, look for a row number with a $ in front of it.
Conditional formatting based on another cell
Thresholds belong in a cell, not inside the rule, so anyone can change them without opening the rule editor. Column G compares each stock count with the limit in J1:
=$C2<$J$1Here J1 is locked in both directions, because every row must read the same limit. C2 keeps its row unlocked, because each row checks its own stock. With a limit of 20, the rule is TRUE for Coffee beans (18), Hand soap (12) and Paper towels (9). Type 30 into J1 and Bin bags (22) joins them.
This mix is the most common pattern in real rules: the data reference locked on the column only ($C2), the input reference locked completely ($J$1). Our absolute reference guide explains the four kinds of reference if this is new.
Combine conditions with AND and OR
A rule can use AND, OR, NOT and IF like any formula. Column H flags products that are below their reorder level and under the limit:
=AND($C2<$D2, $C2<$J$1)That gives TRUE for Coffee beans, Hand soap and Paper towels. Tea bags is below its reorder level but has 40 in stock, so the second condition fails. For more on combining conditions, see IF with AND and OR.
Dates and duplicates
Two more rules analysts write all the time, shown on a list of stock counts with an as-of date in G1:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Product | Last counted | Not counted in 30 days | Duplicate code | As-of date | 2025-10-31 |
| 2 | K-101 | Paper cups | 2025-10-20 | FALSE | FALSE | ||
| 3 | K-102 | Coffee beans | 2025-08-31 | TRUE | TRUE | ||
| 4 | K-103 | Sugar sticks | 2025-10-10 | FALSE | FALSE | ||
| 5 | K-104 | Tea bags | 2025-09-25 | TRUE | FALSE | ||
| 6 | K-105 | Hand soap | 2025-10-25 | FALSE | FALSE | ||
| 7 | K-102 | Coffee beans | 2025-08-31 | TRUE | TRUE | ||
| 8 | K-106 | Dish sponges | 2025-09-10 | TRUE | FALSE | ||
| 9 | K-107 | Bin bags | 2025-10-28 | FALSE | FALSE |
Highlight dates older than 30 days
=$C2<$G$1-30With an as-of date of 31 October 2025, anything counted before 1 October is flagged: Coffee beans (both rows), Tea bags and Dish sponges. In a live workbook you would usually write =$C2<TODAY()-30 so the rule moves with the calendar. A fixed date in a cell is better when the sheet has to give the same answer next week, for example in a month-end report.
Highlight duplicates
=COUNTIF($A$2:$A$9, $A2)>1COUNTIF counts how often this row's code appears in the whole column, and the rule is TRUE when it appears more than once. Both K-102 rows are flagged. The column range is fully locked so every row searches the same list. The built-in "duplicate values" option does the same for a single column, but the formula version can highlight the whole row and can be adapted, for example to flag only the second copy with =COUNTIF($A$2:$A2, $A2)>1. COUNTIF in Excel covers duplicates in more detail.
Every other row
For banded rows without converting the data to a table, the usual rule is =MOD(ROW(),2)=0, which is TRUE on even rows.
Where to paste the rule
The menu names differ between spreadsheet apps, but the steps are the same:
- Select the range the rule should apply to, starting from the row you wrote the formula for. For the first sheet, that is A2:E9 (or A2:D9 if the helper columns are not staying).
- Open conditional formatting and create a new rule.
- Choose the rule type that uses a formula. In Excel it is called "Use a formula to determine which cells to format". In Google Sheets, pick "Custom formula is" from the condition list.
- Paste the formula, starting with
=, exactly as it worked in the helper column. - Pick a fill colour or font, and confirm.
Then open the list of existing rules and check the Applies to range. If the range starts on row 1 but the formula was written for row 2, every result shifts by a row, and the highlight lands one row above the data you meant. That off-by-one is the most common reason a correct-looking rule highlights the wrong rows.
Common mistakes
The formula was written for a different starting cell. The rule must match the top-left cell of the Applies to range. Writing =$C2<$D2 and applying it to A1:E9 shifts every row by one.
Locking the row. $C$2 or C$2 in a rule makes every row read row 2. If everything or nothing is highlighted, check the row numbers first.
Clicking cells while typing the rule. In the rule dialog, clicking a cell inserts it with full $ signs, such as $C$2. Edit the reference afterwards, or type it.
Text where a number was expected. A stock count stored as text never compares as smaller than a number, so the rule stays FALSE. If one row will not highlight, check it with ISNUMBER in a helper cell.
Rules that fight each other. When two rules apply to the same cells, the one higher in the list wins for any format they both set. Order the rules from most to least important.
The practice page has drills where you write TRUE/FALSE rule columns like these and check an AI's rule that flags the wrong rows.
FAQ
How do I use a formula in conditional formatting?
Select the range, create a new conditional formatting rule, choose the option that uses a formula, and enter a formula that returns TRUE for the cells to format, written for the top-left cell of the range.
How do I highlight a whole row based on one cell?
Select the full rows of the table and lock the column of the cell you test, but not the row: =$C2<$D2. Every cell in the row then reads the same column.
How do I base conditional formatting on another cell?
Point the rule at that cell and lock it completely, for example =$C2<$J$1. Changing J1 then updates every highlight.
Why is my conditional formatting highlighting the wrong rows?
Either the row number is locked with $, or the Applies to range starts on a different row than the one the formula was written for.
How do I highlight dates before today?
Use =$C2<TODAY() for any past date, or =$C2<TODAY()-30 for dates more than 30 days old.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- A TRUE/FALSE rule column for refunds
- The late-delivery rule flags on-time parcelsAudit
- Orders the account team handles