How to Create a Gantt Chart in Excel

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?

Simple Gantt chart

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:

Advanced Gantt chart

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
  • Assignees
  • 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:

Enter the project data

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.”

Click 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.

Create a custom date format

Once there, here’s how the dates you modified should look:

Format column Start Date

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:

Gantt graph chart data

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.

Find the Duration values

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.

Find the Progress values

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).

Find the Remaining values

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.

Find the Today's Date values

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.”

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.”

Format the current date values

Once you have done that, here’s how that column should look:

Custom date format

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.

Enter the helper values

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.

Copy the project start and end values

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.

Convert the date values

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.

Gantt chart worksheet overview

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.”

Create a stacked bar chart

Magically, your stacked bar chart, the starting point of our grand adventure, will appear.

Default stacked bar chart

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.”

Click Select Data

19. In the Select Data Source dialog box, click the “Add” button.

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.”

Add a new data series

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.

Add the custom data series

At the end of this phase, order your data series in the way shown on the screenshot below:

Order the data series

Here’s how your stacked bar chart should look. Don’t worry—we’ll sort out this mess shortly.

Custom stacked bar chart

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.”

Click the Edit button

24. For the “Axis label range” field, highlight all of the tasks from your table (A5:A11) and click “OK.”

Add the task names

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:

Gantt chart

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.

Click 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.

Select Categories in reverse order

29. After that, right-click on the chart area and choose “Change Chart Type.”

Select 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.

Set up a Gantt chart

Having done that, here’s what your chart should look like:

Gantt chart 1

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.”

Choose 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).

Edit the primary axis bound values

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.

Match the horizontal axes

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.

Clean up the axes

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.”

Choose Angle Counterclockwise

Let’s take a quick peek at our Gantt chart. Here’s how it should look now:

Gantt chart 2

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.”

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%.

Change the Gap Width

45. Switch to the Fill & Line tab.

46. Under “Fill,” choose “No fill” to make the data series transparent.

Select No Fill

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.”

Format the current date line

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.”

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.

Add the today's date label

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.)

Fine-tune the current date label

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.”

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.”

Hide the supporting data series

61. Right-click on Series “Remaining,” represented by the orange bars, and choose “Format Data Series.”

Select Format Data Series

62. Click the “Series Options” icon.

63. Change the “Gap Width” value to “30%.”

Set the Gap Width 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.

Select Pattern Fill

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.

Recolor the progress bars

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.”

Add a shadow

73. Repeat the same step for Series “Remaining.” At the end of this step, your Gantt chart should look like this:

Gantt chart 3

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.”

Pick Add Data Labels from the menu

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.

Add the assignee

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:

Gantt chart 4

81. Right-click on Series “Progress,” represented by the light blue vertical bars, and click “Add Data Labels.”

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.”

Add the progress labels

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.”

Format the progress labels

Finally, change the chart title, and you’re all set!

Subscribe to Get Weekly Excel Tips

* indicates required