Excel traffic lights based on numbers

Hi Thank you for the reply. I define R,Y,G as the status of a particular item, i.e., if there are no issues, G, if there are some issues, Y, if issues are critical then R.

I really would like use the traffic lights as this is the generally accepted standard for reporting - so even if its just one circle with the correct colour that would be fine. It would also be ok to leave cells that have no info as blank.

So you might use your if logic (using countifs?) and then attribute a number where 0 = blank, 1 = G , 2= Y and 3 = R. Finally apply the CF on that value to create the traffic light. Something like = IF (Countifs (Critical, "Yes"), 3, IF (countifs ( issues, "<>")>Treshold, 2, ...

Not sure if that makes sense with your dataset.

So you might use your if logic (using countifs?) and then attribute a number where 0 = blank, 1 = G , 2= Y and 3 = R. Finally apply the CF on that value to create the traffic light. Something like = IF (Countifs (Critical, "Yes"), 3, IF (countifs ( issues, "<>")>Treshold, 2, ...
Not sure if that makes sense with your dataset.

Hi
Thank you for the response - I eventually went the long way of creating new columns with conditional formatting using webdings and colour coding them accordingly. was quite painful but it seems to have worked! thanks again for your time and advice.

We all know that Traffic Lights are great visuals to communicate Project Status. But, we also know that Conditional Formatting for Traffic Lights can be done only through Numbers not through Text.

We need a flexible system that if I enter R (for Red), A (for Amber) and G (for Green), the required Traffic Light should be displayed. (A for Amber can be substituted with Y for Yellow also). Below is a method to achieve the required Traffic Lights.

Note – In this article, I am talking about Traffic Lights but this can be extended to any other Conditional Formatting type.

The Excel file related to this article can be downloaded from RAG_CF_Spotlight.

Method 1 – Without VBA

In this method, you enter a negative number for R, 0 for A and any positive number and in place of numbers, you can display R, A & G along with Traffic Lights. If you do not want to enter numbers but R, A & G only, then jump to method 2 which is a VBA method. (Don't get intimidated by VBA, just follow the steps and nothing else)

1. Let's Assume that your project layout is as above picture. Select the required range in column B > Right click > Format Cells > Custom and put following custom formatting code –

[<0]"R";[>0]"G";"A"

2. Select your range in column B again and Home tab > Conditional Formatting > New Rule > Format all cells based on their values and choose values as given in Red Zone.

(Note – If you don't want to show RAG and only show Traffic Lights, you can check "Show Icon Only" next to Icon Style and below Reverse Icon Order")

THAT'S ALL. You just need to enter any negative number for R, 0 for A and any positive number for Traffic Lights.

Method 2 – With VBA

As mentioned earlier, this method has the advantage of entering R, A or G and display Traffic Lights. You need not enter numbers which is the limitation of Method 1.

1. Save your file as .xlsm 2. ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Double click on your worksheet name

5. Copy paste the below code in this (You may like to replace range B2:C10 as per your requirement)

Home
Excel Course
VBA Course
Excel Functions
1000 Examples
Templates
Project Management
- - Project Management
- - Business Model
- - Business Plan
Tips

For example, if Target is met, there should be a Green light, if actual is closer to Target but not met, there should be a Yellow light and finally a Red light in case Actual is way off from Target.

In the dataset below, instead of looking at the Actual vs Target figures for each salesperson and then finding out if Target has been met, can there be lights automatically indicating performance of each salesperson?

To do it in Excel, here is the answer:

a) Select the data range with "Actual - Target" performance data. Under "Home" tab, click on "Conditional Formatting" -> "New Rule".

b) Select "Icon Sets" in the Format Style field.

c) Enter the criteria for highlighting by selecting Type and value fields. In the example shown below, any value above 0 in the selected range will have Green light.

Similarly, any value between 0 and -500 will have yelow light and anything below -500 will have red light.

d) Click OK. The data range with "Actual - Target" performance data will appear as below.

You can find similar Excel Questions and Answer hereunder

Here the previous and next chapter

Excel Made Easy was founded in 2002. Excel Made Easy is a service to people looking to learn Microsoft Excel or any other spreadsheet SW on the market.

We endeavor to provide the best service and support and to respond quickly to your questions.

Excel Made Easy is a proud sponsor of Dismantle-It.com the site that shows you the belly of your daily objects, Poupounette.com the site of cars loving cat and PowerPointMadeEasy.com the world top reference for PowerPoint Presentations questions and Pet Cemetery Online a site dedicated to your loving Pets.