How to create a Gantt chart in Excel 2007

This post explains how to create a simple Gantt chart using Excel 2007 and above.

It is also useful for creating charts in PowerPoint, because the data is based on Excel charts.

Step-by-step Instructions

1. Create the Table with Data

Create a table that includes at least the columns detailed below.

Columns to create:

  1. Tasks: name of each task
  2. Start date: date when the task is started
  3. Duration (days): days to end the task.
  4. End date (optional): this is a calculated value. It should be the addition of the values in column B (Start date) and C (Duration (days)).

Use the header name for the columns that you prefer that appear in the chart.

2. Create Chart

Select a cell within an empty space and click on Insert > Charts > Column Charts > Stacked Bar.

3. Fetch Data on Chart

Right-click on chart and click on “Select data”.

Under the Legend Entries (Series), click on “Add” button:

  • Series name: select the header cell on the “Start Date” column.
  • Series values: select all the cells below the “Start Date” header cell.

Add a second value on “Legend Entries (Series)” column:

  • Series name: select the header cell on the “Duration” column.
  • Series values: select all the cells below the “Duration” header cell.

On the “Horizontal (Category) Axis Labels”, click on “Edit” button. Select all cells below the header cell.

4. Format Chart

In versions like Excel 2007, you need to remove the legend box by selecting in and clicking on “Delete” button. This box does not appear by default on later versions.

4.1. Remove the first Group on the Chart

Select the first group in the cell (usually in blue). Select “No fill” to make this section invisible.

4.2. Reverse the Row Order

Select the number on the left. Go to Axis options.

Check “Categories in reverse order”.

4.3. Set the Chart Date Ranges

When setting the date ranges, think what are the minimum date and maximum date that should be plot on the chart.

Type these dates on two different cells. Then convert to number, and write down the corresponding number.

Select the ranges on the top of the chart. On “Axis Options”, set the fixed minimum and fixed maximum base on the numerical value you just wrote down.

Select the primary and secondary units to the same value. For example, if you want to set a vertical line each month, set value “30”.

4.4. Set the Date Format

Select the date ranges in the table and change the date format. To do so, go to “Axis Options”, then “Axis Options” tab, then expand “Number” section, set “Category” to “Date” and select the corresponding “Type”.

You may use the dd/mm format to reduce the amount of text.

5. Customize Chart

Customize chart with you own set of colors and other additional customization options.

You might also be interested in…

External References

Leave a Reply

Your email address will not be published. Required fields are marked *