I created this template for a Gantt Chart in Excel because of the endless searches of me trying to find one when I need it. This Excel Gantt Chart will come in handy for those quick and dirty project plans you need to create and show some visual timelines. I would be very cautious in using this for a long term project with hundreds of tasks and complex timings, this template just isn’t for that. If you are wanting to do something that complex, I would highly recommend looking in to MS Project to accomplish those types of project plans.
Just like always, here is a working copy of the file. Enjoy!
Excel Gantt Chart Template
Features: Create Project Timelines, Milestones, Task Health Indicators with room for over 100 tasks. Also available in daily, weekly, monthly, and yearly Views!
Functionality: Lots of formulas, yea, lots of them, along with many rules of conditional formatting. Little bit of VBA only to expand/collapse columns for viewing purposes only. (You can disable macros and the template still works).
Creating the Gantt Chart
The Excel Gantt Chart Concept
Once you have the above sorted out, use a formula similar to this one below to use against the tasks in the time frame:
The result of the formula will produce a 0, 1, 2, and 3. From this, I will use the result to apply conditional formatting to the cell based on the cell value. It goes something like this:
- Result = 0: solid background with top and bottom border
- Result = 1: solid background with top, left, and bottom border
- Result = 2: solid background with top, right, and bottom border
- Result = 3: solid alternate background color
So while this isn’t a very lengthy article on how to create the Gantt chart in Excel, I hope this helps you out in your journey in how to create one on your own. If you want to use mine, feel free to modify it to your own liking and color scheme and as long as you keep the above in mind you should have little problem doing the update.
Stay tuned for future updates, and the latest revision has been posted to the download link above. Enjoy!
- ID Column (used to set dependencies against)
- Dependency Column, ability to add single dependency against prior task with entering ID
- Budget Hours, used to set the amount of budgeted hours for the task duration (amount of work)
- Actual Hours, used to track the amount of hours worked against a task
- % Comp, which is a calculated column against the budget/actual hours to determine the percent complete. This field is not locked and can be manually typed over
- Added default visibility to include the headings (rows/columns) back to the workbook
- Removed formula bar by default to increase real estate on the page
- Added a weekly view option on the annual plan to show week numbers in the timeline
- Fixed error preventing multi-year view to actually span across multiple years
- Minor bug fixes & formatting changes
- Inserted simple level instructions and links back to articles
- Converted file to macro enabled workbook to allow for VBA functions
- Added expand/collapse buttons to adjust width of columns in gantt chart.
- Minor formatting changes
- 12 Month Project Plan (Daily)
- 6 Month Project Plan (Daily)
- 7 Day Project Plan (Hourly-Whole)
- Added Status/Milestone with color indicators
- WBS Milestones / Task List ID’s
- Time Calculations (Actual, Planned, Used, Balance)
- Resource Identification
- I’ll fill this out later…kind of lazy at the moment.