Dynamic Gantt Chart in Excel

About Dynamic Gantt Chart

Gantt charts are an effective project management tool that helps track project progress, tasks, and resource utilization. The chart is named after Henry Gantt, who invented it around 1910-15.

A Gantt Chart is a visual representation of a project schedule that displays the start and end dates of activities, their dependencies, and progress. It is an essential tool for project management as it enables project managers to plan and monitor their projects effectively.

Over the years, Gantt Charts have evolved into a powerful tool and that can be created using different software tools like Microsoft Excel, Google Sheets, and Microsoft Project.

Dynamic Gantt Chart in Excel

With the same concept in mind, TheDataLabs team has developed a robust and dynamic Gantt Chart in MS Excel that offers a wide range of features to help manage and track project activities.

Dynamic Gantt Chart in Excel
Dynamic Gantt Chart in Excel

Understanding the file structure of Dynamic Gantt Chart in Excel

The Gantt chart has two worksheets – one for corporate holidays and the other for the actual Gantt Chart having all the required features.

image 12

The Holidays worksheet includes a table with three columns for ‘Date’, ‘Day Name’, and ‘Holiday Name’. The table allows users to input corporate holidays for their company, which will be excluded from the computation of activity duration & end dates in the Gantt Chart.

image 13

In the Gantt Chart worksheet, there are several columns, including Activity Name, Planned Start Date, Planned End Date, Planned Duration, Actual Start Date, Actual End Date, Actual Duration, Activity Status, Completion%, and Dates for more than 5 months to show the progress of activities against the planned timeline.

image 14

The ‘Planned End Date’ column is formula-driven and uses the ‘Workday’ Excel function to find the exact End Date after excluding holidays and weekends from the calculation. The ‘Actual Duration’ column is also formula-based and utilizing the ‘Networkdays’ Excel function to calculate the total days between the Actual Start and Actual End date, excluding holidays and weekends.

The Status column is a drop-down menu where users can select either “Pending” or “Completed” to indicate the progress of an activity. The chart also includes a legend that displays four activity statuses: Pending, Not Utilized, On-time, and Extended.

image 15

The Pending status is denoted by a red-colored bar, indicating that the activity has not started and is still pending. The Not Utilized status is shown as a gray-colored bar, indicating that the activity started late and some of the planned days were not utilized. The On-time status is displayed as a green-colored bar, indicating that the activity was completed on time. Finally, the Extended status is denoted by an amber-colored bar, indicating that the activity was completed after the planned end date.

This Gantt Chart also includes a date drop-down list on the top-left corner of the column headers. Drop-down list allows users to highlight a specific date column in the chart using an arrow.

image 16

How to use this Dynamic Gantt Chart in Excel for actual project

Overall, this Gantt Chart is a comprehensive tool that is free to download from our website. Users can remove the dummy details from the columns (A, B, D, E, F, H, & I), and update the actual activity details in the respective columns and first date of activity start period column J. Also, fill-down the formulas where applicable.

image 17

Download the copy of Dynamic Gantt Chart in Excel

You can download the copy Gantt Chart after clicking on the below button. It’s completely free of cost!


YouTube Tutorial on Dynamic Gantt Chart in Excel

Please watch our step-by-step tutorial on YouTube Channel @thedatalabs

If you have any question or feedback, please leave your comment in comment section. TheDataLabs team will reply to you ASAP.


Please enter your comment!
Please enter your name here

Captcha verification failed!
CAPTCHA user score failed. Please contact us!

Recommended Reads

Latest Articles