Traffic-Light Icon Sets

Excel Formulas › Conditional Formatting

All versionsIcon sets

Red, yellow, green — nothing reads faster than a traffic light. Icon sets drop a little symbol in each cell based on thresholds you control, turning a column of KPIs into an instant status board.

Quick formula: select your values, then:
Home → Conditional Formatting → Icon Sets → 3 Traffic Lights
By default Excel splits the range into thirds by percentile. Use More Rules to set exact number cutoffs instead.

The example

Project health scored 0–100, with custom cutoffs at 50 and 80.

AB
1ProjectHealth
2Alpha🟢 92
3Bravo🟡 67
4Cosmo🔴 41

The formula

Icon sets are built-in — the key is setting your own thresholds:

Icon Sets → More Rules → Type: Number Green ≥ 80 Yellow ≥ 50 Red < 50 // exact cutoffs, not percentiles

How it works

Each icon is assigned by where the value falls against your cutoffs:

  1. Select the range, then Conditional Formatting → Icon Sets and pick a 3- or 4-icon style (traffic lights, arrows, ratings).
  2. Open Manage Rules → Edit Rule to control the cutoffs. Change Type from Percent to Number for fixed thresholds.
  3. Set each icon’s boundary — e.g. green when value ≥ 80, yellow ≥ 50, red below. The order is top (highest) to bottom.
  4. Tick Show Icon Only to hide the numbers for a clean dashboard, or leave them for context.

Reverse the order when low is good (like defect counts) using the Reverse Icon Order button — so green marks the small numbers. You can also mix icons from different sets via the dropdowns.

Try it: interactive demo

Live demo

Set thresholds; icons update.

Variations

Number thresholds

Switch Type from Percent to Number for exact cutoffs.

Reverse (low is good)

Use Reverse Icon Order so green = small values.

Icon only

Check “Show Icon Only” to hide the numbers.

Pitfalls & errors

Percent ≠ percentile ≠ number. The default “Percent” type splits the range, not your data values. Switch to Number for meaningful business thresholds.

Text and blanks get no icon. Icon sets only apply to numbers; non-numeric cells are skipped silently.

Boundary direction. Excel uses “≥” from the top icon down; double-check whether a value exactly on a cutoff lands where you expect.

Practice workbook

📊
Download the free Traffic-Light Icon Sets practice workbook
A KPI board with traffic-light icons on custom number thresholds, a reversed example, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I add traffic-light icons in Excel?
Select the values, then Conditional Formatting → Icon Sets → 3 Traffic Lights. Edit the rule and set Type to Number to control the exact cutoffs.
Why are my icons not matching my thresholds?
The default type is "Percent," which splits the range rather than using your numbers. Change Type to Number in Manage Rules → Edit Rule.
How do I make green mean low values?
Use the Reverse Icon Order button in the rule editor — useful for metrics where lower is better, like error counts.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Color-scale heatmap · Highlight above average · Data bars