Use Conditional Formatting in Excel Without Hiding the Meaning
On this page
Conditional formatting changes a cell's appearance when a rule is true. It can draw attention to a problem, but colour alone should not carry the meaning. Use labels so the sheet remains understandable in a monochrome printout or to someone who cannot distinguish the colours.
Practice setup: Start in a new workbook with fictional data. Formulas use English function names and commas; your regional settings may require semicolons. Interface labels vary between Excel versions. Keep the source data and check your results before reusing the method.
Prepare a small stock list
Use Product, Stock and Reorder level in A1:C5. Enter Notebook 8 and 10, Pen 25 and 20, Folder 10 and 10, and Marker 0 and 5. In D1 type Status.
In D2 use =IF(B2<=C2,"Reorder","OK") and fill down. The labels should be Reorder, OK, Reorder and Reorder. The rule includes the exact reorder level, so Folder is an important boundary test.
Create a formula-based rule
Select A2:D5. Open Conditional Formatting and create a rule using a formula. Enter =$B2<=$C2, choose a light highlight with readable dark text and apply it to the selected range.
The dollar signs fix columns B and C while the row remains relative. Because the selection begins at row 2, the rule begins with row 2. Excel evaluates it for each record in the applied range. Notebook, Folder and Marker should be highlighted; Pen should not.
Inspect the rule and range together
A correct formula with the wrong Applies to range can format unrelated cells or only part of the table. Open the rule manager and check both. If you add a sixth row, confirm that the rule expands or extend it deliberately.
Avoid applying several complex rules to a whole worksheet when a small range is enough. A compact set of well-named rules is easier to inspect. If multiple rules overlap, priority and Stop If True can affect what appears.
Test a controlled change
Change Notebook stock from 8 to 12. Its text should become OK and its highlight should disappear. Restore 8 after testing. If only one changes, inspect the text formula and formatting rule separately.
Clear a stock cell in a practice copy. A blank can behave like zero in a numeric comparison. If an unrecorded count needs a Missing label, add that rule explicitly rather than letting the colour imply confirmed low stock.
Keep the visual design simple
Choose one emphasis for the condition readers must notice. Several bright colours, icons and data bars can compete for attention. Check contrast and keep a text explanation near the sheet. A highlight means a rule matched; it does not mean someone has reviewed or resolved the issue.
Try it yourself
Create a rule that highlights only confirmed zero stock. Which sample product matches? How does that differ from the broader reorder condition?
Check your answer
For numeric inputs, =$B2=0 highlights Marker in A2:D5. If blanks are possible, add an explicit nonblank check, such as =AND($B2<>"",$B2=0), and test it.
Zero stock is narrower than stock at or below the reorder level. If both rules apply, choose a deliberate priority and keep a text status such as Out of stock so the meaning does not depend on colour.
Keep exploring: Excel learning path.