One of the most frequent tasks for any manager is “planning”, be it putting together a hiring schedule or designing a jumbo jet, it all starts with a simple project plan and gantt chart is simple and intuitive representation of the same. But how to make a gantt chart in excel without writing too many formulas or adding conditional formats? Do not worry! with the simple trick we are going to learn today, you will be able to “gantt in 60 seconds” 🙂
You dont have even 60 seconds, worry not, download the free gantt chart template and get your project plan ready in 6 seconds.
For our example purposes, we will look at a fictitious project plan shown below:
Even though you can use this trick to pretty much any data format, it works better when the project plan is structured around how I represented it above.
Now lets build a gantt chart in 60 seconds, get your stopwatches out and get, set …. GO!
1. Create a bar graph
Select the data part of your plan (ie all the cells except header row in the above table) and click on chart icon in excel. Select “bar chart” as chart type and “stacked bar 2d” as sub-type (2nd left on the top row) as shown here.
Click finish. At this point your gantt chart should look like this:
2. Transform your bar graph to gantt chart
Now we will convert this stacked bar graph to a gantt chart by using chart formatting options.
- First we will reverse the way data is charted, so that it looks like a gantt chart. For this we need to right click anywhere on the “y-axis” of the graph and select “format” option. Go to “scale” tab and check “Categories in reverse order” option.
Click ok, now out gantt chart should look like this:
btw, what is the time on that stop clock, 34 seconds, well, thats just fine, we have got plenty of time to spruce this up.
- Now, lets get rid of first data series so that our graph looks more like gantt chart. Select the first data series of your chart (should be in violet blue color 🙂 ), right click and go to “format data series” option.
Select “none” for “border” and “area” options in the “patterns” tab. This will make sure that the first series is invisible, so we see second data series floating on the chart, thus making it look almost like a gantt chart.
Go to “data labels” tab and check “category name” option. This will make sure our gantt chart will show labels (but on the now invisible first data series)
Click ok, at this point our gantt chart should look like this:
- Finally we will adjust labels and backgrounds to convert this to a perfect gantt chart
- First lets remove the legend box on the right by selecting it and hitting “del” key.
- Now, lets also remove the y-axis since labels are visible on data-series 1. Just click on the y-axis (or category axis) and hit “del” again.
- Lets adjust the alignment of the data labels on series 1 so that they are properly visible. Right-click on the data labels and select “format” option. Go to “alignment” tab in the dialog and select “Right” for horizontal alignment and “inside end” for label position. This will ensure that our data labels are right aligned and shown at the end of data series 1, ie closer to the actual series 2 (duration of the task). See the below screen cap for more help.
- Lets also remove the plot back ground to remove the annoying grey color from our gantt chart. Just click on the grey color anywhere and hit “del”. While we are at it, you can also change the line color of x-axis (the days) to white or transparent to reduce the eye sore.
- Finally, lets adjust the plot area size so that we can read all the data labels and everything looks normal.
At this point our gantt chart should look something like this:
If you still have few seconds left, you can tweak the chart format to make it look better. I had 3 more seconds left, so I tried this 🙂
Feel free to download the free gantt chart template and see how to create gantt charts using excel bar graphs.
Bonus tips for enthusiastic excel experimenters:
1. Adjust the grid line format to make them more subtle
2. Select a particular task’s data point and change its color to emphasize progress / stalled statuses
3. Enhance this to add another column with no. of resources (or difficulty etc.), add this to the stacked chart and make it invisible just like series 1, but show the data labels.
4. You get the picture… so start gantting… 😀
Also read:
Learn how to create project plans / gantt charts using conditional formatting
Create art grade excel charts with these 73 designer templates
Other uber cool excel tricks to make your colleagues zealous and your boss happy
18 Responses to “Gantt in 60 seconds – churn out an excel project plan in no time”
Very interesting and very useful... am toying with this now... man, u always have something really useful.. great...
one question - i usually have "dates" on my project schedules (on the X-Axis, i mean)... instead of "days"... Getting that, is looking a bit tricky from my initial trials... Will let u know if i figure some way... if u have any thoughts, add in...
Cheers
Am using this on my Mac. This is extremely slow. We purchased this about a week back.
Everytime I make a change to a data... it takes around 30 seconds to reflect.
Is there a way to make it faster. It is highly frustrating to enter data and I am getting very impatient. Please help
Regards
Srinivas GS
848-468-7598
OK, i figured a way... the two columns will just have to be "Start DATE" and "number of DAYS"... Almost solves it off... Figuring a way to include EST / EFT etc.
@Sastry .. thanks for the comments, yeah, I figured many of us actually have dates on the plans. It could be little tricky working with dates but as you proved in the next comment, you can do that by merely replacing the 2 columns with startDate and no. of days.
It would be great if you can share your way when you figureout how to get the EST / EFT et al in the gantt... 🙂
[...] quotes to your workbook with one click, Master your ifs and buts - learn these 6 tips on if(), Project plan in 60 seconds, your time starts now!, Honey! I shrunk the bar charts and much more Tags: cool, excel, howto, learn, microsoft, [...]
"Gantt in 60 seconds" Hehehe, I saw what you did there...
[...] Like this? Also try: Thermometer charts in Excel, Micro bar charts, Gantt charts with excel bar graphs [...]
Hi, I'm working on a variation of this gantt chart and like Sastry I figured out a way to graph actual timepoints on the x-axis (instead of nominal timepoints). I'm trying to keep the chart dynamic. Taking advantage of the common method involving names and the offset function, I want my chart to expand or contract depending on the amount of data a user inputs into a source data table. The problem is that since I'm plotting actual dates on the x-axis, Excel automatically scales the x-axis for me. This is pretty annoying since Excel will always over-estimate and the scale will be bigger than I want it to be (ie first task starts Jan 2009 but Excel sets the min value as Jan 2007.) I know you can manually set the min and max values on a scale, but this would defeat the purpose of keeping the chart dynamic. I've seen a couple solutions that makes use of vba to create a dynamic scale...wondering if there's a simpler way? Any help would be greatly appreciated.
Hi all,
what about using a bullet like charting instead of stacked bars, that would be more focused on the status (real vs planned).
I think it will also highlight the milestones as we'll see clearly where we are regarding where we zere planning to be!
cheers
-kader
Let me see...how many ppl I need to thank...
1. Internet.....Thanks
2. Google....Thanks Thanks
3. Chandoo...Thanks Thanks Thanks....
Hi,
I came across your site earlier this afternoon and tried this 'Gantt in 60 seconds'.
Thanks very much for an easy to follow and effective solution to making gantt charts in Excel, its been very useful already.
Thanks
DC
Hi Chandoo-
I love all of your tips and tricks! I use Excel 2010 at work and some of the info is not applicable and I am not always able to translate - any ideas?
Hi Chandoo,
If I want to include the progress or % of completion into this Gantt chart, how should I go about doing it? Thanks for the great tips!
how about if i wanna create a chart with the same data, for columns from left to right will be the tasks instead and the rows going down will be the dates, is it possible? million thanks in advance!
what i mean is that X axis labels are showing the tasks from left to right, then Y axis labels will be those start days of each task. os it possible? seems i struggle to make it and find no clue..
Thank very much. This is so so helpful. I had no idea how to go about stand now will be able to provide feedback to stakeholders. Stay blessed
Chandoo,
Thank you so much, this is really helpful !
I found your instructions via Google. I'd been looking for a way to make a gantt graph without knowing how or even what the proper name was. Your instructions were very helpful, even for Excel 2013. Thanks!!!