Gantt charts are a special kind of bar chart used in scheduling and program management. A set of tasks or activities is listed along the left hand axis, and the bottom axis shows dates. Horizontal bars indicate when each task begins and ends, and which tasks are in progress at any given time. The simplest kind of Excel Gantt chart involves a worksheet range, with the tasks listed in the first column, and dates (for example, week beginning or ending dates) in the top row. If a date along the top falls between the start and end dates for that task, the cell in the same row as the task is shaded a different color.
It is not too difficult to create a regular bar chart showing this same amount of detail.
And once you’ve decided to make a chart, you can add more advanced embellishments to show more details.
This tutorial will show you how to make all types of Gantt chart in Microsoft Excel.
Simple Gantt Charts in the Excel Worksheet
Let’s start with this simple Gantt chart data. Listed are tasks, beginning and ending dates, and duration. For this simple chart, we don’t even need the duration column.
We can expand the range to the right, with weekly dates in the top row of the added columns.
We can fill the cells in manually, of course, but it’s nice to set up conditional formatting so the colors update whenever the beginning or ending dates change. For each cell, the formula checks whether the date at the top of the column is greater than or equal to (on or after) the starting date and less than (before) the ending date for that row. If so, then the cell is formatted as shown.
The result is a quick little graphic, easily done right in the worksheet, so the Gantt chart timelines line up with the task rows.
It’s not fancy, but sometimes all you need is something quick.
Simple Excel Gantt Charts
To make a Gantt chart from an Excel bar chart, we can start with the following simple data: Task, Start Date, End Date, and Duration.
Since dates and duration are interrelated, usually Start is entered, and either End or Duration. If Start and Duration are entered, End is calculated as =Start+Duration. If Start and End are entered, Duration is calculated as =End-Start.
The small range to the right will come in handy when fixing our date axis limits in the bar chart. The upper blue-shaded cell contains a formula that returns the minimum of the Start Dates, and the cell above this is seven days earlier. The lower blue-shaded cell has a formula that returns the maximum of the End Dates, and the cell below that is seven days later. The second column shows these dates in General number format, so we see just the serial number of the date (the number of days since 1/1/1900); we will use the yellow shaded cells as the limits of the chart’s date axis.
Before we make the chart, we should apply a special number format to the Start Dates so they fit closer along the axis. Select the dates, click Ctrl+1 to format them, and on the Number tab, choose Custom, and in the Type box, enter d (for day), type Ctrl+J to enter a line feed right in the format, then enter mmm (for three letter month abbreviation) and press Enter. This produces two-line dates with the day number on the first line and the month abbreviation on the second. We can change this back later, but it’s easier to create this format in the worksheet than to try to achieve it in the chart. Also, clear the top left cell, so Excel plots the data the way we need it plotted.
To create the simple Gantt chart, we start by selecting the columns with Task, Start Date, and Duration, and inserting a stacked bar chart. Notice the date format which was taken from the worksheet range.
The tasks are listed in the opposite order, so we need to format the vertical axis (select it and press Ctrl+1 or simply double click on it). Check ‘Categories in Reverse Order’ and ‘Horizontal Axis Crosses at Maximum Category’ to correct this. See Why Are My Excel Bar Chart Categories Backwards? and Excel Plotted My Bar Chart Upside-Down to read all about it.
Now we need to format the date axis, so select the axis and press Ctrl+1. Change the minimum and maximum to 43184 and 43289 (from the special range we set up besides the main data range), and set the major unit to 7. Under Number, uncheck ‘Linked to Source’, and then you can reset the date formats in the data range.
Finally, a little clean up. Format the Start bars to have no fill color, and give the Duration bars an appropriate fill. I like to use 25 or 50% transparency, so the fill isn’t overpoweringly dark and so the gridlines show through. Also delete the legend.
That wasn’t too hard now, was it?
Advanced Excel Gantt Charts
Now that we know how to make a Gantt chart with a regular Excel chart, we can think of ways to enhance it. How about duration bars that show the fraction complete? How about milestone markers coded to show finished and unfinished tasks? And maybe a vertical line to indicate a date of interest?
The data range is a bit more complicated. The user enters Start Date, % Complete, and either Duration or End Date. % Complete could also be computed from the Start Date, End Date, and the date of interest (indicated in the block labeled “Vertical”). If the user enters Duration, the End Date formula in G3 is =C3+F3. If the user enters End Date, the Duration formula in F3 is =G3-C3. Duration is split into Done and Not Done components with two simple formulas. The formula in D3 is =K3*F3 The formula in E3 is =(1-K3)*F3 The Milestones formula in J3 is =(ROW()-ROW(J$2)-0.5)/ROWS(J$3:J$11), which provides the milestone series with a vertical position to align with the bars. The Finished milestones formula in H3 is =IF(K3=1,J3,NA()) The Unfinished milestones formula in I3 is =IF(K3<1,J3,NA()) All of these formulas are copied down to row 11.
The block to the left below the main table will help with setting the date axis scale later. Cell C14 has the formula =MIN(C3:C11) Cell C15 has the formula =MAX(G3:G11) Cell C13 has the formula =C14-7 Cell C16 has the formula =C15+7 Cells D13 through D16 link to C13 through C16, but are formatted as General to show the serial number of the date.
The date listed next to ‘Vertical’ is where we will draw a vertical line indicating our interest in that date. Before we insert our chart, let’s apply a temporary special number format to the Start Dates in C3:C11. Select the range, click Ctrl+1 to format the cells, and on the Number tab, choose Custom, and in the Type box, enter d (for day), type Ctrl+J to enter a line feed right in the format, then enter mmm (for three letter month abbreviation), and press Enter. We will change this back later, but it’s easier to create this in the worksheet than to try to achieve it in the chart. Note also that cell B2 has been cleared, to help Excel understand how to plot the data.
The chart starts out pretty much like the simple Gantt chart above. Select the first four columns containing data for Tasks, Start Date, Done, and Not Done, and insert a stacked bar chart.
Format the vertical axis (select it and press Ctrl+1 or simply double click on it). Check ‘Categories in Reverse Order’ and ‘Horizontal Axis Crosses at Maximum Category’ to put the tasks in the order we want.
Now change the time scale on the horizontal axis. Change the minimum and maximum to 43184 and 43289 (from C13 and C16 in the special range we set up below the main data range), and set the major unit to 7. Under Number, uncheck ‘Linked to Source’, and then you can reset the date formats in the data range.
Now we can format the bars. I’ve made the Start series invisible by using no fill color; I’ve colored the Done bars blue and the Not Done bars orange, with 25% transparency on each so the gridlines appear through them.
At this point, we depart from the procedure for a simple Excel Gantt chart, by adding the Milestone data. Select and copy G2:I11 (End Date, Finished, and Unfinished), select the chart, and use Paste Special from the Paste dropdown on the Home tab of the ribbon, and add the data as new series, with series in columns, category labels in first column, and series names in first row. The data is added as new bars stacked on the Done and Not Done bars.
Right click on any series in the chart, and choose Change Series Chart Type from the pop-up menu. Change Finished and Unfinished to XY Scatter. Excel automatically assigns them to the secondary axis group and adds a secondary vertical axis.
We need to switch the new axis: select it and press Ctrl+1, and check ‘Values in Reverse Order’. We also need to lock the axis min and max to 0 and 1. Under Axis Options, the Minimum box shows 0.0, and the Maximum box shows 1.0. Change these to 0 and 1, which does not change the chart, but the Auto label next to each box changes to Reset.
Now we can hide the secondary vertical axis (the one we just reversed). While formatting it, under Labels, change Label Position to None.
Now let’s format those milestones. I’ve used black-bordered diamonds for both, with black fill for Finished and white fill for Unfinished.
Now let’s add the data for the vertical reference date line. Select and copy G13:H14 (which includes the “Vertical” label), select the chart, and use Paste Special from the Paste dropdown on the Home tab of the ribbon, to add the data as a new series, with series in columns, category labels in first column, and series names in first row. The data is added as a new single point along the top of the chart.
Select this new point, and choose Error Bars from the menu you get when you click the plus icon floating beside the chart. Microsoft calls these little icons “Skittles”.
You might not even see them, but the point now has vertical and horizontal error bars. Selecting them is a bit of a challenge: you can right click on the chart, and click the Chart Element dropdown to see all of the chart elements.
Or you can click the Current Selection dropdown at the top left corner of the Chart Tools > Format tab of the ribbon. Select the ‘Series “Vertical” X Error Bars’, and press the Delete key. Then select the ‘Series “Vertical” Y Error Bars’, and press Ctrl+1 to format them. Select Plus (the bar will point downward, but we just reversed the order of the axis, remember?), No Cap, and assign it a Fixed Value of 1. Then format the line color (I used a green lin). Select the Vertical data point again, and using the plus skittle, check Data Labels. Format the data label to show X value, not Y value, and assign a number format of “d mmm”. The space between day and month is fine, no need to try to insert that pesky linefeed.
And now a little more clean-up. I made the error bar thicker, gave the “Vertical” data label (“1 Jun”) a white fill with 25% transparency, and formatted the Vertical to use no marker at all. I deleted the legend, because it’s no longer needed.
There are a few additional labeling embellishments you might use, shown below. I’ve hidden the task labels on the vertical axis (change Label Position to None), and added data labels to the invisible Start Date bar chart series. I am using the Category Name option instead of Value, and the Inside End Label Position. This puts the task names to the left of the visible floating bars. Then I added data labels to the Finished and Unfinished markers. In both cases, I changed to the Value from Cells option, using the % Complete values in K3:K11.
I’ve given all of these labels a white fill background with 25% transparency.
Josh says
Very classy and effective!
Quick note: you said “The formula in E3 is =(1-F3)*K3”
Should it be =(1-K3)*F3 ?
Jon Peltier says
Josh –
That’s right, and I just corrected it. Thanks.
Megan says
How exactly did you do this;
Then I added data labels to the Finished and Unfinished markers. In both cases, I changed to the Value from Cells option, using the % Complete values in K3:K11.
Hermien Oberholzer says
I followed your step by step instruction – thank you very good. However, when I get to the step “Select Plus (the bar will point downward, but we just reversed the order of the axis, remember?), No Cap, and assign it a Fixed Value of 1.” I start to struggle.
If I add the value of 1, all my “finish/unfinish” markers move away from their bars.
Jon Peltier says
Hermien –
I left out a step in the instructions. When you change the direction of the secondary vertical axis, you also need to adjust the axis scale. Here is the sentence I’ve added to this part of the instructions:
We also need to lock the axis min and max to 0 and 1. Under Axis Options, the Minimum box shows 0.0, and the Maximum box shows 1.0. Change these to 0 and 1, which does not change the chart, but the Auto label next to each box changes to Reset.
Hermien Oberholzer says
Thanks Jon – you are doing amazing things with Excel and your blogs are very good.
Netty says
Hi Jon,
Thank you so much for this – it makes an excellent chart.
I need advice on making it slightly more autonomous. I have made this as a standard template for others to fill in but the one thing that I cannot make automatic is the formatting of the x axis so that it starts on whatever their start date is.
I know in your guide, you just type in the date in its general format but is there a way that it can pick up this number automatically? I.e. if someone’s start date is 1st Jan then the minimum axis starts at 43101 but if their first task actually starts 1st Oct, this automatically changes the minimum to 43374.
Any ideas?
Thanks!
Jon Peltier says
Netty –
This isn’t something that’s easy to do. I have an idea or two to try out, but I seem to remember trying this in the past with no joy.
Netty says
Hi Jon,
Thanks for your reply. This is very annoying. I don’t know how to make it easier for non technical people!
If you do have any ideas, please do let me know.
Thanks!
Jose Higuita says
Hi Jon,
What formula did you use to work out the %complete?
Regards,
Jose
Jon Peltier says
Jose –
% Complete is a parameter entered in this case by the user, who would estimate progress. I used arbitrary values as examples.
In a more detailed analysis, there may be tracking of progress against the milestones, which would mean formulas formulas would have to be determined.
Camille says
Hi John – great article! Super helpful.
One thought – if you want to create Gantt charts and project timelines in PowerPoint, it’s actually super hard. Especially if you want to be able to update them easily.
If any of your audience members want to learn how to do that, they can check out this step-by-step blog post: https://nutsandboltsspeedtraining.com/powerpoint-tutorials/how-to-make-a-gantt-chart-in-powerpoint/
Cheers!
Jon Peltier says
Interesting approach, using SmartArt as an intermediate step to turn an Excel list into a set at shapes and labels. But then you’re stuck having to drag and align these shapes manually. And maintaining the collection of shapes sounds like a nightmare.
Why not build the chart in Excel, then copy and paste it into PowerPoint? Format it all you want, and it’s still linked to the data if you need to make adjustments.
Thom Mitchell says
Thank you, Jon! I had a very nerdy application for this technique!
Jesse Blattstein says
Jon,
Absolutely phenomenal series! Breaks it down beautifully…
I have a question … I want to graph two hockey players … from two opposing teams that faced each other in a game. The goal is to chart their start times and duration, and they go on the ice in shifts.
So, for example, player 1 during his first shift, enters the ice at :55 (which means 55 seconds have elapsed in the game) and stays on for 1:26 (a minute and 26 seconds) …
Player A of the opposing team jumps on the ice at :57 (after 57 seconds have elapsed) and stays on for a minute and 45 seconds (1:45) …
My goals is to see which players have start times and durations that overlap the most. Is this possible to do with 2 sets of data values in Gantt? How would this be done?
Much thanks again.
alberto kusmic says
Outstanding job, very clear step-by-step instructions.
Thank you so much!
Michele says
Hi John,
I found your article by accident and it was a revelation. Thank you so much.
Do you know why once I changed Finished and Unfinished to XY Scatter, Excel automatically assigns them to the secondary axis group and adds a secondary vertical axis but not aligned with the previous bars.
Thank you so much for your answer
Jon Peltier says
Michele –
If you have a bar chart, Excel cannot plot other chart types on the same axes as the bar data. So Excel automatically assigns the other data to the secondary axes, and we just have to make it work.
Are your points not aligned vertically? That’s why I use Y values from 0 to 1, set the secondary vertical axis scale of 0 to 1, and plot that axis in reverse order.
Are your points not aligned horizontally? As long as both the bars and the XY points use valid dates, they should work on the bar chart’s horizontal axis. If Excel also provided a secondary horizontal axis, you may delete it.
Michele says
Hi Jon,
thank you very much for your prompt reply. Sorry, I’m getting crazy because I followed your step by step but when I change the chart to XY points, all my finish and unfinished markers move away from their bars. Vertically they would be ok, but starting from the task n°5 they remain in higher position respect the bar even if I adapted the scale from 0 to 1 in the secondary axe. I would like to show you a picture of that.
Thanks a lot.
Michele
Mark Rosenkrantz says
Hi Jon;
This is a well documented example of creating Gantt Charts which I was trying to make more dynamic using the Excel Table functionality. In essence to add a variable amount of tasks to the chart without having to format and update my formulas and chart!
Sadly, this includes (according to this example) adding a field name in cell B2, which leads to a totally different chart!
Is there a work around, or option (which I might have missed) to overcome this?
Would like to hear from you!
Jon Peltier says
Mark –
If having a label in cell B2 messes up the chart, select all of the needed columns except column B when you create the chart, then use Select Data to add the appropriate X values to the chart.