Excel Traffic Light Using Conditional Formatting - Free Download
What is Excel Traffic Light?
The global standard for a traffic light is that red means stop, and green means go. Due to this, the inherent conditioning is that red is bad, green is good, and orange is somewhere in between. Red usually denotes that there is a problem in the information which is presented, green that it is on track, and orange that it is at risk. These colors are used in many reports, Excel allows these colors to be used in its reports, in order to help the viewers focus on the problematic (red) areas. We can do these excel traffic light using Conditional Formatting in excel, which allows the user to customize the parameters for the colors.
Download all our templates |
| Excel Traffic Light using Conditional Formatting |
How to Create Excel Traffic Light?
In order to use the traffic lights in an Excel table, the following steps are required –
Step 1 : Insert Values into Excel Table
Insert the numbers into the Excel table, along with all the other relevant information. For example: the number of truck sales in 2016 by the sales people of a car dealership. Column A includes the names of the sales people, and column B the numbers of trucks they sold in 2016 –
 |
| Excel Table for Traffic lights in Excel |
Step 2 : Conditional Formatting in Excel
Choose the numbers in column B (without the header), and click on “Home -> Conditional Formatting -> Icon Sets” and then choose the traffic lights icons –
 |
| Conditional Formatting Excel for Traffic Light |
Step 3 : Traffic Lights in Excel
Once this is completed, the default traffic lights will appear next to the numbers in column B. The default parameters for the traffic lights are –
- The top 33% of the numbers (67% and up) have the Green traffic light next to them.
- The middle 34% of the numbers (less than 67% but more than 33) have the Yellow light next to them.
- The bottom 33% of the numbers have the Red light next to them.
 |
| Default Excel Traffic Light |
Customizing the Traffic Lights in Excel
Choose all the numbers in column B (without the header), and click on “Home -> Conditional Formatting -> Manage Rules”. The following menu will appear –
 |
| Conditional Formatting Excel Traffic Light |
Setting Up the Rules for Conditional Formatting
Click on the “Icon Set” so that it is colored in blue as in the picture above, and then click on the “Edit Rule” button. The following menu will appear –
 |
| Conditional Formatting Excel Rules |
In the top half of the menu (“Select a Rule Type”) leave the default as is (“Format all cells based on their values”). In the bottom half of the menu the following customization can be made –
- The “Format Style” mustn’t change from the default “Icon Sets”, otherwise the traffic lights will disappear.
- Clicking on the “Reverse Icon Order” button will cause the red light to appear at the top row, and the green one at the bottom. If the rest of the parameters stay the same, then the top third numbers will be red, and the bottom third green (no change to the yellow numbers).
- The “Icon Style” drop-down menu allows the user to change the appearance of the lights. There are many icons to choose from.
- Ticking the “Show Icon Only” box will cause the numbers to disappear from column B, and only the traffic lights will appear in the column.
Setting up the Parameters in Traffic Lights
Changing the parameters of the traffic lights requires in the bottom section of the menu, under “Display each icon according to these rules” –
- Each Icon can be changed by choosing one from the drop down menu
In the greater than or equal to drop down menu it is possible to choose whether either “>=” or only “>”. - In the “Value” field the parameter for which the first icon will appear can be chosen.
- In the “Type” drop down menu there are 4 possible parameters to choose from –
Number:
An absolute number.
Percent:
The top X% of all of the numbers in the column.
Formula:
This requires writing a formula, and the numbers which meet the requirements of the formula will have a traffic light next to them.
Percentile:
Only the numbers which will be included in the percentile chosen will have a traffic light next to them. - For example: if the first icon is a green traffic light (the default) and the following parameters are chosen: “>=”, the value field is “200” and the type is “Number” then all of the numbers in column B which are 200 or above will have the green traffic light next to them.
 |
| Parameters in Excel Traffic Light |
Best Practices for Creating Excel Traffic Light
 |
| Best Practice to Create Excel Traffic Light |
- If only one table appears in the spreadsheet, and the parameters apply to all of it, it is recommended to choose “This Worksheet” in the drop-down menu (after clicking on “Home -> Conditional Formatting -> Manage Rules”. This will ensure that all of the values in the table will adhere to the same parameters.
- Try and limit the number of icon sets to one per spreadsheet.
- The lights are dynamic, so changing the numbers in the column may cause the traffic light to change automatically.
PMO Kick Off Meeting Template
Project Kick Off
You have defined the strategy and objectives of your Project Management Office (PMO). You have assembled a team to implement it. You even wrote a PMO Charter. You are ready to go and start changing the complex and dynamic universe of projects in your organization. You have plenty of work to do, would love start issuing nice monthly portfolio dashboards right away and seeing stuff happen in your PMO. You can feel the smell of change in the air. This is a serious moment, the PMO is ready to kick-off. |
| PMO Kick Off Meeting Template |
Running a PMO kick-off meeting might sound like a light and fluffy thing to do, however it a powerful way to get people’s attention to the PMO and ensure that expectations are aligned regarding what the PMO is, why it is needed, and how it is going to work. It is critical not to neglect it or rush it.
In fact, several reports claim that many PMOs fail in achieving their objectives due to lack of sponsorship and there are still many cases of abandoned PMOs because of poor engagement with their user community. So, although you might be in a hurry to show your PMO-love, get it off to a good start first with a PMO kick-off meeting. It is a fantastic opportunity for the PMO management team to start building relationships with project teams and other stakeholders. After all, no one destroys what one helps to build, right?
What is for PMO kick off meeting?
Implementing a PMO is no easy-peasy work. Yet, with a quick but effective PMO kick-off meeting you can leverage a range of benefits to support the PMO implementation:
- Get everyone on the same page:
present the PMO. No misunderstandings, no “I-have-no-idea-what-this-PMO-thing-is” anymore. With a kick-meeting, attendees gain a clear view on the objectives, scope, and services provided by the PMO. - Gain sponsorship and commitment: it’s easier to gain sponsorship for the PMO’s work and commitment from the team when roles and responsibilities are explicit and visible to all.
- Build momentum for work to start:
when you walk out of the kick-off meeting, visibility of the PMO is at an all-time high. Make use of it to keep the ball running. - Get the PMO team energised on the work ahead: by bringing the team together and sharing the PMO vision and roadmap as a team, a PMO kick-off meeting enables the creation of a cohesive, successful, high-performing PMO team.
OK, I’m convinced but When should I do it?
As early as possible. Make no mistake: your first challenge will be to find a date (and likely a meeting room available) that works for everyone, so try to schedule the kick-off meeting while you are still working on your PMO Charter, allowing yourself enough time for preparation. The PMO Kick-off meeting marks the start of the PMO implementation project hence you will want to make it right and ensure as much attendance as possible.
Right, and Who to invite for PMO Kick off meeting?
This will much depend on your organizational structure, however, it is advisable to have at least the PMO Sponsor, the PMO team who is going to carry out the work, and representatives from the different user groups, such as Change Management, Resource Management, Finance, Project Managers, and other areas of the Business deemed relevant. In summary, anyone who can fit the role of “PMO’s client”.
What to cover in the PMO Kick Off Meeting Agenda?
A PMO implementation should be taken as seriously as any other project, meaning that the kick-off meeting requires preparation and a structured agenda. A great kick-off is always the result of great preparation. An example of an agenda is:
- Introductions and Setting the Scene: what is the context for the PMO implementation?
- PMO Vision and Objectives: what do we aim to achieve?
- PMO Benefits: why are we doing this?
- PMO Scope: what are we doing?
- PMO Roadmap: how are we going to make this happen?
- PMO Team Roles and Responsibilities: who is doing what?
- PMO Communication Plan: how are we going to communicate our progress?
- PMO Ground Rules: how are we going to work together?
- Next Steps: how do we keep momentum?
Some final Hints and Tips
- Make it brief but exciting: no one likes long boring meetings. Try to keep the entire kick-off meeting as brief but impactful as possible. Usually, 1 to 2 hours is enough.
- Focus on the big picture: establishing clarity and excitement among the PMO team and stakeholders is your mission for the kick-off. Don’t worry too much about the details at this stage, you’ll have plenty of time after the meeting, when the party starts.
- Don’t be afraid to answer questions: allow sufficient time at the end of the meeting for participants to pose questions. It is important that people feel comfortable and are clear on what was presented during the meeting.
- Summarize key points and finish with a smile: do a quick recap focusing on the important points and close the meeting in a positive and enthusiastic way. Give people a reason to talk about the PMO implementation even after they leave the meeting room.
Big journeys start with simple steps and by investing the necessary time in preparing the best PMO kick-off meeting ever, you can set a winning course and tone for the rest of the PMO implementation project. After all, the PMO kick-off meeting is much more than a ceremony, it’s your opportunity to shine and get everyone excited about the PMO, do not waste it!
