Gantt chart is used basically in project management. You cannot create a Gantt chart in Excel in a direct way. But don’t worry, you can make an advanced-level Gantt chart in Excel by modifying the Stacked Bar chart. This quick tutorial will guide you to make a Gantt chart in Excel with some simple steps. In the worksheet, you see a simple Gantt Chart, created using the piece of data on the left of the chart.
Introduction to 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 B contains the task name, column C contains the start date of the task, and column D contains the duration to finish the task.
Note: One important thing is that column B must not have any heading, otherwise column B and column C both will be used as the category axes. OK, let’s start creating this Gantt Chart from scratch.
How to Make a Gantt Chart in Excel: with Easy Steps
Let’s say, we have a dataset that contains information about an arbitrary Project Schedule. We will make a Gantt chart using the dataset. Here’s an overview of the dataset for today’s task.
Step 1: Create Dataset with Proper Parameters
In this portion, we will create a dataset to create a Gantt chart in Excel. We will make a dataset that contains information about different types of works with starting dates, and the duration of those works. We shall use the following data to create the Gantt chart. So, our dataset becomes.
Step 2: Make Stacked Bar Chart
Now, we will make a Gantt chart using the dataset. To do that, firstly, we will create a 2-D Bar chart. After that, we will make a Gantt chart. Let’s follow the instructions below to learn!
- First of all, select the data range B4 to D16. Hence, from your Insert tab, go to,
Insert → Charts → Insert Bar Chart → 2-D Bar → Stacked Bar
- 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 3: Reversing Order of Category Axis
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.
- As a result, you will be able to change the Category Axis in order of work.
Step 4: Changing Labels Position of Horizontal Axis
When you reverse the Category Axis 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 on the chart: “Board Meeting” is the last data. So, “Board Meeting” is regarded as a high position. When I selected Label Position High for Horizontal Axis(value Axis), in the following chart you see: Horizontal Axis is taking a position on the 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 sets 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 represents value 03/05/2013 of Start Date data series of point “Planning Meeting” and the Red Bar represents the value 3 of Duration data series of the 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 5: Finding Out 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, and you will get the serial number of relevant dates. Using this method, I have found that:
- The serial number of the date 7/3/2013 is
- And the serial number of the date 3/5/2013 is 41397.
- So there are a total of 57 days there between the dates 7/3/2013 and 3/5/2013.
Look at the below GIF to understand it.
So in the chart, for the Board Meeting data point, the blue bar is actually representing number 57 and the Red bar is representing the number 3. So this is how this chart is working.
Step 6: Change Minimum, Maximum, and Major Values of Horizontal Axis
Now our first task of the Project “Planning Meeting” starts on 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 10.
Step 7: Change Date Format in Gantt Chart
I want to change the number format. To do that, I type dd/mm in the Format Code under the Number tab. The dates are more meaningful in this way.
Step 8: 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 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 9: Formatting 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 level.
- 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.
- Finally, by completing the above process, you will be able to make a Gantt Chart which has been given in the below screenshot.
👉 Use Insert ribbon to create a 2-D Stacked Bar chart.
👉 Press ENTER on your keyboard while completing the requirements of the Axis options option
Download Practice Workbook
So, you see making a Gantt Chart in Excel is an easy job. Just you have to practice and understand the concept of creating a chart. The rest is easy.
Any Questions on how to make a Gantt Chart in Excel? Feel free to ask in the comment section. I will be very happy to hear from you and learn more along the way.