📎 This article includes 1 downloadable practice file ↓

📘 Excel Beginner Course · Lesson 16 of 18

A stock sheet with 300 items hides the three that are about to run out. Conditional formatting colours cells automatically based on their values: low stock turns red, expiring items amber, the biggest numbers get the longest bars. Change the data and the colours follow.

Everything is under Home › Conditional Formatting. Select the cells first.

Quick rules

Visual rules

• Data Bars: a bar inside each cell, proportional to the value. Great for stock or sales columns.
• Colour Scales: green-yellow-red shading. Good for heat maps like sales by region × month.
• Icon Sets: arrows or traffic lights. Use sparingly; three icons are easier to read than five.

Formula rules: compare with another cell

Quick rules compare with a fixed number. When each row has its own limit (a reorder level per item), use a formula: New Rule › Use a formula to determine which cells to format.

=$B2<$C2                         ' stock below its own reorder level
=AND($D2>=$B$13, $D2<=$B$13+30)  ' expires within 30 days of the date in B13
=$D2<$B$13                       ' already expired

Select the whole table (A2:E11) before creating the rule, and the entire row is coloured. The $ before the column letter keeps every cell looking at column B or D; the row number stays free so each row checks itself. Write the formula as if for the first row of your selection.

Managing rules

Conditional Formatting › Manage Rules (choose “This Worksheet” at the top) lists every rule. You can edit the range, change the order and tick Stop If True so a red “expired” rule wins over an amber “expiring soon” rule. Copying and pasting rows often duplicates rules; clean them up here now and then.

Keep it readable

• Colour should mean something. Red for problems, green for good, nothing for normal.
• Two or three rules per table is plenty.
• Light fills with dark text read better than bright fills.
• Remember some readers are colour-blind; icons or bold text help.

Where beginners go wrong

More examples: highlight an entire row with a formula.

Practice

Download this lesson’s workbook below. The Stock sheet has stock, reorder levels, expiry dates and suppliers; build the rules and answer how many cells each should colour. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.