Let's suppose we have a project which has the tasks, planned start dates and end dates in the table below, and we are making a Gantt Chart based on this Table.
1. Create a Gantt Chart
Step 1: Select Column B in this example with Start Dates, click Insert from the Ribbon, then Column Chart >> Stacked Chart;
Step 2: A basic bar chart will be created. At this point, the chart should look like the chart below (you can also Insert a blank chart first then pilot the chart with the data column with Start Dates).
Step 3: Add Duration Data: Right click anywhere on the chart, then click "Select Data";
The "Select Data Source" dialog box will appear. In the box, click "Add";
Step 4: In the "Edit Series" window, move mouse to the first box (series name), then click cell D1 ("Duration"); move your mouse to the second box (Select Values), delete all contents inside, then select data range D2:D7, it will look like "=Sheet1!$D$2:$D$7";
Step 5: Click OK at the bottom, it will return back to the "Select Data Source" box, but at the same time, you will find the chart now has two portions (blue and orange – which maybe in different colors based on your Excel settings);
Step 6: Add Task Names: click "Edit" in the "Select Data Source" box;
Step 7: In "Axis labels" dialog box, move mouse in the "Axis label range" box, then select range A1:A7;
Step 8: Click OK, you will notice all tasks are now showing on Axis;
Step 9: Click "OK", you should have a chart below;
Step 10: Hide blue part, right click on the blue bar, select "Format Data Series" from the dialog box, click "Fill and Line" section in the "Format Data Series" window, then select "No fill" and "No line".
Step 11: A Gantt chart is created.
2. Improve Gantt Chart
Step 12: X Axis Options: To remove the extra space at two ends, right click on X Axis, select Format Axis, and then in the Format Axis window, change minimum value (first day project stat) and maximum value (planned last day). We need to convert the first day and the last day to a date value and here are the formulas:
Minimum value formula (the start day of the project): =datevalue(text(B2, "MM/DD/YYY")), which is 42795
Maximum value formula (the last day of the project): =datevalue(text(C7, "MM/DD/YYY")), which is 42900
Now the chart should like the one below:
Step 13: Reverse the order of Y axis to make it easier to read, because "Gather Materials" is the first step of this project and we can reverse the order to have it on the top.
Right click on Y axis, select Format Axis, and then check "Categories in reverse order".
Step 14: You will find the X axis also moved to the top position and we need to move it down to the bottom. To move it down, right click on X axis and then select Format Axis. In the Format Axis window, click Labels, then select High in the Label Position drop down list;
Step 15: Adjust Bar and Gap Width: Right click on the bar, select "Format Data Series" in the dialog box, then in the Format Axis window, change the value of "Series Overlap" and "Gap Width" till you are satisfied;
Step 16: Change Bar Color: Right click on the bar, select "Format Data Series" in the dialog box, then in the Format Data Series window, select the color you like;
Step 17: You should have a Gantt Chart same as below now. From here, you can make other miscellaneous changes such as chart title, font of the axis, 3D design, font size, etc.