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 and advanced solution with a full video tutorial and sample Excel file, click here to jump to the bottom of the page to learn more. [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.] 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 project management software like MS Project).
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.
Advanced solution video & sample file
I've taken the idea in this article and created a new video and sample file 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.
If you want to go into more detail on creating Gantt charts in Excel, see this article by Excel MVP Jon Peltier.