So, you’re looking for a way to create a Gantt chart that will blow everyone away, but all the searching you’ve done online merely leads you to this mediocre option?
Let’s be honest. It’s 2021, and this chart just doesn’t cut it as a project management tool that you would actually use. But are there any alternatives? Well, you’ve come to the right place!
In this step-by-step tutorial, you will learn how to create this professional-looking Gantt chart in Excel without any add-ons—even if you’re a complete newbie:
The beauty of this Excel Gantt chart is that it supports a whole bunch of critically important features that third-party software providers typically charge you for:
- Unlimited tasks
- Full customization
- The current date line
- Tracking the progress on each task
- Sleek design
With this Gantt chart, you are in the driver’s seat. You have full control over every single element of the graph. You can even use it in your dashboards, as opposed to in-cell Gantt charts. And you can get all of that (and more) without paying a dime.
The setup process is a bit lengthy and may take you anywhere between 10 and 15 minutes. But the payoff is tremendous.
We have broken everything down into small, simple steps to make sure you can actually build out the same Gantt chart illustrated above on your own. Without beating around the bush, let’s jump right in—we’ve got some charting to do!
Step 1. Add the Project Data
Start with adding all the actual project data using the screenshot below:
Here’s the breakdown of the tables to help you adjust it to your own specifications:
- Project Name & Team Lead – These two cells won’t be used in the setup process and serve merely to provide your users with more context.
- Project Start & Project End – These two dates define the beginning and end of your project and will be used for charting the horizontal axis of your Gantt chart.
- Task – These values determine the actual tasks and will be charted as vertical axis values.
- Assignee – These are going to be used only as data labels. Alternatively, you can use the departments responsible for the execution of a given task.
- Start Date & End Date – These columns define the beginning and end of each task. Enter the dates in the dd/mm/yyyy format.
- Progress – This column indicates the task completion stage. If you don’t want to use this feature, set all the values to 100% and change the background and font color to white to hide the column.
Right out of the gate, we need to format the dates in column Start Date in a different way so the horizontal axis will display neat-looking dates.
1. Highlight the values in column Start Date (C5:C11), right-click, and select “Format Cells.”
2. In the Format Cells dialog box, under “Category,” choose “Custom.”
3. In the “Type” field, enter mmm d to set a custom date format that will make it a lot easier to read the chart.
Once there, here’s how the dates you modified should look:
Step 2. Prepare the Chart Data
Since our Gantt chart is going to contain a whole bunch of information, it takes a bit of preparation to make sure everything works like clockwork.
At the end of this stage, this is what you should see:
Here’s the breakdown of the chart data highlighted in blue to help you retrace the steps outlined below.
- Duration – The values in this column count the total number of days it takes to complete each task.
- Progress – This column converts the progress percentage into the corresponding number of days spent on a given task.
- Remaining – This column determines the remaining number of days it will take to complete a task based on the progress percentage points it takes to reach 100%.
- Today’s Date – This column pulls today’s date using the TODAY function to position the current date line on the Gantt chart.
- Today’s Date (Helper) – These helper values will be used to create the red line illustrating today’s date on the Gantt chart.
- Project Start & Project End – These values convert the project start date and end date in cells E1 and E2 into sequential serial numbers which we will use to define the horizontal axis limits. For instance, the date “9/21/2021” equals “44460” which indicates the number of days since January 1, 1900.
Whew, that was a lot of theory, so let’s get cracking.
4. Start with populating column Duration (G4:G11). Create a column named “Duration” (G4), type “=D5-C5+1” into cell G5, and copy the formula down into cells G6:G11 using the fill handle.
The formula subtracts the task start date (C5) from the task end date (D5) and adds one day to come up with the number of days it will take to complete a given task.
5. Create a column named “Progress” (H4), enter “=G5*E5” into H5, and copy the formula down.
This formula picks the number of days it takes to complete a task (G5) and uses the values in column Progress (E5) to calculate the progress on each task.
6. Set up another column named “Remaining” (I4), type “=G5-H5” into I5, and copy the formula into the remaining cells in the column (I6:I11).
7. Put together yet another column called “Today’s Date” (J4), type “=TODAY()” into J5, and copy the function into the remainder of the cells (J6:J11).
This simple function dynamically returns today’s date to help Excel accurately display the current date on the Gantt chart.
Since the default date format just doesn’t cut it for displaying the values on the chart, let’s spruce it up a bit.
8. Highlight all the values in column Today’s Date (J5:J11), right-click on them, and choose “Format Cells.”
9. In the dialog box that appears, choose the Custom option for the Category, enter “Today:” mmm d (make sure you include the quotation marks around “Today:”) into the “Type:” field, and click “OK.”
Once you have done that, here’s how that column should look:
10. Finally, create a column named “Today’s Date (Helper)” (K4), enter “0.1” into K5, and copy the value into the rest of the cells (K6:K11).
These values will make up a thin vertical line to display the current date on the Gantt chart.
As the last step before we can start building out our Gantt graph, convert the project start and end dates into the corresponding numeric code to come up with the values that will be used to set the limits of the Gantt chart.
11. Duplicate the “Project Start” and “Project End” labels (D1 and D2) into cells G1 and G2. Enter “=E1-3” into H1. Type “=E2” into cell H2.
The value in cell H1 copies the project start date and subtracts three days from it to leave some space for the assignee labels on the chart and to prevent the chart elements from overlapping—you can tweak it however you see fit.
The value in cell H2 simply copies the project end date.
12. Select the values in cells H1:H2 and format the dates as “Numbers.”
13. Hit the “Decrease Decimal” button twice to remove the decimal points.
By the end of this stage, here’s how your worksheet should look. Double-check everything just in case and move on to the next step.
Step 3. Create a Stacked Column Chart
I know, I know. You can’t wait to jump into action. Well, let’s finally get to building out the Gantt chart.
Right off the bat, lay down the foundation by creating a simple stacked bar chart.
14. Highlight all the values in column Start Date (C5:C11).
15. Go to the Insert tab.
16. Choose “Insert Column or Bar Chart.”
17. Select “Stacked Bar.”
Magically, your stacked bar chart, the starting point of our grand adventure, will appear.
Step 4. Add New Data Series
We haven’t prepared all that chart data just for it to collect dust, so let’s push all of that into the chart plot.
18. Right-click on the chart plot and choose “Select Data.”
19. In the Select Data Source dialog box, click the “Add” button.
20. In the “Series name” field, click the header row of column Progress (H4).
21. In the “Series values” field, select all of the values in column Progress (H5:H11) and click “OK.”
22. Rinse and repeat the same process to create Series “Remaining,” Series “Today’s Date,” and Series “Today’s Date (Helper)” by following the exact same process outlined in steps #19–21.
At the end of this phase, order your data series in the way shown on the screenshot below:
Here’s how your stacked bar chart should look. Don’t worry—we’ll sort out this mess shortly.
Step 5. Modify the Vertical Axis (Add the Tasks)
Next stop: setting up the vertical axis labels represented by the names of the tasks pulled from your original data table.
23. While still in the Select Data Source dialog box, look under “Horizontal (Category) Axis Labels” and click “Edit.”
24. For the “Axis label range” field, highlight all of the tasks from your table (A5:A11) and click “OK.”
25. Save the changes you made by clicking “OK” twice to close out of the dialog box. At this point, your chart should look something like this:
Step 6. Reverse the Category Order
As you may have noticed, something is off with the vertical axis. Let’s fix it up quickly with just a few minor tweaks.
26. Right-click on the vertical axis and choose “Format Axis.”
27. Switch over to the Axis Options tab.
28. Under “Axis Position,” check the box next to “Categories in reverse order” to turn the chart upside down and put everything back in its place.
29. After that, right-click on the chart area and choose “Change Chart Type.”
30. In the Combo tab, set the “Chart Type” value to “Stacked Bar” for every single data series.
31. Check the “Secondary Axis” boxes for Series “Today’s Date” and Series “Today’s Date (Helper)” and click “OK” to close the dialog box.
Having done that, here’s what your chart should look like:
Step 7. Create a Gantt Chart
It’s time to put all the pieces of our puzzle together and transform the mess that we currently have into a powerful Gantt chart.
To do that, we just need to match the bound values of both the axes.
32. Right-click on the primary axis and click “Format Axis.”
33. Hit the “Axis Options” button.
34. Under “Bounds,” set the “Minimum” value to the converted value of the project start date (H1).
35. Change the “Maximum” value to the converted value of the project end date (H2).
36. Repeat the same process outlined in steps #34–35 for the secondary axis and watch your chart magically transform. Once there, delete the secondary axis (the one highlighted on the screenshot below) by pressing the Delete key.
Step 8. Clean Up the Axes
Let’s clean up the axes a bit to start seeing the first signs of our lovely Gantt chart.
37. Switch back to the primary axis. In the same Axis Options tab, under “Units,” set the “Major” value to “2.” This value determines the intervals of the days on the primary axis scale, so you can tweak it however you want.
38. Change “Label Position” to “High” to re-position the axis position.
39. Without de-selecting the primary axis, slightly rotate the axis labels for everything to fall into its place. To start with, click the Home tab.
40. Hit the “Orientation” button.
41. Select “Angle Counterclockwise.”
Let’s take a quick peek at our Gantt chart. Here’s how it should look now:
Step 9. Set Up the Today Line
42. Right-click on Series “Today’s Date” represented by the dark blue horizontal bars (all of the same size) and click “Format Data Series.”
43. In the Format Data Series task pane, go to the Series Options tab.
44. Change the “Gap Width” value to “0%.”
45. Switch to the Fill & Line tab.
46. Under “Fill,” choose “No fill” to make the data series transparent.
47. Jump to Series “Today’s Data (Helper)” without closing the tab.
48. Click the Fill & Line icon.
49. Change the fill color to red.
50. Set the border fill color to red.
51. Under “Border,” set the “Width” value to “1 pt.”
52. If you want to add the custom data label that we previously set up, double-click at the bottom of the vertical red line, right-click on it, and select “Add Data Label.”
53. Select the Data Label and click the “Label Options” button in the Format Data Labels pane.
54. Check the “Value From Cells” box.
55. For “Select Data Label Range,” select any value in column Today’s Date to set up a custom data label.
56. Click “OK” to apply changes.
57. In the same tab, double-click the “Value” checkbox to remove the default data labels. (This will add them and then take them away again.)
58. Finally, change the label color to red (Home > Font > Font Color) and make the label bold (Home > Bold). In the Label Options tab, under “Label Position,” choose “Inside Base.”
Step 10. Set Up the Task Bars
Before we call it a day, all we need to do is set up the task bars to help your team keep track of whether you’re ahead of or behind schedule.
59. Hide the bars supporting the Gantt chart. Right-click on the helper Series “Start Date” holding the task bars together and select “Fill.”
60. In the menu that appears, choose “No Fill.”
61. Right-click on Series “Remaining,” represented by the orange bars, and choose “Format Data Series.”
62. Click the “Series Options” icon.
63. Change the “Gap Width” value to “30%.”
64. Navigate to the Fill & Line tab.
65. Under “Fill,” select “Pattern fill.”
66. Pick “Diagonal stripes: Dark upward” from the options that appear.
67. Change the “Foreground” to dark blue.
Switch to Series “Progress,” represented by the grey bars, and recolor them to make the color scheme consistent:
68. In the Fill & Line tab, under “Fill,” select “Solid fill.”
69. Next to “Color,” click the “Fill Color” icon and change the color of the data series to light blue.
70. In the Format Data Series task pane, go to the Effects tab.
71. Click the “Shadow” button.
72. Under “Presets,” select the dropdown to the right. In the menu that pops up, under “Outer,” choose “Offset: Bottom Right.”
73. Repeat the same step for Series “Remaining.” At the end of this step, your Gantt chart should look like this:
Step 11. Add the Assignee & Progress Labels
Technically, you can stop right there. But alternatively, we can make our Gantt chart a lot more descriptive and informative by adding the data labels illustrating the corresponding assignee and progress for every single task.
74. Right-click the hidden Series “Date Start” holding the task bars together and choose “Add Data Labels.”
75. Select the labels and go to the Label Option tab.
76. Uncheck the “Value” box.
77. Under “Label Position,” select “Inside End.”
78. Under “Label Contains,” click “Value From Cells.”
79. In the Data Label Range dialog box, select all the values in column Assignee (B5:B11).
80. Click “OK” to apply changes.
Change the font size and color for the labels to fit them better on the chart—and take a look at the results of all your hard work:
81. Right-click on Series “Progress,” represented by the light blue vertical bars, and click “Add Data Labels.”
82. Adjust the progress data labels by repeating steps #75–80. In this instance, however, set the “Label Position” value to “Inside Base” and use the Progress data from E5:E11 when you set up the “Value From Cells.”
All you have left is to spruce up the progress data labels to make them stand out.
83. Change the label font color to white.
84. Change the label background color to black.
85. Apply the bold and italic font formatting.
86. Change the font size to “8.”
Finally, change the chart title, and you’re all set!