To create a traffic light graph in Excel, put the values you want to assess in a column, select them, then choose Home > Conditional Formatting > Icon Sets > 3 Traffic Lights. For a useful status dashboard, set the icon thresholds to match your rules, label the measures, and show the icons beside the numbers; you can also summarize the red, amber, and green counts in a chart.
What a traffic light graph shows
“Traffic light graph” can mean two related things in a spreadsheet. It may be a table of measures marked with red, amber, or green indicators, or a chart that summarizes how many items fall into each status. Excel’s built-in traffic light icon sets are conditional formatting, not a chart type. They are often the quickest way to make a KPI dashboard readable, because the original values remain available for checking.
Use an indicator table when a reader needs to see which individual projects, targets, or tasks need attention. Use a chart when the main question is about the overall distribution—for example, whether a team has more green projects than red ones. These formats can work together: display an icon beside each project and add a small summary chart above or next to the table.
Prepare the data and choose meaningful thresholds
Start with a clear row for each item and a consistent measure in one column. For example, a project table could have Project in column A, Completion in column B, and Owner in column C. Enter completion as percentages, not a mixture of percentages and text such as “nearly done.” Then decide what each color means before applying formatting.
- Green: on track or meeting the defined target.
- Amber: a warning range that merits monitoring.
- Red: below the acceptable range or requiring action.
For a completion measure, a team might choose green at 80% or above, amber from 60% up to but not including 80%, and red below 60%. Those are illustrative thresholds, not universal standards. A project that is only 40% complete might be healthy early in its schedule, while the same number could be critical near its deadline. Set cutoffs using the measure’s context, and document them where dashboard viewers can find them.
Apply the built-in traffic light icons
In Excel for Microsoft 365 on Windows, enter the measures in a range such as B2:B20 and select that range. Choose Home > Conditional Formatting > Icon Sets, then select 3 Traffic Lights (Unrimmed) or 3 Traffic Lights (Rimmed). The icons appear beside the values. The exact appearance depends on the selected set and your Excel theme.
- Keep the measure cells selected and choose Home > Conditional Formatting > Manage Rules.
- In the rules list, select the icon-set rule and choose Edit Rule.
- Set the two threshold values and change each threshold’s Type to Number when you want fixed numeric cutoffs.
- Choose the desired icon order, and select Show Icon Only if the table should display indicators without the underlying numbers.
- Confirm the rule and inspect the results against a few known values.
Excel commonly starts icon-set rules with percentage-based cutoffs, such as dividing the selected values into relative groups. That can make the colors change when the data changes, even if a particular value has not changed. For operational thresholds such as “green at 80%,” use Number rather than leaving a relative percentage rule in place.
Set fixed green, amber, and red cutoffs
Suppose B2:B4 contains 92%, 74%, and 48%, and the intended rules are green at 80% or higher, amber at 60% or higher, and red below 60%. In the Edit Formatting Rule dialog, choose Number for both threshold types and enter 0.8 and 0.6. Excel stores percentages as decimal values, so 80% is 0.8 and 60% is 0.6. With the usual descending three-icon order, the examples should show green, amber, and red respectively.
Check the comparison operators shown in the dialog as well as the numbers. A threshold is commonly treated as “greater than or equal to,” but the displayed operator determines what happens to an item exactly on a boundary. Test values at 60% and 80% if those boundaries matter to your reporting. Icon rules also rely on the order of the icons; verify that the highest values receive the intended favorable color rather than assuming the icons will match your preference.
If Excel applies the rule to a larger or smaller range than expected, reopen Manage Rules and check Applies to. When using a table that will grow, consider converting the data range to an Excel table with Home > Format as Table. Table formatting and conditional-formatting expansion can make new rows easier to manage, but it is still sensible to verify that the new rows received the rule.
Build a chart that summarizes status counts
To turn the status results into a conventional chart, first create a text status column. For example, put each project’s status—Green, Amber, or Red—in C2:C20. Create a small summary table with those three labels in E2:E4, then count each label in the adjacent cells. With statuses in C2:C20 and the labels in E2:E4, enter this formula in F2 and fill it down to F4: =COUNTIF($C$2:$C$20,E2). If there are 9 Green, 7 Amber, and 4 Red entries, the summary will return 9, 7, and 4 beside the matching labels.
Select E1:F4 if you have added headings such as Status and Count, then choose Insert > Insert Column or Bar Chart > Clustered Column. Add a descriptive title, such as “Project status,” and keep the category labels visible. To color the bars consistently, select an individual data point and use Format Data Point > Fill to choose the matching green, amber, or red. Excel may initially give all bars the same color, so format each point separately if the colors do not automatically distinguish the categories.
The summary chart answers “how many items are in each status?” It does not show which projects are responsible for the counts, so retain the underlying detail table. Also, do not rely on color alone: retain text labels or values, since readers may have difficulty distinguishing colors or may print the workbook in grayscale.
Use formulas when your source data needs interpretation
Icon sets work directly on numeric cells, but real data may require a calculation first. For example, if actual completion is in B2 and the target is in C2, with a positive target in C2, put the following in D2 and fill down: =B2/C2. If B2 is 72% and C2 is 80%, D2 returns 90%. You can apply a traffic light rule to D2:D20 to show attainment against target. Format the results as percentages if displaying the numbers, or choose Show Icon Only if the ratio is just a helper measure.
Be careful about whether high or low values are good. A completion percentage usually improves as it rises, but defect counts, overdue days, and spending may be better when lower. Applying the ordinary icon order to a lower-is-better measure could label the worst result green. In that case, create an appropriate helper score, reverse the icon order if the rule dialog offers the needed arrangement, or use formula-driven conditional-formatting rules. Check the logic with sample values before sharing the workbook.
Make the dashboard understandable and accessible
A traffic light is a quick signal, not an explanation. Add a column heading such as “Status” or “Attainment,” state the reporting period, and include a brief note describing the thresholds. If different measures use different cutoffs, show the applicable target alongside each measure rather than using one unexplained legend for the whole sheet.
- Keep the number, unit, and status close together; for example, “74%” beside an amber indicator.
- Use consistent number formats and avoid mixing blank values with zero unless they mean the same thing.
- Use readable labels and sufficient contrast, and do not communicate status through color alone.
- Freeze the header row for long lists and use filters when viewers need to find particular owners or categories.
- Explain what a blank or unavailable result means so it is not mistaken for green or zero.
For people who use screen readers or have color-vision differences, a colored icon alone may not communicate the status adequately. Keep a text status column, use clear labels, and consider adding a short legend. A chart should likewise have a descriptive title and category names rather than relying on three colored bars without explanation.
Common problems and their fixes
If the icons appear in an unexpected order, edit the rule and check both the icon sequence and threshold operators. If all values show the same icon, confirm that the cells contain numbers rather than text, that the cutoff type is appropriate, and that the thresholds make sense for the scale. A value stored as text may look like “75%” but fail to behave like a numeric percentage.
If colors shift as new values are entered, check whether the thresholds are set as percentiles or percentages of the selected range instead of fixed numbers. If a new row has no icon, check the rule’s Applies to range. If the chart counts are wrong, check spelling and extra spaces in the status labels, and make sure the COUNTIF range includes all current records. Keep one consistent spelling for each status.
Excel for Mac and Excel for the web
On current desktop versions of Excel for Mac, the Conditional Formatting and Icon Sets features are available through the Home ribbon, though dialog layouts can differ from Windows. Use the rule-management dialog to adjust thresholds and verify the resulting range. Keyboard shortcuts can vary by keyboard and macOS settings, so the ribbon path is a dependable way to reach the commands.
Excel for the web can handle many everyday conditional-formatting and chart tasks, but detailed rule editing or some dialog options may differ from desktop Excel. If you cannot set the threshold types or icon order you need in the browser, open the workbook in the desktop app. Avoid treating an icon set as a chart object: the icons are cell formatting, while the summary chart is a separate object created from the summary data.
Frequently Asked Questions
Can I make a traffic light graph without showing the numbers?
Yes. Edit the icon-set rule and select Show Icon Only. Keep the underlying numbers in the cells so the rule can evaluate them, and consider providing a text label or a separate visible value for readers who need more context.
How do I change the traffic light thresholds in Excel?
Select a cell with the rule, choose Home > Conditional Formatting > Manage Rules, edit the icon-set rule, and set the threshold types and values. Use fixed numeric thresholds for stable targets, and test values at the boundaries.
Can traffic lights be based on text such as “On track”?
Icon sets are designed to evaluate values, so they are not the most direct choice for text categories. You can apply conditional formatting to text with rules, use colored fills, or create a numeric helper column that represents the statuses and apply icons to that column.
Why does Excel show the wrong color for a low score?
The default icon order may treat higher numbers as better. For lower-is-better measures, revise the icon order or use a helper calculation and test high, middle, and low examples. The color should reflect the meaning of the metric, not just its size.
Is a traffic light icon set a real Excel chart?
No. It is conditional formatting applied to worksheet cells. To make a chart, summarize the number of red, amber, and green items in a separate table, then insert a chart from that summary.
Related
- Excel Trendlines: How to Add, Format, and Choose One
- Scenario Analysis in Excel: Example and Setup Guide
- Rate Analysis Excel Sheet: Build and Check Unit Rates
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Box Plot in Excel: Create, Format, and Read the ChartYes. In Excel for Microsoft 365 on Windows, select your numeric data, then choose Insert > Insert Statistic…
- PowerPoint Org Chart: Make All Boxes the Same SizeYes. For an org chart made from separate shapes, select the boxes and enter matching Height and Width…
- Make Charts in Excel the Same Size: A Step-by-Step GuideTo make several charts the same size in Excel for Microsoft 365 on Windows, select their chart borders…
- Excel Box Plot: Add a Series and Expand the Chart DataTo add a series to an Excel box plot in Microsoft 365 for Windows, select the chart, choose…
- Pivot Chart Formatting Options in Excel 365 ExplainedIn Excel for Microsoft 365 on Windows, format a PivotChart with the contextual Chart Design and Format tabs:…
- Bell Curve Pivot Chart in Excel: Build and Explain ItA PivotChart can summarize your data into bins, but Excel does not provide a one-click bell-curve PivotChart. To…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.