Stylish traffic lights in Excel give your dashboard a glossy, 3-D status lamp for every KPI that turns red, amber or green on its own. The trick is simple: a grey frame with a round hole sits on top of a few cells, and conditional formatting colours those cells. There is no VBA, no chart and no picture swapping. In this tutorial you build the frame, add a glass shine, write the three colour rules and link live labels, exactly as shown in the video.
Watch the Video Tutorial
In the video PK builds the frame in PowerPoint, styles it in Excel with a 3-D bevel, adds the glass shine with gradient ovals, colours the lamp with three conditional formatting rules and finishes with WordArt labels linked to the cells.
Stylish Traffic Lights in Excel: What You Will Build
- A small data table with three KPIs and a colour legend.
- A 3-D frame shape with a round hole, made with Merge Shapes in PowerPoint.
- A glass shine made from three white gradient ovals.
- Three conditional formatting rules that colour the lamp red, amber or green.
- Linked WordArt labels that show the KPI name and its current value.
Prefer a lighter build with formulas and symbol fonts? The KPI traffic lights with IF formulas and Wingdings dots tutorial shows a different method.
Part 1 – Prepare the Data
Step 1: Enter the KPIs and the colour legend
Type the three metrics in B1:B3 and their values in C1:C3. Add the legend in G1:H3 so everyone knows the rule:
| Row | B (Metric) | C (Value) | G (Colour) | H (Rule) |
|---|---|---|---|---|
| 1 | Service Level | =RANDBETWEEN(10,100)% | Red | <50% |
| 2 | Quality Score | =RANDBETWEEN(10,100)% | Amber | 50% to 70% |
| 3 | Sales Conversion | =RANDBETWEEN(10,100)% | Green | >70% |
In the video the values are test numbers made with =RANDBETWEEN(10,100)%, which returns a whole number from 10 to 100 and turns it into a percentage. Press F9 (or Formulas > Calculate Now) to get new values and watch the lights change. In a real dashboard you replace these formulas with your actual KPI values.
Step 2: Hide the gridlines
Go to View and untick Gridlines. The lights look much cleaner on a plain white sheet.
Part 2 – Build the Traffic Light Frame
Step 3: Cut a round hole with Merge Shapes in PowerPoint
PK uses PowerPoint 2013 for this step because it has Merge Shapes:
- Insert a Rounded Rectangle and make it a little bigger.
- Insert an Oval on top of it.
- Select both shapes and use Align > Align Center and Align > Align Middle so the circle sits exactly in the middle.
- Go to Shape Format > Merge Shapes > Combine. The overlapping area is removed, so you get a rounded square with a round hole.
PowerPoint 2010? Merge Shapes is not on its ribbon, but Combine Shapes exists. Go to File > Options > Customize Ribbon, choose Commands Not in the Ribbon, create a New Group on the Home tab and add Combine Shapes to it. Then select both shapes and click it.
Step 4: Paste the frame into Excel and give it a 3-D look
Copy the combined shape, paste it into Excel, right-click it and choose Format Shape:
- Line: Solid line, black, about 2 pt wide.
- Fill: Solid fill, a dark grey.
- Effects > 3-D Format: Top bevel Circle, Material Plastic, Lighting Soft.
The frame now looks like the raised plastic housing of a real signal lamp. In the video it measures 1.38 x 1.38 inches.
Part 3 – Add the Glass Shine
Step 5: Make the first gradient oval
Insert an Oval the same size as the hole. In Format Shape > Fill & Line set No line and Gradient fill, then:
- Delete the extra gradient stops so only two remain, and set both to white.
- Set the Type to Path.
- Second stop: Transparency 100%, Position about 20%.
- First stop: Transparency about 30%.
White on white is hard to see, so PK temporarily fills the cells behind the oval with black while he works. You remove that fill later.
Step 6: Add two more highlight ovals and group them
Copy the oval and change the second stop’s position to about 35% for a softer, bigger glow. Copy it again and change the gradient Type to Linear. Arrange the three ovals so the highlights sit where light would reflect on a glass lens, then select them (Ctrl+A selects all shapes; Ctrl+click the frame to leave it out), right-click and choose Group.
Move the group over the hole of the frame, select both and group them again. Your traffic light now has a frame and a glass lens. Set the temporary black cells back to No Fill.
Part 4 – Colour the Lamp with Conditional Formatting
Step 7: Choose the cells behind the hole
Here is the key idea: conditional formatting cannot change the colour of a shape, but it can colour cells. So the lamp colour comes from a small block of cells, for example D9:E13, that sits behind the hole. Select that block and make sure it has No Fill.
Step 8: Add the three colour rules
With the block selected, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Add one rule per colour and pick the fill in Format > Fill:
| Fill colour | Rule formula | Meaning |
|---|---|---|
| Red | =$C$1<50% | Value below 50% |
| Amber | =AND($C$1>=50%,$C$1<=70%) | Value from 50% to 70%, both ends included |
| Green | =$C$1>70% | Value above 70% |
Press Alt+O+D to reopen the Conditional Formatting Rules Manager when you want to add or check rules.
How the formulas work
- Each rule returns TRUE or FALSE for every cell in the block. When it is TRUE, Excel applies that rule’s fill.
$C$1is fully absolute, so every cell in the block looks at the same KPI value, Service Level. With a relative reference each cell would check a different cell and the lamp would colour only partly.AND($C$1>=50%,$C$1<=70%)is TRUE only when both conditions are met, which gives the amber band from 50% to 70%.- The three ranges do not overlap and leave no gap: 49% is red, 50% and 70% are amber, 71% is green.
Step 9: Put the frame on top of the coloured cells
Drag the grouped traffic light over the block so the cells fill the hole. Press F9: the lamp turns red, amber or green with the value, and the glass shine makes it look lit.
Step 10: Make the other two lights
Copy the cell block to a new spot for the next KPI, open the Rules Manager and edit each rule: replace $C$1 with $C$2 for Quality Score, and with $C$3 for Sales Conversion. Copy the traffic light shape twice and place each copy over its own block.
Part 5 – Add Linked Labels
Step 11: Link WordArt to the cells
Insert a WordArt below the first light, click its border, type =$B$1 in the formula bar and press Enter. It now shows Service Level; make it bold and resize it. Add a second WordArt above the light, link it to =$C$1, make it bigger and choose a WordArt style from the Format tab, so the current value is always shown.
Copy both labels to the other two lights and relink them to B2/C2 and B3/C3. Double-click Format Painter to copy the formatting to all labels in one go. Your stylish traffic lights are ready.
Common Errors and Fixes
The lamp stays white or grey
Either the frame has no hole, so it covers the cells, or the cells are not under the hole. Check that Merge Shapes really cut the circle out, and move the frame until the coloured cells show through.
Only part of the lamp changes colour
The rule uses a relative reference such as C1 instead of $C$1. Edit the rule and add the dollar signs. (Addition based on how conditional formatting works.)
The colour does not change when the value changes
Check that the value in column C is a real number or percentage, not text, and that each rule points to the correct row after you copied it. (Addition.)
Tips
- Merge Shapes also has Subtract: select the rounded rectangle first, then the oval, and Subtract cuts the hole the same way. (Addition.)
- Group the frame and lens, but keep the cell block separate, because cells cannot be grouped with shapes.
- Use the same frame for any KPI: only the three rule formulas and the WordArt links change.
- Microsoft explains rule formulas in Use formulas with conditional formatting.
Want It Ready-Made?
Want the finished lights without building them? Get the Stylish Traffic Lights in Excel template on NextGenTemplates.com and plug in your own KPI values, or browse more Excel charts and visualization templates. To learn how indicators like these fit into a complete report, the Excel Pivot Tables and Dashboards course on NextGenTemplates Academy covers charts, infographics and dashboard design step by step.
Related Tutorials
- KPI Traffic Lights with IF Formulas and Wingdings
- 3D KPI Indicator in Excel
- Filling Circle Chart in Excel
- Comparison Infographic in Excel
- Conditional Formatting in Excel
Frequently Asked Questions
Can I make stylish traffic lights in Excel without VBA?
Yes. The light is a grey frame shape with a round hole. The cells behind the hole get their colour from conditional formatting, so the light turns red, amber or green as soon as the value changes. No macro and no chart is involved.
Why are the colours on the cells and not on the shape?
Conditional formatting works on cells only; it cannot recolour a shape. By cutting a hole in the frame and placing it over a block of cells, the coloured cells show through the hole and look like the lamp of the traffic light.
What if my PowerPoint has no Merge Shapes button?
PowerPoint 2013 and later have Merge Shapes on the Shape Format tab. In PowerPoint 2010, open File, Options, Customize Ribbon, choose Commands Not in the Ribbon, add Combine Shapes to a new group and use that instead.
How do I change the red, amber and green thresholds?
Select the cells behind the light, press Alt O D to open the Conditional Formatting Rules Manager and edit the three rules. Change the 50% and 70% limits in each formula so that the ranges still meet without gaps.
Can I connect the traffic lights to my real KPI values?
Yes. The video uses RANDBETWEEN only to test the colours. Replace those formulas with your actual values or with links to your dashboard calculations, and the lights update on their own.
About the Author
PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, dashboards, VBA and Power BI since 2016 on his YouTube channel. Find more ideas in his Excel dashboard collection.
Conclusion
Stylish traffic lights in Excel come from one clever combination: a 3-D frame with a hole, a glass shine made of gradient ovals, and conditional formatting on the cells underneath. Three short rules decide whether the lamp is red, amber or green, and linked WordArt keeps the labels current. Build one light, copy it for every KPI, and your business or project dashboard shows its health at a glance, with no VBA at all.
