You track a team’s tasks and want the whole row to turn red when the deadline has passed and the task is not done. If you paint cell by cell, the colour is lost when rows are added.
How to use Conditional Formatting Formula step by step
1. Choose the condition
Value above a number, exact or partial text, value between two numbers, empty cell, repeats, weekend, overdue date or alternate rows.
2. Give the first cell
Type the first cell of the column you evaluate. The tool pins the column with $ and leaves the row free, as the rule requires.
3. Paste it into the rule
On Home, Conditional Formatting, New Rule, “Use a formula”. Apply it to the whole range, pick the colour and confirm.
The Step Count: Traditional Friction vs. KorexTools
Painting cells one by one (4 steps)
- Select the cell or the row.
- Apply a fill colour by hand.
- Remember to repaint when the data changes.
- Repeat it with every new row.
With the KorexTools “Conditional Formatting Formula” builder (3 steps)
- You choose the rule’s condition.
- You give the first cell to evaluate.
- You paste the formula into New Rule and apply the colour.
The trick of conditional formatting lies in which part of the reference is pinned
When the rule paints a whole row based on a single column, the reference must carry the $ sign in front of the letter and not in front of the number, for example $B2. That keeps the column on B while the row moves along with each row of the range. If the reference has $ on both parts, every row is compared with the same cell; if it has none, the comparison drifts to the right.
The rule’s formula does not start with a result function such as IF: it only returns true or false, and Excel paints when it is true. That is why an overdue-date alert combines two conditions with AND and compares against TODAY, which recalculates whenever the file opens. Alternate rows come from comparing the row number with its remainder when divided by two.
How to choose the reference
- •Paint the whole row from one column: $B2.
- •Compare with one fixed cell for the whole range: $B$2.
- •Compare each cell with the one to its left: B2 without $.
Rule applied from A2 to D100, with the evaluated value in column B
| Rule | Formula | When it paints |
|---|---|---|
| High value | =$B2>100 | The row whose B passes 100 |
| Alternate rows | =MOD(ROW(),2)=0 | The even-numbered rows |
| Overdue deadline | =AND($C2<TODAY(),$D2<>"Done") | Past date and status other than Done |
Common Mistakes and How to Avoid Them
- ⚠️Pinning the row with $ so the whole sheet compares with a single cell.
- ⚠️Applying the rule to a different range from the one you wrote in the formula.
Frequently Asked Questions
Where do I paste the formula the tool generates?
On Home, Conditional Formatting, New Rule, “Use a formula to determine which cells to format”. Paste it unchanged, apply it to the whole range and pick the fill format.
Why does my rule paint the wrong cells?
Almost always because of the applies-to range: the rule is written with the first row of the range in mind. If you selected from A2 but the formula talks about row 5, it will be out of step.
How many rules can I have at once?
As many as you like. They are evaluated by priority in the rules manager, and you can tick “Stop if true” so that one overrides the ones after it.
What I would do if I were in your shoes
«A good colour rule answers one clear question: if you cannot explain it in a sentence, it is probably surplus.» — DataKorex Engineering Team.