How to Create Gantt Chart in Excel

The Gantt Chart is a powerful project management tool that provides a visual representation of a project schedule (tasks or events) over time. It represents the start and end times for each task in a project timetable as well as the series and interdependencies between project tasks.

Unfortunately, Excel doesn’t provide options to create a Gantt chart, so you have to create one by customizing the in-built bar chart type. In this tutorial, we will show you how to make a Gantt chart in Excel by customizing a Stacked Bar chart.

Create a Project Table

The first step in making any chart in Excel is entering your data in a spreadsheet. So enter your project data and break it down into individual project tasks in separate rows and they form the basis of your Gantt chart. Each task should have a start date, an end date, and a duration (i.e. the time required to complete the tasks) in separate columns.

This is a sample spreadsheet for a software project.

You need to input the data into the spreadsheet and label the columns as task, start date, end date, and duration (number of days required to finish each task) like it is shown in the above screenshot. Also, the task data need to be sorted by the order of the start date.

Create Bar Chart

Now that your data is entered and properly formatted, you can start creating the Gantt chart by making a ‘Stacked Bar chart’ first.

You cannot select the whole table and insert a bar chart, if you do you’re more likely get a messy result like this:

So, we have to add the columns one by one to the chart. To do that follows these steps:

Insert a Stacked Bar Based on Start Date

First, select the ‘Start Date’ range in the table with the column header, in our case it’s B1:B11. Make sure not to select any empty cells.

Go to the ‘Insert’ tab in the Ribbon, click on the ‘Bar Chart’ icon in the Chart group, and select ‘Stacked Bar’ Under the 2-D Bar section (as shown below).

Now a bar chart is inserted based on Start Date data. The dates at the bottom of the chart may look overlapping one another, but that will change once the rest of the data are added.

Add Duration Data

Now we need to add the Duration data to the Gantt chart.

To do that, right-click anywhere within the chart area and select ‘Select Data’ from the context menu.

The Select Data Source window will appear. You’ll notice that ‘Start Date’ is already added under Legend Entries (Series) box. And now you need to input Duration data there.

Click on the ‘Add’ button to open Excel’s ‘Edit Series’ pop-up window. There are two fields in the Edit Series dialog box, type ‘Duration’ in the ‘Series name’ field, then click the ‘Series values’ field, and select the range of Duration values (in our case, C1:C11 ) for Series values. But do not select the column header, only the values. Then, click ‘OK’.

It will take you back to the Select Data Source window, where you will find ‘Start Date’ and ‘Duration’ are added under Legend Entries (Series).

The result:

Add Task Names to the Chart

The next step is replacing the duration (days) on the vertical axis of the chart with the names of the tasks.

Right-click anywhere within the chart area and select the ‘Select Data’ option to bring up the Select Data Source window again. Select ‘Start Date’ on the left pane and click the ‘Edit’ button on the right pane under Horizontal (Category) Axis Labels.

A small Axis Label will window appear. In that, click on the Axis label range box and select the task range from the table as you did for the Duration data. Be sure not to select the column header cell or an empty cell.

Click ‘OK’ to twice to close both windows.

Now your chart should have task descriptions on the vertical axis and will look something like this:

This is starting to look like a Gantt chart, but we’re not done yet.

Transform the Bar Chart into a Gantt Chart

Now you need to format the newly created stacked bar chart to turn it into a Gantt chart. What you need to do is to delete the blue bars of the chart in order that only the orange bars representing the tasks will be visible. Technically you’re not removing the blue part of the bars, but rather making them transparent, hence invisible.

To make the blue bars transparent, click on any blue bar on the chart to select them all, right-click on it and select ‘Format Data Series’ from the context menu.

The Format Data Series pane will open up on the right side of the spreadsheet. Switch to the ‘Fill & Line’ tab and select ‘No fill’ in the Fill section and ‘No line’ in the Border section.

Now, close the pane to find the blue bars are no longer visible, but the tasks on the left side (x-axis) are listed in reverse order.

To fix this, right-click on the list of tasks on the vertical axis of your Gantt chart and choose ‘Format Axis’ from the context menu.

In the Format Axis pane, check the ‘Categories in reverse order’ option under Axis Options.

Now, task names changed back to their original order and the horizontal axis moved from the bottom to the top of the chart.

When we removed the blue bars earlier, they left blank spaces between the orange bars and the vertical axis. Now, you can remove the blank white spaces where the blue bars occupied at the start of the Gantt chart.

To remove some of that blank spaces and move your tasks a little closer to the vertical axis, right-click on the first Start Date cell in your data set, and then select ‘Format Cells’ to open the Format Cells dialog. In that, choose the ‘General’ option in the ‘Number’ tab and note down the number under ‘Sample’ – this is the serial number of the date, in our case 42865. Then, click ‘Cancel’ (not ‘OK’) because if you click ‘OK’, it will change the change date into a number.

Then go back to the chart and right-click on the dates above the taskbar and select ‘Format Axis’ to bring up the Format Axis pane.

In the Format Axis pane, under the Axis Options tab, change the ‘Minimum’ Bounds number to the number you noted down from the Format cells window of the first date (in my cases ‘42800’ to ‘42865’). Doing this will bring the orange bars closer to the vertical axis of the Gantt chart.

To remove the excess space between the bars, right-click on any of the bars on the chart and select ‘Format Data Series’. Under the ‘Series Options’ tab reduce the percentage of ‘Gap Width’ to remove the excess space.