When our KPI is less than the value in A2, our traffic light will show red.In cell A2 and A3 we have some numerical values and in cells B2:B4 there is some conditional formatting based on these values and our KPI. In this example, our KPI value that drives the traffic light will be in Sheet1 and the behind the scenes stuff that actually makes the lights change will be in Sheet2. Conditional Format Rules To Change Fill Colour Drawing Tools Format tab ➜ Shape Outline then change the colour to a black.Ĭopy and paste this new shape into Excel and we’re done with PowerPoint.Drawing Tools Format tab ➜ Shape Fill then change the colour to a dark grey.I don’t know any traffic lights that are blue, so we will also change the colour from the default blue. This will essentially cut out the circles from the rectangle and leave us with one shape (a rectangle with three holes in it). We can go to the Drawing Tools Format tab ➜ Merge Shapes ➜ Combine. Once we’ve got everything nicely aligned and centered, we are ready to merge the shapes. We can also use the alignment tools in the Drawing Tools Format tab ➜ Align ➜ Align Center and/or Distribute Vertically. PowerPoint has a build in alignment tool that will help with aligning all the shapes which is another advantage to doing this part in PowerPoint vs Excel. Then we can copy and paste the circle for a total of three circles.

We can adjust the size as needed so the circle fits nicely inside the rectangle. Hold the Shift key while drawing the oval shape to lock the ratio and create a perfect circle. There is actually no circle shape, only an oval, but we can use this to create a circle. Add the rectangle shape first, this way it will be layered as the bottom shape. Merging Shapes In PowerPointįirst we will need to add a couple shapes. So in order to create our traffic light shape, we need to head into PowerPoint to use the merge shapes options. But the merge shapes isn’t even found there. There are a lot of hidden commands in Excel that don’t appear in the ribbon tabs, and it’s possible to use them by adding them to the quick access toolbar. But in PowerPoint there’s an extra Merge Shapes option that’s just not there in Excel. When you add a shape in either PowerPoint or Excel and then select it, a new Drawing Tools Format tab will appear in the ribbon. Unlike the magic wand, our traffic light will actually work! When our KPI value changes, our traffic light will change too, depending on what range of values we set it with.

How to add shapes in excel 2016