Creating an Excel Gantt Chart

Mar 01

Creating an Excel Gantt chart. #free #Gantt #Excel #MSExcel |

Recently at work, we just started on a new project and wanted to map out all the tasks in a Gantt chart format. For those of you who are not familiar with Gantt charts, a Gantt chart simply illustrates a project schedule in a visual form, making it easier for users of the Gantt chart to follow the task summary.

Why use Microsoft Excel to create a Gantt chart?

In the market, there are several software which are developed to specifically produce Gantt charts such as Microsoft Project. So why then do we use Microsoft Excel to create a Gantt chart? Here are 3 reasons why I feel Excel Gantt charts are superior as compared to using specialized software:

  • Lower cost to get started. Not all companies have access to those specialized software to create Gantt charts.
  • Easy to maintain. Creating an Excel Gantt chart makes it easier to maintain as you can add new tasks from any computer that has Microsoft Excel installed.
  • Easy to use. Users of Excel Gantt chart find it easier to navigate as most people are familiar with Microsoft Excel.

However, do note that we can only create a lightweight Gantt chart in Excel. If you do require a lot of details with dependencies and resource allocation features in the Gantt chart, an Excel Gantt chart may not be suitable for your needs.

Preparing the Gantt Chart

To build a Gantt chart, we first need to prepare the following table with information of our project. Once you have created the headers below, select the headers and press Ctrl+T to format the table as an Excel Table.

  • Tasks: here you can add short description of the tasks (whatever that is in here will be displayed beside the task later in the Gantt chart.)
  • Start_Date: task start date
  • End_Date: task end date
  • Duration: is given by the formula =[@[End_Date]]-[@[Start_Date]]
  • %Complete: is an arbitrary percentage of how much you think the task has progressed.
  • Days_Completed: is given by the formula =[@Duration]*[@[%Complete]]
  • Days_Remaining: is given by the formula =[@Duration]-[@[Days_Completed]]

Once we have populated the Excel Table with some data (like the one above), we can go on to create the Gantt chart.

Creating an Excel Gantt Chart

Step 1: Select the columns Tasks, Days_Completed and Days_Remaining together while holding on to the Ctrl key.

Step 2: Go to INSERT | Charts | Bar Chart icon | Stacked Bar. You should get something that look like this.

Step 3: Click on the chart. Go to {CHART TOOLS} DESIGN | Data | Select Data.

Step 4: In the Select Data Source window, click on Add under the Legend Entries.

Step 5: In the Edit Series window, for Series name select cell B1 which is the Start_Date. The field should be populated with =Sheet1!$B$1.

Step 6: The Series values should be cells B2 to B7 (which is the last entry of our data). Remember to delete the {1}. The field should be populated with =Sheet1!$B$2:$B$7. Press OK.


Step 7: You should be back at the Select Data Source window. Select Start_Date and click the up arrow to move the Start_Date to the first item. Press OK.

Step 8: You now have some resemblance of a Gantt chart. Finally we move on to the formatting of the Excel Gantt chart.

Formatting our Excel Gantt chart

Step 9: Notice that the task names are in descending order in the chart. Select the task names, right click and select Format Axis. Under AXIS OPTIONS, select Categories in reverse order.

Step 10: We need to now hide the Start_Date series in the Excel Gantt chart. Click on the Start_Data bars (make sure all the bars are selected), right click, under Fill select No Fill.

Step 11: In the legend, click on the Start_Date twice until you only select that item. Press the Delete key to delete.

Step 12: Select the Chart Title and delete as well.

 Step 13: Right click on the dates, and select Format Axis. Under AXIS OPTIONS, set Bounds: Minimum to be the Monday before the earliest start date of all your tasks. In my example above, my earliest start date is 28 Feb 2017 (Tuesday) and so I will thus enter 27 Feb 2017 (which is a Monday) and press ENTER. Notice that the date entered will be auto converted into Excel’s numerical date.

Step 14: Under Units: Major, set it to be 7. Hence we see that each major axis now represents a new Monday on the Gantt chart.

Step 15: This is what you will get. Just a little bit of coloring left to complete our Excel Gantt chart.

Final Coloring

Step 16: Select the Days_Completed bars and fill them with dark green. Then select the Days_Remaining bars and fill them with  light grey.

Step 17: Select the categorical axis (y-axis) and go to HOME | Font | Font Color and select grey. Repeat the same for the date series (x-axis) and use the same color.

Finally, the much awaited Excel Gantt chart

Yes, we finally finish our Excel Gantt chart, and it looks simple and pleasing to the eye. When you update the %completion of each tasks or add new tasks, the chart will automatically update due to the Excel table feature.

Now you are in full control of the project management process!


Till then,

Defeat Excel Gantt chart.