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
  1. First lets remove the legend box on the right by selecting it and hitting “del” key.
  2. 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.
  3. 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.

     

  4. 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.
  5. 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

| More

Posts & Navigation Have an Excel Question? « Citibank’s brilliant “no panga sab changa” ad Welcome to Pointy Haired Dilbert – Chandoo.org » Go Back to Home Page Tags: charting, charts, Charts and Graphs, free, gantt charts, how to, ideas, Learn Excel, microsoft, productivity, technology, tips, tricks, tutorial

Comments

RSS feed for comments on this post. TrackBack URI

Leave a comment

   Name (required)

   E-mail (required, never displayed)

   URL

Notify me of when new comments are posted via e-mail

If you have a question, please ask in the forums