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

by Kishan Tambralli

Project Plan Template

The excel project plan template has some advanced features that help identify delayed tasks, traffic light indicators, and using predecessors.

    Project Plan Template Excel, Project Plan Template
    Project Plan Template Excel

    Project Plan Template Excel - Features

    • The Excel Template helps in creating a project plan and tracking with a Gantt view.
    • It clearly highlights if the plan's task 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 task's finish date.
    • 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 daily 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 Gantt Chart's coloring is designed using conditional formatting, so there is a limited number of phases that can be created with different colors.
    • The timeline and Gantt are designed for the daily view. Even though there is an option to have a broader view, the timeline works best daily.
    • The project phases cannot be tracked in the plan. For example, there is no way of knowing the 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. Check other dashboard templates here.

    Basics of the Project Plan 

    • In the picture below, 1 is the 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 the number of days for the timeline. By default, it is one day, so it shows the Gantt daily. 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.
    Project Timeline Basics, Project Timeline
    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 because the tasks contain numerous formula and conditional formatting settings that may not work if you try to create them on your own.

    Techno-Tip: Always reuse the existing template structure and tasks by copying and pasting rows or simply renouncing the tasks and changing the dates. It is best to rename the phases and tasks rather than trying to create new ones.

    • 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.
    • The predecessor column should have the task id for the task to finish before the current task starts. This is mandatory except for the first task. If you don't want to use the predecessor, you must delete the contents in the start date, including the formula, and enter your chosen date.
    Creating Project Tasks, Project Tasks, project task
    Creating Project Tasks
    • The start date except the first task is driven by predecessors. The start date will be the end date of the predecessor task plus 1. The end date is auto-calculated and reflected in the Gantt. The calculation for the 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 that will indicate the status of the tasks. If the task is on track, then the signal will be green, 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.