How to Make a Gantt Chart in Excel [Video Tutorial]

You cannot create a Gantt chart in Excel in direct way. But don’t worry, you can make 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.

How to Make a Gantt Chart in Excel 2013 Img1

Basic Excel Gantt 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.

Note: One important thing is: column A must not have any heading, otherwise column A and column B both will be used as the category axes. OK, let’s start creating this Gantt Chart from scratch.

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.

Start DateDuration
Planning Meeting03/05/20133
Develop Questionnaire04/05/201311
Print and Mail Questionnaire18/05/20139
Receive Responses21/05/201315
Data Entry21/05/201318
Data Analysis08/06/20134
Write Report14/06/201312
Distribute Draft Data28/06/20132
Solicit Comments29/06/20135
Finalize Report05/07/20136
Distribute to Board12/07/20132
Board Meeting20/07/20132

[Video] How to Make a Gantt Chart in Excel 2013

Click here to read the transcript of the above video.
A Gantt chart is a horizontal type bar chart. Gantt chart is used basically in project management. By default Excel does not support any Gantt type chart. But you can create advanced level Gantt chart in Excel. In the worksheet, you see a simple Gantt Chart, created using this piece of data. The horizontal axis represents the total time span of the project, each bar 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. 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. One important thing is: column A must not have any heading, otherwise column A and column B both will be used as the category axes. OK, let’s start creating this Gantt Chart. Same values are in the second worksheet. I open it. I select a cell within the data, Insert tab, in Bar chart drop down, I select Stacked Bar chart. I delete the legend and make the chart a little bit wider. On the category axis, the “Board Meeting” takes position at the top, but in project management data, Planning Meeting is topping, I double click on the category axis and in the Axis options, I reverse the category order. When I reverse the category order, the horizontal axis also changes its location, I click on this axis, in the axis options, and then in Labels, I select Label Position at high. You see, Board Meeting is at the high position, so the axis labels take position here. You see the horizontal axis starts from 07/03/2013, so, this blue zone actually shows the days between 07/03/2013 and 03/05/2013. Let’s find out how many days are between these two dates. You know Excel treats dates as numbers. 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 57 days between these two dates. So this blue range is actually representing number 57 and this red zone is representing number 3. So this is how this chart is working. Now our first task of the Project: “Planning Meeting” task 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 “41340” is actually representing this date. Equivalent number for date 03/05/2013 is 41397; I put number 41397 as the minimum value. Maximum value is automatically set, but I change it to 41480. And I change major units to 7. I want to change the number format, in the format code I type: dd/mmm. The dates are now looking more meaningful. Now I select the blue colored data series, in the Fill and Line tab, I use No Fill for this data series. You see, we have already got a Gantt Chart. Now this chart needs some formatting. I click on this data series, as Gap width value I put 30% to decrease the Gap Width between Axis values. In the Fill I use Solid Fill and in the 3-D format, I use some 3-D action. You see our chart is almost ready. I show the Data Labels for this axis. I change the Chart Title content to Project Schedule; make it bigger in size. I place the Chart title here; I want to show horizontal grid-lines, in the Grid Lines, I select Primary Major Horizontal. You can do easily the rest of the formatting. Just use some background color for the Chart Area, and make the grid lines dashed. So this is how you can create a Gantt Chart.

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.

How to Make a Gantt Chart in Excel 2013 Img2

Select Stacked Bar Chart in the Insert Bar Chart drop down

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.

How to Make a Gantt Chart in Excel 2013 Img3

By default, this Stacked Bar Chart is created. Delete the Legend from the chart.

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.

How to Make a Gantt Chart in Excel 2013 Img4

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 Axis Options tab, contract the Axis Options, and extend the Labels, select Label Position at High.

How to Make a Gantt Chart in Excel 2013 Img5

Change the Labels position at High for the Horizontal Axis.

Read More: Excel Charts with Dynamic Title and Legend Labels

Observe the data of the chart: “Board Meeting” is the last data. So, “Board Meeting” is regarded as the high position. When I have selected Label Position High for Horizontal Axis(value Axis), in the following chart you see: Horizontal Axis is taking 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.

How to Make a Gantt Chart in Excel 2013 Img6

Board Meeting data is at high. Horizontal Axis is also at High position.

You see the Horizontal axis starts at 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 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”.

How to Make a Gantt Chart in Excel 2013 Img7

Value of Start Date data series of Planning Meeting data point.

As the Horizontal Axis starts from 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:

  1. At first the Data Series name. In the above chart data series is: Start Date.
  2. Then the Data Point name. In the above chart, you are seeing data point is Planning Meeting.
  3. And finally the Value of the Data Series. In 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 date? 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:

  • Serial number of date 7/3/2013 is 41340
  • And the serial number of date 3/5/2013 is 41397
  • So there are total 57 days are there between dates 7/3/2013 and 3/5/2013.
How to Make a Gantt Chart in Excel 2013 Img8

Serial number of date 07/03/2013 is 41340 and serial number of date 03/05/2013 is 41397. There are total 57 days between these two dates.

So in the chart, for 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 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.

How to Make a Gantt Chart in Excel 2013 Img9

Set minimum value as 41397, maximum value as 41480, and Major axis as 7

Step 6: Change the date format

I want to change the number format, in the format code, I type: dd/mmm.

How to Make a Gantt Chart in Excel 2013 Img10

As Format Code 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.

How to Make a Gantt Chart in Excel 2013 Img11

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.

How to Make a Gantt Chart in Excel 2013 Img12

Select No Fill option for the Blue Bars

Now, look at the chart below. You see, we have already got a Gantt Chart.

How to Make a Gantt Chart in Excel 2013 Img13

You are getting this Gantt Chart now.

Step 8: Formatting the Gantt Chart

Now the above chart needs some formatting.

  1. 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.
    How to Make a Gantt Chart in Excel 2013 Img14

    Set Gap Width value to 30%

  2. Format Data Series → Fill & Line → Fill → Solid Fill → Use a color for the Data Series.
  3. Format Data Series →Effects → Expand 3-D Format → Top Bevel drop down → Select Angle type Bevel.
  4. 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.
  5. Change the Chart Title content to Project Schedule and make the Chart Title bigger in size.
  6. 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.
  7. You can use some different background color 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

How to Make a Gantt Chart in Excel 2013 Img15

This is the chart that I am getting finally.

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.

Wrapping Up

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:

Basic_Gantt_Chart.xlsx

Read more…

Excel Chart Elements: Parts of Charts in Excel

How to Make a Pie Chart in Excel (Ultimate Guide)


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

1 Comment

      Leave a reply