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

Availability: Tested in Microsoft Excel 2007, 2010, 2013
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).

Excel Gantt Chart 784.66 KB 97401 downloads

Gantt Chart Template for Microsoft Excel 2010 ...

Creating the Gantt Chart

The method to creating the Gantt Chart in Excel was relatively easy to put together. To start, I decided to base it off the start and end date of a task and determine how to create the visual block of the task. My thoughts quickly went to creating a formula based off the dates and the result of the formula dictating what conditional format I wanted to apply.

The Excel Gantt Chart Concept

I won’t go into detail about how to create the information for your tasks, as that is ultimately up to you as long as you use a start date and an end date in your task. After that, you need to create a span of dates covering the time frame of your project on a day by day row. You can set the dates to however you want, meaning it can be daily, weekly, monthly, etc. In my example, I did a daily span covering 4 years. I like the daily date spans as an approach, as you can make the time bars much easier on the eyes.

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.

Change Log

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.