Recent Blog Posts

March 13, 2016

How to Create a Project Plan in Excel - A Template using Gantt Chart & Traffic Lights

Project Plan Template

The excel project plan template has some advanced features which help in identifying delayed tasks, traffic light indicators and using predecessors.
Download This Template Now !
project plan template, Project Plan Template Excel, how to create a project plan in excel
Project Plan Template Excel

Project Plan Template Excel - Features

  • The Excel Template helps in creating a project plan and tracking with a Gantt view.
  • Clearly highlights if the task in the plan is on track, behind or delayed using traffic lights.
  • Each task can have a predecessor for dependent tasks which will auto-populate the start date.
  • PM needs to enter only the start date and effort. The finish date is auto calculated by the planning template. 
  • When calculating the finish date only weekdays are considered.  
  • The template auto calculates the % Plan.
  • The start date of the timeline is configurable for the entire plan.
  • There is an option to enter up to 20 holidays in the config section of the template. 
  • When a day is marked as a holiday it is not counted in a calculation for a finish date of a task.
  • There is an option to enter % complete ( % Act) in the plan. 
  • The status column is a traffic light with green, red and amber depending on the task status.
  • When a task starts on a weekend the task is highlighted in red.
  • The plan has a Gantt View on a daily basis and displays the date and day of the week.
  • The Gantt View is designed to show tasks in a different color for each project phase in the plan.
  • The Gantt View timeline in the plan can be changed to show the view over 2 or more days.
  • The current date is highlighted in blue on the timeline for easy task tracking.

Some limitations of the Template

  • As the template is built on excel adding tasks is a manual step in the template.
  • The coloring of the Gantt Chart is designed using conditional formatting so there is a limited number of phases which can be created with different colors.
  • The timeline and Gantt are designed for the daily view. Even though there is a option to have a broader view the timeline works best on a daily basis.
  • The project phases cannot be tracked in the plan. For example, there is no way of knowing start date and end date of the Requirements Phase or Build Phase. 
  • Individual leaves cannot be factored into the planning process.
  • Cannot help with tracking multiple projects using excel.
  • Does not give an overall view like a project management dashboard.

Basics of the Project Plan 

  • In the picture below 1 is Project name
  • 2 is Start for the view - Not necessarily the start of the project but the start date of the timeline view. 
  • 3 is Timeline view setting - This is a number of days for the timeline. By default it is one day so shows the Gantt of a daily basis. Please note that the broader the timeline number the less accurate the timeline. 
  • 4 is the project timeline. 5 - is today's date which is highlighted as blue in the template.
  • The project plan allows up to 20 project holidays.  The holidays can be added under the data tab in the template.
Download This Template Now !

Project Plan Template Excel, project plan templates
Project Timeline Basics

How to create Phases and Tasks

It is recommended that when you want to create a new phase or task you should copy the existing phase or task. This is important to do this as the tasks contain numerous formula and conditional formatting settings which may not work if you try to create them on your own.

Techno-Tip: Always try to reuse the existing template structure and tasks by copying and pasting rows or simply rename the tasks and change the dates. It is best to rename the phases and tasks rather than trying to create new.
  • Each task has a task ID(#), Task Name, Resource Name, Predecessor (Pre), Start, Finish, Effort ( Days),  Progress so far (% Act), Planned Progress ( % Plan) and Status. Some of the fields like task name, resource name are self-explanatory. 
  • Task ID should always be sequential and unique across the project.
  • Predecessor column should have the task id for the task that should finish before the current task starts. This is mandatory except for the first task. If you don't want to use the predecessor then you must delete the contents in the start date including the formula and enter the date of your choice.
project plan templates, project plan template excel
Creating Project Tasks
  • Start date except the first task are driven by predecessors. The start date will be end date of the predecessor task plus 1.  End date is auto calculated and reflected in the Gantt. The calculation for end date will consider only weekdays and ignore the holidays. DO NOT MODIFY THE END DATE MANUALLY.
  • The Effort is the total effort required to complete the task. This is the effort and not duration. % Act is the Actual progress till now. This value should be coming from the project team members. % Plan is ideal % complete. The % Plan is AUTO CALCULATED and should not be changed. 
  • Status is the column which will indicate the status of the tasks. If the task is on track then the signal will be green, will be yellow if the task is behind by less than 5 % and red if the task is delayed by more than 5%.
We recommend that you check out our collection of project plan templates using Excel and MS Project.

Download This Template Now !

No comments