Conditional formatting

Conditional formatting changes a cell's color or style based on its value, with rules like "greater than", "duplicates" or a color scale, and updates on its own when the data changes.

Select any words here to ask your Maestro about them.

You add a two-color scale (white to green) to October sales for 12 stores. Downtown sold $412,000; the other 11 sold between $28,000 and $47,000. What do those 11 cells look like?

October sales at 12 stores in a chain. Example data.

What it is: rules that change a cell's formatting (fill, font color, icons, data bars) based on its value: highlight what beats a target, flag duplicates, shade a color scale. In Excel it's under Home > Conditional Formatting; Google Sheets has the same thing.

What it's for: making a pattern jump out of a big table, like an outlier or the months a KPI fell short of its target. In a dashboard built in Excel, it points the eye at what matters.

Where it breaks: the color is only as good as the rule, and a badly written rule confidently shades the wrong cells. Check two or three shaded cells and two or three unshaded ones by hand.

How it's used

Highlight what beats a target

Sales above target

Select the sales column
marked: Home > Conditional Formatting > Highlight Cells Rules > Greater Than
Type 45000 and pick a formatYou can also point to a cell holding the target, so you change it in one place.
Change a sale: the color updates on its ownThe formatting follows the values; it isn't paint.

Conditional formatting is a rule, not a color: it's re-evaluated every time the data changes.

When to look closer

A formula rule for A2:D50

Range A2:D50

marked: Rule: Use a formula: =$D1>1000

You format A2:D50 with the rule =$D1>1000. Which row's value decides the color of row 2?

Ask your Maestro about Conditional formatting

It answers from the free course page closest to this word, the way it answers while you study.

Example

You: What does "Conditional formatting" mean, in plain words?

Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. Which number in your spreadsheets would you like flagged automatically?

Asking about: Answers from the page "What does an empty cell mean? Zero, "unknown" or "not applicable""

Only this browser keeps what you ask here. How it works

Learn more

Use conditional formatting in Excel (Microsoft)

Outlier
An outlier is a value that sits far from the rest, like a $9,800 order among $300 ones. It can be a data-entry error or a real, important case: before you drop it, find out which.
Percentile (and quartiles)
A percentile is the value a given share of the data falls at or below: 90% falls at or below the 90th. Quartiles are the 25th, 50th and 75th.
KPI (key performance indicator)
A KPI is a number a team tracks to know whether it's doing well at what matters most, like monthly sales or orders delivered on time. It only works if it's well defined, so two people who calculate it get the same answer.
Dashboard
A dashboard puts the few numbers someone checks often to make a decision on one screen, each next to its goal or its trend.

The whole glossary