What conditional formatting actually does
Conditional formatting is your instruction to Excel: "Format all cells that meet this condition in this particular way." When the data changes, the formatting updates automatically to reflect the new values. You set the rule once — Excel applies and maintains it forever.
A column of sales figures where all values above target are automatically green and all values below are red communicates performance status instantly, without anyone manually reviewing and colouring cells after each data update. That automation is what makes conditional formatting genuinely valuable rather than decorative.
Why it beats manual formatting
Manual formatting breaks the moment data changes. If you manually colour all negative values red, and then the data updates, you have to repeat the process. Conditional formatting eliminates this entirely — the rule runs on the current cell values, not a snapshot of them.
It also scales. Manually formatting 5,000 rows of data is not realistic. Applying a conditional formatting rule to 5,000 rows takes the same three clicks as applying it to 5 rows.
How to apply your first rule
Select the cells you want to format. Go to Home → Conditional Formatting → Highlight Cells Rules or Top/Bottom Rules. Choose your condition. Set the format — a preset colour combination or a custom format you define. Click OK.
The keyboard shortcut for opening Conditional Formatting in older Excel versions is Alt+O+D. In Excel 2007 and later, the ribbon approach is faster.
Cell value rules
Cell value rules test the value of the cell itself. Options include: greater than a threshold, less than a threshold, between two values, equal to a specific value, containing specific text, occurring on a specific date, or being a duplicate value.
The most common: Highlight Cells Rules → Greater Than → type your threshold → choose a format. Useful for: marking overdue items (date is before today), flagging sales below target (value is less than the target amount), or highlighting duplicates (to find repeated entries in a list).
Formula-based rules
Formula-based rules are more powerful. Instead of testing the cell's own value, you write a formula that can reference any cell, and Excel formats the selected range wherever the formula evaluates to TRUE.
The formula must start with an equals sign and return TRUE or FALSE. Example: to highlight an entire row where the status column (column D) says "Overdue", select the entire data range, choose New Rule → Use a formula → type =$D2="Overdue". The $ before D fixes the column reference while allowing the row reference to adjust for each row.
=$D2="Overdue"
-- Highlight if value is below average of the range:
=A1<AVERAGE($A$1:$A$100)
-- Highlight every other row (zebra striping):
=MOD(ROW(),2)=0
Colour scales and data bars
Colour scales apply a gradient across a range — typically green for high values shading to red for low values, or any combination of three colours representing minimum, midpoint, and maximum. They are useful for immediately communicating relative performance across a dataset without knowing the exact values.
Data bars add a proportional horizontal bar inside each cell, sized relative to the range's minimum and maximum. They work like a mini bar chart embedded in the cells themselves — readable at a glance without opening a separate chart.
Icon sets
Icon sets add a small icon to each cell — arrows (up, flat, down), traffic lights (green, amber, red), flags, stars, or shapes — based on the cell's value relative to thresholds you define. Traffic light icon sets are particularly effective for status dashboards where you want operational red-amber-green signalling without separate status columns.
Managing and editing rules
Home → Conditional Formatting → Manage Rules shows all rules applied to the current selection or the current worksheet. From here you can edit, delete, reorder (rules apply in order from top to bottom, with earlier rules taking priority), and copy rules to other ranges.
The "Stop If True" checkbox next to each rule prevents lower-priority rules from applying when a higher-priority rule is already true. Useful when you have a hierarchy of conditions and don't want multiple rules stacking on the same cell.
Common use cases
Highlighting duplicates in a data column before appending to a master list. Marking all rows where the booking date is more than 30 days in the past. Flagging cells where the formula result is an error (#N/A, #VALUE) using =ISERROR(A1). Creating a heat map of values across a matrix using a three-colour scale. Adding a visual progress indicator to a task list using data bars on a percentage completion column.
`