Conditional formatting in Excel: how it works

With conditional formatting, Excel colours a cell by itself when it meets a rule, such as red below 0. When the value changes, the colour changes with it. You'll find it on the Home tab.

Try the first exercises for freeIn the lesson Conditional formatting

What does conditional formatting do?

Conditional formatting gives a cell a colour, bold text or a bar, but only if it meets a condition. You set up a rule once, such as: colour it red if the value is below 0. After that, Excel keeps track for you: if the value changes, the formatting changes with it.

That way, exceptions stand out automatically in a long list: who's below target, which invoice is overdue, which code appears twice. With a formula as the rule, you can even colour a whole row based on one column.

When do you use conditional formatting?

Negative amounts in red: Highlight Cells Rules > Less Than, with 0.
Tracking down duplicate codes in a list: Highlight Cells Rules > Duplicate Values.
Seeing at a glance which revenue is high or low: data bars or a colour scale.
Colouring a whole row if column E says no: a rule with the formula =$E2="no".

Creating a rule, step by step

This is how to go about it:

Select the cells that can get a colour, for example the column with results.
Choose Home > Conditional Formatting > Highlight Cells Rules, and then a rule such as Less Than.
Type the limit, such as 0, choose a format on the right, such as a red fill, and click OK.
Change a number in your list: the colour adjusts straight away.
If you want to colour a whole row, select the rows and choose New Rule > Use a formula to determine which cells to format. Type the formula for the first row of your selection.=$E2="no"

Colouring a whole row

Choose New Rule > Use a formula to determine which cells to format, and type a formula such as =$E2="no". The dollar sign before the E makes every cell in the row look at column E.

Where it often goes wrong

You write a formula in a rule for the first row of your selection. If your selection starts in row 2, your formula refers to row 2 as well.
Rules pile up if you keep adding them again. Tidy them up under Conditional Formatting > Manage Rules.

Want to try it yourself?
Practise this in the lesson Conditional formatting. The first exercises are free.

Try the first exercises for free

Frequently asked questions

Can I see which rules apply to a cell?

Yes, under Home > Conditional Formatting > Manage Rules. Choose This Worksheet at the top to see all the rules on the sheet.

What are data bars?

A bar in the cell that gets longer as the number gets bigger. That way you can see at a glance which values are high and low, without a chart.