Gantt charts are commonly used in project management and while Excel doesn’t have a native Gantt chart in its chart library we can easily create one.
Don’t get too excited, it’s a very basic one.
If your job involves complex project management then you probably need a more sophisticated tool like Microsoft Project.
However, for fairly straight forward projects an Excel Gantt chart can be a great visual tool to understand the workflow and timing of your project.
How to Create a Gantt Chart in Excel
Set up your data. Here is mine:
Note: you don’t need the End Date to create your chart, but I think it’s helpful for the planning process.
Select any cell in your table and on the Insert tab of the ribbon choose 2D Bar Chart > Stacked Bar Chart:
It should look a bit like this:
Now the Trickery Begins
Right click and select the X axis containing the task names and select Format Axis (or if you have Excel 2010 you can just double click the X axis to open the Format Axis dialog box).
Note: in a bar chart the X and Y axis appear to be reversed with X being the vertical axis and Y being the horizontal axis.
Under Axis Options check the ‘Categories in reverse order’ box:
Now select the first series in your chart (in mine it’s the grey/gray bars), and on the Home tab of the ribbon select ‘no fill’. This will make this section of the bars invisible.
Now click on the brown section of the bar chart that represents the End Date and press the delete key to delete it. While you’re there you can delete the legend too.
Your chart should be taking shape and look a bit like this:
Now let’s fix the dates.
First find out the serial number for the start and end dates of your project.
To do this simply format the dates in your table as General and make a note of the serial numbers for the first and last date, then change the formatting back to date.
Note: My dates are formatted ddd-dd/mm/yyyy.
My start date is 3rd June 2013 which has a serial number of 41428 and my end date is 19th July 2013 which has a serial number of 41474.
Now right click (or double click if you have Excel 2010) on the Y axis and select Format Axis >Axis Options and make the following changes:
- Enter your start date serial number as the minimum
- Enter your end date serial number as the maximum
- Set your major unit to 7 (to group it into 1 week bands)
- Set your minor unit to 1
While still in the Format Axis dialog box select the Number tab and change your date format to dd-mmm or mmm-dd if that’s how you format your dates.
For a finishing touch you can reduce the gap width on your bars to make them a bit bigger. To do this right click on one of the bars > Format Data Series > Series Options. I’ve set mine to 30%:
Now your chart should look something like this:
Another way to create a Gantt chart in Excel is to use Conditional Formatting, but that’s a topic for another day.
What Do You Think?
If you liked this please use the buttons below to share it with your friends and colleagues on LinkedIn, Facebook, Twitter, +1 on Google or leave me a comment below. I read them all.
Very exciting tutorial on preparing Gantt Chart. How can I insert a vertical bar on the chart to show the progress date?
Hi Frankhttps://1drv.ms/i/s!AjfS33R8yoG9jIpoRRCIDutc6Gt9tg
You can try a conditionally formatted row, in the image below I set Start dates in F13 to R13, then a conditional formatting formula to that range:
=F13
The date axis will be filled based on today date.
Catalin
How can do multiple start and end dates on the same line? For example: A person completes the same task for 20 days, 40 days, and 50 days with in a 1 year period.
Hi Mike,
Yes, you can. You just need to calculate the gap i.e. the number of days between the end date and the new start date. And calculate the second duration. Add both of these columns to the chart and set the gap part of the bar to no fill.
Mynda
This is great! Is there a way to add an additional data point and apply conditional formatting so that the bars are different colors based on that additional data point? For example, I have a project plan and different actions for different audiences (employees or managers) and I want the bars for employees to be one color, and the bars for managers to be a different color.
Hi Andrea,
Depends on your layout, but in theory, it can be done. You can upload a sample file, if you need more help on this, on our Help Desk (open a new ticket).
Cheers,
Catalin
Thanks – that’s really helpful. Think I’ve got it now. Didn’t get the series number before but it makes sense now. I also found it was better to ignore the chart wizard icon.
Thanks. It works well with 2011.
🙂 Glad I could help, Henry.
That’s a nice little tool … even if I just started to pull my hair out because I never got to see the “bar” I wanted, the duration. Until I read for the 100th time and saw that I have to DELETE the “end date”.
So my question: why add them in the first place when creating the chart? Just because you selected the whole table, and you did the “selection” afterwards? Or is there a deeper meaning behind, that I don’t get?
Otherwise I must say that excel had some difficulties to create the chart right from the start with 900 task lines (never looked like what you got) … ^^ … so I created it for about 100 and added the rest afterwards via the “select data” option.
Anyway, I am really glad to have been able to built a gantt with this little tutorial. Thank you very much!
I tried the conditional formatting gantt too: quite funny to do. 🙂
Hi Maria,
If you don’t select all the columns the dates won’t be on the correct axis. You can try it yourself and see the results.
I can imagine 900 tasks would look quite crammed!
Glad you’ve been able to get it to work for your needs.
Mynda
This was a really great article! I used the techniques involved to create a Gantt chart with color-coded bars, instead of just one color. I used a start and end date column, then had a series of values that evaluated to 0 or the date difference, depending on a set of criteria. In other words, if a 3-day project was supposed to be red, then the “red” column was set to 3 and the “green” column was set to 0. Then I created the chart similar to how you created yours, but I added extra series for each column. Since I made sure that the total of all the colors added up to the total duration of that step, the bars appear where they should, but with different colors. Lastly, I added a two-point xy chart with values (TODAY(),1) AND (TODAY(),100) on a secondary axis to show how far along in the timeline we are. As time goes along, the whole thing automatically updates itself.
Cheers, Bryan. Sounds great. Thanks for sharing.
Hi Bryan,
I like what you’ve done with the chart. Any chance you could share the file as Im really struggling with it?
Much appreciated
Joe
Hi Mynda
I enjoyed your tutorial a lot! that’s cool, I never know how to create other chart apart from the basic one built in excel.
Thank you a lot
Thanks, Cintya 🙂
Thanks, this is really useful. It’s always a challenge to create an effective Gantt chart in Excel.
Thanks, Charlotte 🙂
Hi Mynda,
Nice spreadsheet. Good for small projects.
Made a couple of additional “enhancements” as follows:
– calculated the End Date using the function WORKDAY.INTL to take care of weekends and public holidays (who wants to work on weekends and public holidays …)
– added new column to calculated the actual elapsed days as End Date (calculated using WORKDAY.INTL) minus Start Date and used this column for the chart
Ah, Pietro. On the contrary…at budget time you work everyday 🙂 Well, back in my London days we did!
Actually you raise a good point. Thanks.
I also used the WORKDAY function to calculate some of the start dates that were dependent on previous tasks.
Thanks for sharing.
Mynda.
Fantastic I am doing something like this called a loading on excel will send you the file after completing…..
Great. Looking forward to it, Bakar 🙂
Hi Mynda,
Thanks for sharing your experience with us. We were in touch earlier when I was working in Manila. Right now, I am In Africa, in Dakar.
I would like to comment like this; I like to work with Excel rather than Microsoft for gantt chart, but the way you presented here (and this is the most common we found on internet) is not very useful, and is just worth for small project. More interesting is to work on the sheet itself with conditional formatting to be able to see changes in the dates (delay or anticipation), and even more interesting % of implementation, all this on the same line. The only problem is that if you work over 3 years for example, and on a monthly basis, Excel will not fill up properly because, a task finishing on June 10 will be considered as finished on June 30!!
So as someone else mentioned below, would appreciate your advice on this!!
With best regards,
Etienne
Hi Etienne,
Thanks for your comments. I agree, and mentioned in my post above, this application of Gantt charts is limited, but still useful for many. I’ve put a Conditional Formatting version on the To-Do list 🙂
Kind regards,
Mynda.
It’s useful me Thank u
Cheers, Siddhu 🙂
Brilliant lesson in basic Gantt charts. It still amazes me how versatile Excel is. Being part of a construction company we use these all the time. For those of us non-experts who do not use bespoke software this is a great little tool. Bring on the conditional formatting lesson.
Thanks, Kevin 🙂