So, you’re looking for a way to create an advanced Gantt chart to schedule your project tasks, but all the searching you’ve done online leads you to this mediocre graph?
Let’s be honest. It’s 2021, and this Gantt 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, the main competitor of Excel Kanban boards and Kanban makers, is that it supports a whole bunch of critically important features that third-party software providers typically charge you for:
- Unlimited project tasks
- Full customization
- The current date line
- Tracking the progress on each task
- Assignees
- Sleek design
Gantt charts have their own pros and cons, but it’s one of the few project management tools that stood the test of time.
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!
Also, if you’re short on time, you can grab a Gantt chart template and follow the setup instructions we mapped out at the end of the article. The template is completely free and you don’t have to give away your email address or sign up for anything to get it.
Also, check out our top picks for the best Gantt chart software if you’re looking to take your project management to the next level with features such as task descriptions, task dependencies, etc.
Step 1. Add the Project Data
Start with adding all the actual project tasks 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 to create your project timeline.
- Task – These values determine the actual key 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 Gantt 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 (the duration data).
- 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 Microsoft 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 add single quotes 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 Bar Chart
I know, I know. You can’t wait to jump into action. Here’s how to make a Gantt chart in Excel that can blow away your friends and colleagues.
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 Excel 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 window, 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!
Short on Time? Grab This Free Excel Gantt Chart Template (with Step-by-step Setup Instructions)
As you can see from all of the steps covered above, building this Gantt chart template takes quite a bit of time.
So if you’re short on time, grab a free Gantt chart template by clicking the link above and follow simple instructions to adjust the Gantt chart Excel template to your actual data.
Let’s zoom out and take a look at the entire Gantt chart template.
On the screenshot below, the only columns and values you need to modify to get up and running are highlighted in blue while the rest gets adjusted automatically based on your actual values.
Here’s the breakdown of the values that you need to adjust:
Columns A, B, C, D, E – these columns are pretty self-explanatory. Use them to name your tasks, create assignees, specify the start and end date of every task, and keep track of your progress.
Column G and H – These are the only columns that are a bit trickier. The values in cells H5 (12/1/2021) and H6 (2/1/2022) are getting copied into cells K1 (44531) and K2 (44593). Once there, they converted into the corresponding numerical values – since all dates in Excel are stored as numbers.
These values define the beginning and end of the horizontal axis. That being said, you need to tweak the “Project Start” (H5) and “Project End” (H6) values in a way that prevents the assignee labels from overlapping the vertical axis of your Gantt chart.
Once there, you need to adapt your horizontal axis.
1. Right-click on the horizontal axis and select “Format Axis.”
2. Hit the “Axis Options” icon.
3. Under “Bounds,” set the “Minimum” value to the value in cell E1 (44531).
4. Under “Bounds,” change the “Maximum” value to the value in cell E2 (44593).
Once there, your chart is all set and ready to go.
Free Excel Gantt Chart Template #2 – Simplified Version
Are you looking for something simple yet professionally-looking? We’ve got you covered! This simplified version of our Gantt chart template has just everything you need to manage your project schedule effectively.
The setup process takes less than two minutes. So, let’s dive right in.
To get up and running, you need to modify seven elements of the template while the rest is adjusted automatically:
- Column Start Date – this column stores all the values corresponding with the start date of each of the tasks.
- Column End Date – this column contains all the values corresponding with the end of each of the tasks.
- Column Progress – Use this column to keep track of your progress.
- Column Assignee – Use this column to assign team members to each of your tasks.
- Column Priority – This helper column helps teams prioritize their efforts for better performance.
- Column Date – These two values generate the chart axis values for column M that you will need to use to adjust the axis based on your dates, helping you prevent your chart from getting messy.
Here’s how these values are generated: The underlying formula subtracts a few days from the start date of your first task and adds a few days to the end of your last task to prevent the labels from overlapping. Once there, The values are copied into column M and get converted into numbers. - The Horizontal Axis – Finally, you need to adjust the horizontal axis. This part is a bit tricky, so let’s cover it in greater detail.
1. Select the chart axis, right-click on it, and choose “Format Axis.”
2. Once the Format Axis task pane appears, click “Axis Options.”
3. Set the “Minimum Bounds” to the value in cell M1 (44305).
4. Set the “Maximum Bounds” to the value in cell M2 (44338).
Ta-da! You’re all set. Here’s how to make a Gantt chart in Excel, the go-to tool for project managers to impress clients and coworkers. If you want to create a Gantt chart in Google Sheets, we’ve got you covered as well.
