The Gantt chart is an excellent tool to visualize the sequence in the activities of a project, as well as their duration in days from a set start date. This type of diagram is widely used in project planning and development to facilitate resource allocation.
In today’s market there are specialized software tools for creating and editing Gantt charts, as is the case with Microsoft Project, but there will surely be times when project participants will not have access to these tools so it will be essential share the details of a project using other software.
An alternative is to use Excel to create a Gantt chart that contains basic information, but is very useful for making decisions in the development of the project. So, this time we will do an exercise showing the steps necessary to create a Gantt chart in Excel.
When developing each of the steps described in this article, we will have a table with basic information about each of the project activities as well as an Excel bar chart that will adequately represent the Gantt chart. In the following image you can see an image of the final result.
Before starting our exercise I must make a couple of things clear. First of all, you should keep in mind that this is a basic Gantt chart in Excel but you can add additional functionality later if you wish. You should also remember that Excel was not made for the purpose of creating these types of diagrams, so certain things that are simple to implement using specialized tools could be much more complicated to implement within Excel.
How to create a Gantt chart in Excel
As you can see in the image in the previous section, our data table has information about each of the project activities such as: the name of the activity, the start date, its duration and the end date. You do not need to manually enter the date in the End column, as you can calculate it by adding a sum of the Start column and the Duration column.
You must pay special attention in the creation of this table since it will be the data source for our graph. Enter this information very carefully and when finished we will be ready to insert the graphic.
Once we have the data table ready, we must click on an empty cell on our sheet and then go to the Insert tab> Charts> Bar and then select the option which is the second icon within the group as seen in the following image.
You will notice that this action will insert a blank chart, but we will not have to worry about that at this moment since little by little we will add each of the elements of our Gantt chart.
Select data for the Gantt Chart
Now that we have our empty bar chart embedded in the sheet, we must right click on the chart and select the option .
The dialog box will be displayed immediately and it will be here where we will indicate the range of cells that contains the information about the activities of our project.
If you are not used to creating Excel charts in this way, I recommend doing each of the steps described below with great care since the proper functioning of our Gantt chart will depend on this.
We will use this dialog box to add two data series, and as you are probably imagining, we will do it through the Add button. So, let’s start by clicking on that button and another small dialog box will open with the title .
In this new dialog box we will focus our attention on the box where we will indicate the values of the column from our data table, which for our example will be the range B2: B6.
Once the selection is made, we must click on the Accept button and the first series of data will have been created. Now we need to modify the horizontal axis labels for that series so you should focus your attention on the right panel of the dialog box and click on the button found below the section :
This will open the dialog , and you must select the column from our data table and that is the range A2: A6. Finally you must click on the OK button.
In this way we have added the first data series with information on the start of each activity and now we have to add the second data series that will contain information on the duration of each activity.
At this time you will have the dialog box on the screen , so you will have to click on the Add button and in the dialog box inside the box , you must select the data from the column which in our data table is the range C2: C6.
Click the OK button to finish with the creation of the second series but, as with the previous data series, it will also be necessary to edit the labels of the horizontal axis so you must select the second series from the left panel and then click on the Edit button located in the right pane to open the dialog box just as we did with the first data series.
For the axis labels of the second data series we will also choose the values of the column that is, you must indicate the range A2: A6. Finally you will have to click on the OK button to close the dialog box. and finally click the OK button again to close the dialog .
After having added both data series we will have a graph like the one shown in the following image.
Right now our chart looks like any other bar chart, but we will continue to work to add the detail needed to create our Gantt chart in Excel.
Format to the vertical axis of the graph
Our next step will be to format the axes of the graph since at this time the activities are shown in reverse order, that is, at the top of the graph is activity “E” and at the bottom is activity “A ”But we wish to reverse that order. To achieve this change you must right click on the vertical axis and select the option .
The dialog box will appear and within the section you should make sure to check the option
When you close the dialog box, you will have ordered the activities within the graph in the same way as they appear in the data table.
Remove padding from the first data series
If you carefully analyze the current state of our graph, you will see that the red bars adequately represent the start, duration and end date of each of the activities.
The function of the blue bars is to help us correctly locate the start of the red bars, that is, they push them to the right until they are correctly located on the start date. So, strictly speaking, it is not necessary for the blue bars to be visible in our graph so we will proceed to “hide” them.
To visually hide the blue bars we will remove their fill color and for that you must select any of the blue bars and right click to select the option
The dialog opens and you should make sure to go to the section to select the option :
When closing the dialog box, the blue bars will have visually disappeared from our graph. To give a little more space to the bars we can remove the legend from the graph and we can do this by clicking on said legend and selecting the Delete option.
After applying these changes you will have a graph like the one shown in the following image in which only the red bars are shown and which occupy a greater percentage of the graph area.
Format the horizontal axis of the graph
Our last step in creating a Gantt chart in Excel will be to format the horizontal axis so that there is not as much free space to the left of the bars. We will achieve this by indicating to the graph the numerical value where the horizontal axis should start.
The trick of this step is to properly obtain the numerical value of the start date of the first activity to start, which for our example is activity A. We can easily know this numerical value by right clicking on cell B2, which has the start date for said activity, and later we choose the option and temporarily change the Category option to as seen in the following image.
Without clicking on the Accept button you can see in the section that the numerical value for the date 01/01/2011 is the value 40544. You must write down this number because we will place it as the starting value in our graph, so close the dialog box clicking Cancel and then you must right click on the horizontal axis to select the option :
The dialog box will appear and within the Axis Options section you must enable the option for the fixed minimum value and place the number that we have just obtained, which for our example is the value 40544 and which corresponds to the start date of activity A.
By setting the minimum value of the horizontal axis we will have eliminated the white space that existed to the left of the bars.
But before closing the dialog box we will modify the labels of the horizontal axis a little. For that you must select the section and within the Category box you must choose the option and in the text box you must enter the value and finally press the Add button. This will give better visibility to the dates displayed on the horizontal axis since it will remove the year and only the day and month will be shown.
Close the dialog box by pressing the Close button and you should have a diagram identical to the one shown at the beginning of this tutorial.
This is how we have finished our exercise to create a Gantt chart in Excel. As I have mentioned before, it is a diagram that shows basic information but that will be enough to have a clear vision of the duration of the activities of a project.
It is entirely possible to add additional functionality to this Gantt chart but that will be a topic for some future publication. For now you can download working file used in this article and use them as a template to create your own Gantt charts in Excel.