How to: create a dynamic Gantt chart in Excel

Often it’s more effective to communicate a series of project schedules visually. In this how-to we’ll create a Gantt chart in Excel that automatically adjusts as project dates change.

The first thing you’ll need is a series of data points. That could be an export from a project tracking system or database, you just need three distinct fields: project names, launch dates, and completion dates.

To follow along with this how-to, you can download the example Gantt chart.

First arrange your project names, launch dates, and completion dates in three adjacent columns. Then in a row above, insert dates incremented by month (or whatever interval makes sense for you).

Below is an example.

Note: to easily create a series of incremented dates (for the top row in this example), enter two points in the series, select those cells, and drag the formula filler box to the right. So in this case I entered 1/1/2008 and 2/1/2008, selected those two cells and dragged the formula fill box to the right. Excel filled in the additional dates using my first two points as a 1 month patterned increment.

Now we need to shade the cells that are contained within each project schedule. To do that we’ll insert a formula and use conditional formatting.

1. Select the first cell in the table series, in this example it would be cell D3.

2. From the Format menu, choose Conditional Formatting.

3. When the Conditional Formatting window appears, click on the drop-down menu under Condition 1 and choose Formula Is.

4. Choose a pattern or other visual effect using the Format… button.

5. Enter the formula: =AND(D$1>=$B2,D$1<=$C2). By using the AND() function we can specify two conditions within a single formula, in this case it’s a launch and complete date.

D$1 specifies a static reference to row 1, but allows Excel to dynamically adjust the column reference. Similarly, $B2 and $C2 forces Excel to always compare launch and complete dates from column B or C but allows it to dynamically adjust the row number.

6. Click on the OK button.

If the first set of project dates falls within the first month (or whatever interval you’ve chosen), then your visual effect should show up.

7. Copy the conditionally formatted cell to the rest of your table range. In this example I’ll push CTRL+C while cell D3 is active, select the entire table range and paste in the conditional formatting using CTRL+V.

For a more detailed view, change the date intervals on the top row to a smaller increment.

Now whenever any of the project launch or completion dates change, Excel will automatically adjust which cells are shaded. Enjoy!

• 

• Hackers attack Excel, batten down the spreadsheet!
• Security headaches for Excel users as Microsoft warns of zero-day attack
• Swinburne and Suntech team up to create the next solar generation
• Google’s dynamic themes offer stylish web searching with impressive visuals
• Wikipedia boss to create Facebook and Google killer in one?