Home › Excel basics › Conditional formatting

Conditional Formatting in Excel: Rules, Formulas and Examples

Conditional formatting changes how a cell looks based on its value: red for overdue, green for on target, a bar for size. The formatting updates automatically when the data changes, so problems stand out without anyone looking for them.

Excel CSE · 3 min read

Quick rules: highlight cells

1. Select the range, for example the sales figures in C2:C100.
2. Go to Home › Conditional Formatting › Highlight Cells Rules.
3. Choose a rule, such as Greater Than…, type 1000, pick a format and click OK.

Other useful built-in rules in the same menu:

• Less Than / Between / Equal To for thresholds.
• Text that Contains, for example every cell with "urgent".
• A Date Occurring: yesterday, this week, last month.
• Duplicate Values: highlights every value that appears more than once.
• Top/Bottom Rules: the top 10 items, the bottom 10%, or above average.

Data bars, color scales and icon sets

These show the size of a value instead of a yes/no condition.

• Data Bars: a bar inside each cell, longer for larger values. Great for a quick in-table chart.
• Color Scales: a gradient, for example red for low and green for high. Turns a table into a heat map.
• Icon Sets: arrows, traffic lights or ratings based on thresholds.

Highlight an entire row with a formula

Built-in rules only format the cell they test. To color the whole row when a value is high, use a formula rule.

1. Select the whole table without headers: A2:C5.
2. Home › Conditional Formatting › New Rule › Use a formula to determine which cells to format.
3. Enter: =$C2>1000
4. Click Format, choose a fill color, and click OK twice.

The $ before C locks the column, so every cell in the row checks column C; the row number 2 has no $, so each row checks its own value. Only Anna's row is colored. More on this in absolute references.

More formula ideas:

• Overdue tasks: =AND($D2<TODAY(), $E2<>"Done")
• Zebra stripes: =MOD(ROW(), 2)=0
• Highlight a row when a status is "Cancelled": =$B2="Cancelled"

Manage, edit and remove rules

• Home › Conditional Formatting › Manage Rules shows every rule for the selection or the whole sheet. Change the order, the range ("Applies to"), or tick "Stop If True".
• Clear Rules in the same menu removes rules from the selected cells or the entire sheet.
• Copying cells also copies their rules. Too many overlapping rules can slow a workbook down, so clean them up regularly.