Stop 02 · Excel & SpreadsheetsTopics · 4/10

Conditional Formatting

In short

A manager won't read 300 numbers. Colour the bad ones red and the good ones green, and they'll spot the problem in two seconds flat.

Automatically highlighting cells based on rules (color scales, data bars, icon sets) — turning a table of numbers into something a stakeholder can scan in seconds.

The jaadu colours boxes on a grid page with green and red highlighters.

Think of it like this

The teacher's red and green pens

A teacher checking 60 answer sheets marks wrong answers in red and full marks in green. Flip through the pile and the weak topics jump out without reading a word. Conditional formatting is that pen, applied automatically by rules you set.

Watch it happen

A rule colouring delivery times

1/4These are delivery times in minutes for six orders. Hard to scan as plain numbers.

The big ideas

What you need to know

Colour scales

A gradient from light to dark shows high and low values at a glance across a whole table.

Data bars

Tiny bars inside cells turn a column into a mini bar chart without leaving the table.

Formula rules

Use a custom formula like =$E2>40 to colour an entire row based on one column.

Less is more

Colour only what needs attention. If everything is highlighted, nothing stands out.

See it in code

A tiny example

excel

1Range:   A2:F2002Rule:    Custom formula is3Formula: =$E2>404Format:  Light red fill

A rule that colours a whole row red when the delivery time in column E is over 40 minutes. The $ locks column E.

Save yourself the pain

Common mistakes

Oops

Using red and green as the only signal.

Do this instead

Some people can't tell red from green. Add icons, bold text or a blue-orange scale too.

Oops

Stacking five colour rules on one table.

Do this instead

Pick one or two rules that answer the question. Remove the rest.

Your mission

Highlight your weak subjects

~15 min

Quick check

You want the whole row to turn yellow when column D says "Pending". Which formula rule works for range A2:F100?

Go deeper

1Apply a color scale to a numeric column to spot highs and lows instantly.
2Build a custom rule (e.g. highlight cells below a target threshold).
3Learn where conditional formatting helps versus where it just adds visual noise.