Useful Resource - The Basics: How To ...: Create an automatically updating Excel Traffic Light Indicator

WHAT is an Excel Traffic Light Indicator & WHY have one?
Also known as RAG indicators (Red Amber Green), the traffic light "Andon" is a great visual management indicator for giving an immediate impression of how things are going. This page suggests an easy way create such traffic light indicator for your reports for spreadsheets created in Microsoft Excel. The instantly recognised symbol of a traffic light can provide an immediate impression of three distinct meanings. E.g.

• Red Status - "You need to fix something"
• Yellow/Amber Status - "You may be in control but should be careful"
• Green Status - "Everything seems to be going well, now look forward & see how you can make it even better!"

Download FREE! My Microsoft Excel Traffic Light Indication Tool. Edit it to your needs.

As a manager, director or business ownwer, knowing the health of a business, financial position, or process is key! In the digital age, generating such reports can be instant yet despite this capability, many businesses expect their employees to figure out how to do this without any training on the tools. My Lean Thinking Sensei, Peter Bruch: Works Director of Tews Engineering & trusted programmes advisor Evgeni Vachkov: CEO Genixit always remind me that having metrics are vital to knowing where you are in relation to the context of your goals. Well now, if you're one of these employees, you're in luck! Download FREE! My Microsoft Excel Traffic Light Indication Tool. Edit it to your needs.

I like the idea of this, HOW can I persuade others to try using this tool?
An equivalent analogy I often use is driving from one place to another using a sat nav. Firstly you need to plot in your current position so that the sat nav can plot a course towards your goal. As you start to make your way to the next destination, the sat nav is able to indicate how far you have travelled and how much further you have to go; including expected travel times. Whilst travelling, if due to travel diversions, etc, you end up taking a different route, the sat nav is often able to continue directing you towards your goal, and able to continue providing the metrics of distance and time left to travel.


Download FREE! My Microsoft Excel Traffic Light Indication Tool. Edit it to your needs.

HOW to create this in Excel
Operation 1: Three standard cells are used to act as the three lights. Resize the cells to provide the required effect aiming for three squares, seperating by two rectangles.

Operation 2: Use the normal cell fill to colour these cells a light grey. Use the font colour tool to colour text to the same grey.

Operation 3: Use the standard Excel drawing tools shapes to create the traffic light image. First use the "doughnut" shape for around the lights and then use rectangles to form the traffic light shape.

Operation 4: Designate where in the spreadsheet you wish to store the data value which will trigger the traffic light.

Operation 5: Designate where in the spreadsheet you wish to store the data values which will be used to set the ranges when comparing the main data value.

Operation 6: Use the following algorithm syntax within the grey cells use as the lights. Replace the cell references with your own. In this example, the main cell from Operation 4 is F5. From Operation 5: cell I9 provides the Green light boundary; cell I5 provides the Yellow Light boundary; anything else is for the Red Light.
=IF(F5>=I9,"Green",(IF(F5>=I5,"Yellow","Red")))

Operation 7: Apply conditional formating to colour the light cells and text to the appropriate traffic light colour.

Download FREE! My Microsoft Excel Traffic Light Indication Tool. Edit it to your needs.

Next up, I'll show you how this tool can be used together with tables of figures, and through use of some simple macros have the traffic light change accordingly.