Creating a Project GANTT in Excel

There are so many times when we’ve wished Excel would draw an automated GANTT chart that it is not even funny anymore. Unfortunately, the folks at Microsoft only have tedious workarounds that require using the graph wizard! Trust me – that approach requires you to suffer needless pain with often unsatisfactory results. The graph wizard was never meant to draw a GANTT – period!Excel-Download-Button

If you want an automated GANTT chart in excel for your projects that looks like the example below, then simply download my template by clicking on the download button on the right. Your client / boss / team will love you for it!

Creating a GANTT Chart Using Excel

The template is easy to use; you simply enter the list of projects, their start and end dates; the GANTT will display as soon as you enter the 2 dates for a project. The duration column is also calculated automatically as the number of whole months between the start and end date.

The magic is only in the cells/area that show the GANTT; There is a formula in each cell. I’ve simply used conditional formatting to achieve the coloring based on the result of the formula in each cell. If you would like the bars to display in another color, simply change the conditional formatting.

Hope this helps. If you have any questions, feel free to reach out. Till my next post, be safe and have a great new year and don’t forget to rate my post (use the start rating at the top) !!

Please like & share:

11 thoughts on “Creating a Project GANTT in Excel

  1. Wotiso

    Great Excel template. Would you say there are any advantages of using this over, say, MS SharePoint’s built in features?

    Reply
    1. Sri Prakash

      Simply use the formula: =INT((B1-A1)/7) where A1 contains start date and B1 contains end date.

      The int function simply provides you the resulting weeks as a round number versus a decimal. If you prefer to have the weeks display in decimal simply avoid using the INT function.

      Excel always returns the difference (when you subtract) between 2 cells containing date values as a difference in days. Hope that helps!

      Reply
      1. Sri Prakash

        For this specific spreadsheet, put the formula in D6 as:
        =INT((C6-B6)/7)

        Then copy the same formula down into the cells below D6

        You should be good; if you want me to send you a revised version of the template in weeks, I’ll be happy to. Just send me your email address.

        Reply
      2. Sri Prakash

        hi Randy… WEEKNUM by definition “Returns the week number of a specific date”. So I would not use it as it contextually seems inaccurate for this purpose. But do a little investigation to find out if it is apt or not. Let me know what you find. I would be interested.

        Reply
  2. Pingback: Excel Gantt Chart Template | American Academy of Learning

Leave a Reply

Your email address will not be published. Required fields are marked *

2 × one =