Creating a Gantt chart with Milestones using a Stacked Bar Chart in Excel or PowerPoint

Gantt charts are not a built-in chart type in Microsoft Office (Excel, PowerPoint, and Word). There are templates you can download to create Gantt charts or add-ins you can buy. In this article I want to show you how you can create an accurate, informative Gantt chart with Milestones using a stacked bar chart in Excel (a similar method can be used to create the Gantt in PowerPoint). If you want an advanced solution with a video tutorials and sample Excel files for both monthly and daily Gantt charts, click here to jump to the bottom of the page to learn more. Here’s what our completed graph will look like.

Step 1 – Simple list of tasks with milestones identified

We start with a simple list of tasks with the start and end dates, as well as an indicator as to which tasks are milestones. I prefer start and end dates to start dates and durations because in most cases durations do not account for weekends and holidays, making the calculations much harder. Here is the list we start with (this list can be copied into Excel from another Excel worksheet, a Word table, or a web-based table).

Step 2 – Create the table for the Gantt chart

To convert the list of tasks and dates into a table for the Gantt chart, we need to calculate durations for each task and designate which tasks are milestones. We set up a table like the following.

In the Task column, we simply enter a formula to copy the task name from the original table.

In the Start Date column, we enter a formula to copy the start date from the original table. Set these cells to have the General number format so it changes the dates to the sequential date number Excel uses to store each unique date.

In the Duration column, create a formula that calculates the duration as the End Date-Start Date+1 if the task is not designated as a milestone. The formula for the first row of the table is “=IF(D4<>”y”,C4-B4+1,NA())”. The one day must be added to the difference between the start and end dates to account for both dates being part of the task. The NA() formula enters the #N/A value in this column for milestone tasks. This allows the milestone tasks to be a separate data series with its own formatting.

The column for milestone tasks has a series name as a lower case u. This will allow us to show a diamond character for the milestones in the graph (a diamond is the standard in project management for milestone tasks). The formula in this column puts a 1 in the cell if the task is a milestone and the #N/A value if the task is not a milestone. In project management standards, a milestone has no duration, but we need a one day space for the diamond shape in the graph. The formula for the first row of the table is “=IF(D4=”y”,1,NA())”.

Step 3 – Create the graph

Select the entire table for the graph. Insert a Stacked Bar chart.

Step 4 – Format the Chart

Chart Title – can be removed or edited to indicate the project name

Legend – delete

Vertical axis – in Axis Options, check the Categories in reverse order checkbox to order the tasks in the correct order; in Tick Marks, set Major and Minor to None

Horizontal axis – in Axis Options, set the Minimum value to a date value appropriate for what you want to show (if you want to show work weeks, set it to a Monday before the first task starts), set the Major Units value to 7 if you want to show week time periods, 14 for two week time periods, or another value that works best; in Tick Marks, set Major type to Outside; in Number, set the Category to Date and Type to a format that works (usually shorter is better); if the dates are too large or running into each other, make the font smaller

Vertical Gridlines – set to a light color so they are visible but not as prominent as the bars

Start Date series – set the Shape Fill to No Fill so this segment is invisible

Duration series – set the Shape Fill to a desired color; set the Gap Width to 30% or less to make the bars wider

Milestone (u) series – set the Shape Fill to No Fill so the segment is invisible; add a Data Label of the Series Name only; change the font to Wingdings to change the lower case u to a diamond shape; set the font size as large as desired

As the table of dates is updated, the Gantt chart will change because the table for the chart calculates its values based on the original table. If you add tasks to the original table, remember to add rows to the table for the graph and include these new rows in the data that is used for the graph.

[Note: if you want to create the Gantt chart in Excel but use the colors from your organizations’ PowerPoint template so the chart will be consistent with the rest of the colors in your presentation, follow the steps in this article and video.]

Advanced solution videos & sample files

I’ve taken the idea in this article and created video tutorials and sample files for both monthly and daily Gantt charts available below. It allows you to use either a list of dates or a list of task linkages and durations to create a Gantt chart. It teaches you the specific functions, formulas, and techniques to build your own flexible Gantt chart that is easy to update. It also gives you the flexibility to change the start date of the chart and the timeline automatically adjusts. Check it out below.