I’m always on the lookout for new and interesting ways to present data in Excel and PowerPoint. So when I saw this DataPoint add in for Powerpoint I was definitely interested. As I looked through the site and of course their blogs, I stumbled across this post: “Traffic Lights as Dynamic Semaphores” and was immediately inspired. Don’t misunderstand me, DataPoint is a very sophisticated add in for PowerPoint and does a lot more than just this one chart, but it got me wondering… Do I need the software to create this effect? Could I recreate this manually and still have the traffic lights update when I adjust the data?
If you’ve read any of the posts on this blog, you’ll already know that I love a challenge, and so off I went. The idea was to create a series of traffic lights that show a warning light (orange) when the stock level is the same as the reorder level, a danger light (red) when the stock is lower than the reorder level and of course a full steam ahead light (green) when the stock levels are healthy and therefore above the order level for that product. As usual I started in Excel and created a data table.
The figures above are just an example and if you want to recreate this using your own data, you can download my worksheet here.
Then I needed to create some calculations to plot the different graphs. So I added three columns labelled “Red”, “Orange” and “Green”. Using IF statements, I then got excel to calculate whether the traffic light should be red, orange or green by putting a 1 in the appropriate column. The three calculations I used can be seen below:
Assuming your titles are in Row 1 and your stock column is in Column D and your reorder column is column E:
- Cell F2 =IF(D2<E2,1,””)
- Cell G2 =IF(D2=E2,1,””)
- Cell H2 =IF(D2>E2,1,””)
Copy and paste the calculations down the table and then your table should look like the one below:
As you can see, the stock for product ax234 (row 2) is higher than than the reorder level so there is a 1 in the Green column for that row. Product ss213 on the other hand has less stock than the reorder level, so there is a 1 in the red column. And so on.
As it stands though, all we have is a nice table. We could use conditional formating and create a table like the one below, but it doesn’t really look like traffic lights. In theory, we could change the formatting of the cells etc., change the direction of the table so that it works, but I wanted something a little more flash!
What I did was create a series of 3 pie-charts for each line. Two would be blank as there is no number in their cells, the third would be filled and create the “light”. Then behind the three pie charts, I create the illusion of the traffic light. Again, if you want to see more, you can download the worksheet here.
How is it done?
Step 1 – create a basic pie chart
With cell H2 highlighted, click on Insert > Pie > 2-D Pie > Pie
You may get a blank pie chart.
If this happens, right click on the chart and choose “Select Data”.
Click on the “Edit” button and then adjust the fields as shown below and press the OK button:
You should then have a filled pie chart. Delete the legend and title. Then adjust the fill of the chart and the background to “No fill” by pressing Ctrl+1 to bring up the formatting menu box and adjusting the parameters. Finally remove any border on the chart by selecting “No line” on the border color. Your chart should now look like this:
You can then adjust the fill of the pie chart so that it has a green radial gradient as shown below:
I’ve chosen a vivid green that changes gradually to a darker green with a radial gradient radiating from the top left corner.
Finally adjust the position and size of the pie chart so that it is perfectly centered inside your rectangular chart background, taking up as much of the space as possible. You do this by dragging the corners of the pie chart to the edge of the background. Once done, you can adjust the size as you wish, just keep the aspect ratio squared i.e. length and width the same to ensure you have a perfect circle.
Step 2 – create 2 more pie charts
Copy and paste the pie chart you have just created and position the new chart above it. Repeat so that you have three pie charts on top of each other. You can use the align commands on the Drawing tools tab to ensure that they are vertically aligned and correctly spaced.
We need to adjust each pie so that it is looking at the data from the correct cell. Click on the topmost pie chart. The table should now have lines on it indicating which cell the pie chart is taking its data from.
Drag the blue square from cell H2 to cell F2. The pie chart should update and appear blank. Repeat for the middle pie chart, dragging its blue square from cell H2 to cell G2. Your three lights should now look like this:
We need to adjust the fill of the top and middle lights. It’s easier to do this when there is data in there, so TEMPORARILY, put a 1 in cells F2 and G2. The fill should reappear in the pie charts. Click on the top most pie chart again, press Ctrl+1 to bring up the formatting box and give it a red radial fill.
Then click on the middle pie chart and give it an orange radial fill. Your charts should now look like this:
Once you’ve adjusted your fill, don’t forget to put the calculations back into cells F2 and G2. The fills will disappear again, but that is normal!
Step 3 – Create the background and highlight
To give the traffic lights a real flair, I added a black rounded rectangle behind the three lights. I also added 2 circles as shown below:
The first circle is black with 40% transparency. The second is red with a red 12pt outer glow. When put together they look like this:
I copy / pasted the 2 circles twice and adjusted the glowing circle to be orange and green for the other two “lights”.
All that’s left is to position the three pie charts on top of the background and you have your first traffic light.
You can see that the green light shines brightly, showing that for the product in row 1 we have plenty of stock.
Step 4 – Repeat for the other rows
Before doing anything, click on the home tab of the ribbon and then choose Find & Select > Select Objects.
This will let you select all of the objects that make up the traffic light by dragging your cursor over them. Once done you can press Ctrl+C to copy the traffic light and then Ctrl+V to paste another one. Click on the first “light” and update the cell reference as shown in step 2 above. It should now be looking at cell F3. Repeat for the other 2 lights, for cells G3 and H3. You should now have 2 traffic lights for the first 2 rows.
Keep copying and pasting and adjusting the cell references until you have a traffic light for every row:
And that’s it! You can download the worksheet here.
Hope you find it useful!
User Friendly 
Awesome!!!