Create a Gantt Chart in Excel

If you do any project management, you’ll eventually need to (or be asked to) create a Gantt chart. A good Gantt chart clearly illustrates the life cycle of individual tasks that make up a project. If you build a house, for example, a Gantt chart can illustrate the time it takes to procure the site, get permits, buy materials, hire workers, connect utilities and so on. There are also relationships involved: you can’t start work without permits or have workers show up before materials arrive. A Gantt chart will also tell you how much of each task is complete and how much left there is to do.

I’ll show you how to create an Excel Gantt chart by formatting a bar chart based on simple data.

We’ll start by entering a task name, the date the task will start, how many days of each task have been completed and how many days are still left to go. We also need to know the start and end dates, but putting those on the worksheet is optional.

Input the data as follows:

You can save some time by downloading Gantt Chart Template 1_gantt data.

Now click anywhere inside the data area, go to the Insert tab on the Ribbon bar, and in the Charts section, click Bar and choose the second item, which is a 2D stacked bar.

This puts a chart on the worksheet that needs some tweaking. Stretch the chart out to see it better, then apply a built-in design. Keep the chart selected, go to the Design tab on the Ribbon bar (under the Chart Tools section that appears when the chart is selected), and in the Chart Styles section, click the down arrow to reveal all the available styles.

I chose Style 45:

Most of the bars show three sections: the length of time before the project starts, the days completed, and the days remaining.

We need to hide the first section of each bar, because the length of time before the project starts is meaningless data. So right-click any of the first sections and select Format Data Series from the popup menu.

In the dialog box that appears, click in the Fill section on the left and select No fill on the right.

Click the Border Color section on the left, and choose No line.

Click the Shadow section on the left, and choose No shadow under the Presets dropdown.

Click the Close button in the lower, right corner of the dialog, and click the chart background to get a better look.

Now let’s fix the range of dates along the bottom. Right-click any of the dates. The tooltip should tell you it’s the Horizontal Axis.

Select Format Axis from the popup menu. We want to set the start date as January 1, 2013 and the end date as September 1, 2013. In the Axis Options section on the left, change the Minimum and Maximum settings to Fixed, and enter the dates.

Click the Close button in the lower, right corner of the dialog.

Now let’s reverse the list of tasks in the vertical axis so that Task 1 is on top. Right-click one of the task names and select Format Axis from the popup menu. The tooltip should tell you it’s the Vertical Axis.

From the Axis Options section on the left, select the box for Categories in reverse order.

Click the Close button in the lower, right corner of the dialog.

But look what that does! It puts the horizontal axis on top of the chart. If you want it back on the bottom, there’s a quick fix: once again, right-click one of the dates on the horizontal axis, and from the Axis Options section on the left, click the dropdown list for Axis labels and select High.

Click the Close button in the lower, right corner of the dialog.

The last thing we want to do to the chart is fix the legend. The Start Date no longer appears in the chart, since it was extra information that we deleted in the first steps. Click the legend to select it, then click Start Date to sub-select it.

Press the Delete key on your keyboard to delete it.

Now right-click the legend and select Format Legend from the popup menu. In the Legend Options section of the dialog, select Bottom.

Click the Close button in the lower, right corner of the dialog.

Congratulations! You now have a completed Gantt chart.

Extra Tip! But Wait, There’s More

But one more thing: what if you need to create a relationship between the end of Task 1 and the beginning of Task 2? Maybe there should be a 2-day gap between them or a 5-day overlap. For that, we’ll put a formula in the data.

Select B5 and delete. To create a 2-day gap from the end of Task 1, enter the formula:
=B4+C4+D4+2

If you want a 5-day overlap, enter this formula, instead:
=B4+C4+D4-5

Now put it to work: for Task 1, change the start date (B4), or the days completed (C4) or the days remaining (D4). As soon as you do, the bar for Task 2 will adjust automatically with respect to the bar for Task 1.

To play with the completed exercise, or to use it for your own projects, download Gantt Chart Template 1_gantt complete.