Traffic Light Chart in Excel: Build a Status Dashboard

A traffic light chart in Excel is a visual status indicator that uses green, yellow, and red symbols to show whether values meet, approach, or miss a target. The simplest version is a table with traffic-light icons: select the numeric results, then choose Home > Conditional Formatting > Icon Sets and select a three-light set.

What a traffic light chart shows

The colors communicate status at a glance. Green usually means a result is acceptable or on target, yellow means it needs attention or is close to a limit, and red signals an exception or an unmet target. The exact meaning is not universal: a team should define what each color means before people use the chart to make decisions.

In Excel, people often call an icon-set table a traffic light chart, even though it is not a chart in the technical sense. It does not plot values on axes like a column or line chart. Instead, it adds a symbol beside each value, making a list or dashboard easier to scan. This approach works well for project milestones, service levels, quality checks, inventory status, and performance measures.

For example, a report could show each project’s completion percentage next to a colored light. A project at 96% might be green, one at 82% yellow, and one at 58% red. Readers can still see the underlying numbers, while the icons help them spot which rows may need follow-up.

Build a traffic light chart with icon sets

Start with a simple, well-labeled table. Put each item in one column and its numeric measurement in another. For example, column A might contain project names and column B completion percentages, entered as actual percentages such as 96%, 82%, and 58%. Select only the cells containing the measurements, not the headings or names.

In Excel for Microsoft 365 on Windows, select the results you want to format.
Choose Home > Conditional Formatting > Icon Sets.
Choose a three-light traffic light icon set. Excel offers traffic light styles with different appearances; pick the one that is clearest at the size you plan to use.
Check the icons against your intended meaning. Excel applies default thresholds, which may not match your business rules.
To change the thresholds, choose Home > Conditional Formatting > Manage Rules, select the icon-set rule, and choose Edit Rule.

In the rule editor, set the threshold types to Number if you want fixed numeric cutoffs. For a percentage measure, for example, you could set green at 90% or higher and yellow at 70% or higher, leaving values below 70% red. Enter the cutoffs as 0.9 and 0.7 if the cells contain decimal percentage values, or as 90 and 70 if the cells contain whole numbers from 0 to 100. The values in the cells and the thresholds must use the same scale.

Excel’s Conditional Formatting menu can also be opened with the Windows key-tip sequence Alt+H, L. On a Mac, use Home > Conditional Formatting; ribbon key tips differ, and there is no need to rely on the Windows sequence. Excel for the web includes conditional formatting options, but rule-editing controls may differ from the desktop dialog. If a detailed icon-set rule is difficult to adjust in the browser, open the workbook in desktop Excel.

Choose thresholds that match the measure

Thresholds should reflect what the number means, not just produce an attractive distribution of colors. A completion percentage and a defect rate need different logic. Higher completion is generally better, while a lower defect rate is usually better. Applying the same cutoffs to both could label poor results green.

Define the decision: Say what action a red or yellow status should prompt. If no one knows what to do with a color, it is decoration rather than a useful signal.
Use meaningful boundaries: Set cutoffs from targets, service commitments, safety limits, or agreed review criteria, rather than choosing values solely to make the chart look balanced.
Check boundary cases: Decide which category includes an exact cutoff. With green at 90 and yellow at 70, 90 belongs to green, 70 belongs to yellow, and a value below 70 is red.
Keep rules consistent: If a dashboard compares several teams or periods, the same measure should normally use the same thresholds throughout.
Document exceptions: If a measure has special cases, state them near the table or in a note instead of expecting readers to infer them from the colors.

There is no single correct set of cutoffs for every traffic light chart. A project team might use schedule variance, while a help desk might judge response time against a service target. A red result can also mean different things across organizations: it may demand immediate action in one context and simply flag a value for review in another.

Make the chart useful in a dashboard

Icons work best when they add a quick signal without hiding the underlying information. Keep the measured value visible where practical, and label the column clearly. A heading such as Status next to a separate Completion column is more informative than a row of unexplained colored symbols.

For a compact dashboard, place the status icon beside a short item name and a date, owner, or key result. Sort or filter the source table to bring urgent items into view, but do not rely on color alone to convey priority. A written status, such as At risk or On track, can make the table easier to understand for readers who cannot distinguish the icons.

Consider whether the icons should appear with the numbers or by themselves. In the Edit Formatting Rule dialog, the Show Icon Only option hides the cell values. That can make a narrow status column tidy, but it removes useful context. If readers need to know whether a result is 89% or 55%, keeping the number visible is usually better. If the exact value is available in a neighboring column, icon-only display may be suitable.

Traffic light chart versus other Excel options

An icon set is a good choice when each row needs one of a few discrete statuses. It is compact, updates automatically when the values change, and can be applied without creating a separate chart object. It is particularly handy for operational lists where people need to identify exceptions quickly.

A bar chart is better when readers need to compare magnitudes or trends. A traffic light tells someone that a value crossed a threshold, but it does not show how far the value is from the target as clearly as a bar or bullet-style display. A color scale, found under Home > Conditional Formatting > Color Scales, shows a continuous range of values rather than three named status categories. It may be useful for exploration, but its colors can be harder to interpret as fixed actions.

A manually inserted traffic-light graphic is another option for a presentation or a single prominent indicator. However, a drawn shape does not automatically reflect changing cell values unless it is connected to additional workbook logic. For a regularly updated report with many rows, conditional formatting is usually easier to maintain than individually colored shapes.

Common problems and how to fix them

The colors do not match the expected cutoffs: Edit the rule and inspect both Type and Value for each threshold. Icon-set rules often begin with percentage-based defaults, which represent positions in the selected range rather than fixed business cutoffs. Change the type to Number when you need specific limits.

Percentages appear to be classified incorrectly: Check how the data is stored. A cell displaying 90% usually contains 0.9, whereas a cell containing 90 and formatted as a number is on a 0-to-100 scale. Match the rule thresholds to the actual stored values, not merely the cell’s display format.

Blank or text cells show unexpected icons: Icon sets are intended for numeric values. Remove blanks and labels from the selected range, or limit the rule to the cells containing measurements. Also check for numbers stored as text, which may not behave like numeric values in conditional formatting.

Every row shows the same color: Verify that the selected range includes the intended cells and that the thresholds are ordered correctly. Check for a different conditional-formatting rule that also applies to the range. Use Home > Conditional Formatting > Manage Rules to inspect rules and their Applies to ranges.

The chart changes meaning when data is added: Make sure the conditional-formatting rule covers new rows. If the data is an Excel table, adding rows to the table can help keep related formatting with the expanding data, but confirm the rule’s Applies to range after making structural changes.

Accessibility and clear interpretation

Color should not be the only way to understand the status. People may have color-vision deficiencies, view the workbook in grayscale, or use a display where the icons are hard to distinguish. Keep descriptive headings and, when practical, include a text status or the numeric measurement alongside the icon. Use a clear legend that explains the thresholds, especially when the workbook will be shared outside the team that created it.

Check the chart at its actual viewing size. Traffic-light symbols that look distinct on a large monitor may become difficult to read when printed or squeezed into a narrow dashboard. Avoid relying on subtle differences between similar shades, and do not use green to imply success if the underlying measure is one where lower values are better. Consistent labels and documented cutoffs make the display more reliable.

Frequently Asked Questions

Is a traffic light chart a real chart type in Excel?
Not usually. The phrase commonly refers to conditional-formatting icons in a table. Excel also lets you build conventional charts, but the traffic-light effect most people want is typically made with an icon set.

How do I change the red, yellow, and green limits?
Open Home > Conditional Formatting > Manage Rules, select the icon-set rule, choose Edit Rule, and adjust the thresholds. Set the threshold type to Number for fixed numeric cutoffs, and make sure the cutoff scale matches the stored values.

Can a traffic light chart update automatically?
Yes. Conditional-formatting icons recalculate their appearance when the numeric cell values change. The rule must apply to the relevant cells, including any new rows you add.

Can I use traffic lights when lower values are better?
Yes, but the status logic must reflect the measure. In the icon-set rule, use the reverse-icon-order option if appropriate, then verify the result with sample high, middle, and low values. Do not assume the default order fits every metric.

Can I show only the icons and hide the numbers?
Yes. Edit the icon-set rule and select Show Icon Only. Use that option when the exact values are available elsewhere or are not needed for the decision; otherwise, retaining the numbers gives readers more context.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.