You cannot create a Gantt chart in Excel in direct way. But don’t worry, you can make an advanced level Gantt chart in Excel modifying the Stacked Bar chart. This quick tutorial will guide you to create a basic type Gantt chart in some simple steps. If you don’t know the basic rules of making a chart, read this article: How to Make a Graph or Chart in Excel
Gantt chart is used basically in project management. In the worksheet, you see a simple Gantt Chart, created using the piece of data on the left of the chart.
Parts of a Gantt Chart
The Horizontal Axis (it is also called Value Axis) represents the total time span of the project. Each Bar in the Gantt Chart represents the duration of a task. The project manager can easily identify the overlapping tasks of the Project. So Gantt chart is very important in project management.
In the data table, column A contains the task name, column B contains the start date of the task, and column C contains the duration to finish the task.
Data that we shall use to make the above Gantt Chart
We shall use the following data to create the Gantt chart. You can download the exercise file from the download section too.
|Print and Mail Questionnaire||18/05/2013||9|
|Distribute Draft Data||28/06/2013||2|
|Distribute to Board||12/07/2013||2|
[Video] How to Make a Gantt Chart in Excel 2013
Step by step procedure to make a Gantt Chart
Step 1: Creating a Stacked Bar Chart
Open a blank worksheet and copy-paste the above data. Now select a cell within the data. Then click on the Insert tab, in the Charts group of commands click on the Insert Bar Chart drop-down, from 2-D Bar section, I select Stacked Bar chart.
By default, the following chart is created. I have selected the Legend in the chart, you also select it and delete it. Make the chart a little bit wider to get more space to work.
Step 2: Reversing the Category Axis order
On the Category Axis(in the chart the Vertical Axis), the “Board Meeting” takes a position at the top, but in project management data, Planning Meeting is topping, I double click on the Category Axis, Format Axis task pane appears. In the Axis Options tab, select the Categories in reverse order option.
Step 3: Changing Labels Position of Horizontal Axis
When you reverse the Category order, the Horizontal Axis(value axis) also changes its location. Click on the Category Axis, again in the Format Axis task pane, and under the Axis Options tab, contract the Axis Options, and extend the Labels, select Label Position at High.
Observe the data of the chart: “Board Meeting” is the last data. So, “Board Meeting” is regarded as a high position. When I have selected Label Position High for Horizontal Axis(value Axis), in the following chart you see: Horizontal Axis is taking a position on Board Meeting side, I mean the High side. Observe the following chart closely to understand what I am saying. This is an important concept you have to understand.
You see the Horizontal axis starts on date 07/03/2013. The date 07/03/2013 is selected by Excel. You did not input it. Every chart starts from a value. Excel by default set that value, but you can set from which value a chart will start. Look at the above chart, from the top, the first Blue Bar is representing value 03/05/2013 of Start Date data series of point “Planning Meeting” and the Red Bar is representing value 3 of Duration data series of same point “Planning Meeting”.
As the Horizontal Axis starts from the date 07/03/2013, so, this blue zone actually shows the days between 07/03/2013 and 03/05/2013. In the same way, you can observe the other data series values in the chart. Just move your mouse pointer over a Bar(in this chart either Blue or Red bar) , Excel will show you three things:
- At first the Data Series name. In the above chart data series is: Start Date.
- Then the Data Point name. In the above chart, you are seeing the data point is Planning Meeting.
- And finally the Value of the Data Series. In the above chart Value: 03/05/2013 is showing.
Step 4: Finding out the days between two dates
Let’s find out how many days are there between these two dates (between 07/03/2013 and 03/05/2013). You know Excel stores dates and times as a number representing the number of days. Date 1st Jan 1900 is represented with serial number 1; date 2nd Jan 1900 is represented with serial number 2 and so on. How will you find out the serial number of the date? The strategy is very simple. Just put a date into an Excel cell, then change the cell format to General, you will get the serial number of relevant dates. Using this method, I have found that:
- The serial number of date 7/3/2013 is 41340
- And the serial number of date 3/5/2013 is 41397
- So there are total of 57 days are there between dates 7/3/2013 and 3/5/2013.
So in the chart, for the Board Meeting data point, the blue bar is actually representing number 57 and the Red bar is representing number 3. So this is how this chart is working.
Step 5: Change Minimum, Maximum, and Major values of Horizontal Axis
Now our first task of the Project “Planning Meeting” starts from the date 03/05/2013, so our horizontal axis will start from this date. I double click on the horizontal axis, Format axis task pane appears. In the Axis options, the minimum value is set as 41340; we know that this “41340” is actually representing the date 07/03/2013. I set the minimum value as 41397, the representing number of date 03/05/2013. The maximum value is automatically set, but I change it to 41480. And I change major units to 7.
Step 6: Change the date format
I want to change the number format, in the format code, I type: dd/mmm.
The dates are more meaningful in this way.
Step 7: Using No Fill for the Blue Bars to make them invisible
Now select the blue bars. In the chart below, you see all the Blue Bars are selected.
In the Format Data Series task pane and in the Fill and Line tab, select No Fill. You can also use the Fill Color drop-down of Home Ribbon.
Now, look at the chart below. You see, we have already got a Gantt Chart.
Step 8: Formatting the Gantt Chart
Now the above chart needs some formatting.
- Click on the Duration data series(select any of the Red Bars), in the Format Data Series task pane, and under Series Options, change the Gap Width value to 30%. It will decrease the Gap between the Category Axis position.
- Format Data Series → Fill & Line → Fill → Solid Fill → Use color for the Data Series.
- Format Data Series →Effects → Expand 3-D Format → Top Bevel drop-down → Select Angle type Bevel.
- Show the Data Labels for this data series. To do that: click on the Chart Elements icon on the top-right corner of the chart. From the menu select the Data Labels option.
- Change the Chart Title content to Project Schedule and make the Chart Title bigger in size.
- I want to show Horizontal grid lines. Again Chart Element icon → Move your mouse over the Grid Lines option, → A little right arrow appears, click on it → From the drop-down: select Primary Major Horizontal option.
- You can use some different background colors for your chart area and you can also make the grid lines dashed. It is up to you.
Output: Here goes the Gantt Chart we have made so far
More Resources on Excel Gantt Chart
You might want to learn more about Excel Gantt Chart. Here is a reference that you can use to learn more about Gantt Chart Templates:
- You will get a good number of Gantt Chart Templates in this link.
So, you see making a Gantt Chart in Excel 2013 is an easy job. Just you have to practice and understand the concept of creating a chart. The rest is easy.
Any Question on how to make a Gantt Chart in Excel 2013? Feel free to ask in the comment section. I will be very happy to hear from you and learn more in the way.
Download Working File
Download the working file from the link below: