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.