Create a Gantt chart in Excel
Gantt charts, which was invented by Henry Gantt in the 1910s, are generally used in project management. It can help to plan, manage and track your projects visually, free you from large task data tables. This tutorial is going to show you how to create Gantt chart in Excel.
The below Gantt chart displays the tasks from two perspectives. When selecting Plan from the drop down list, the Gantt chart only displays the planned duration for tasks. After changing the value to Actual, you can see a green bar displays inside the gray one, which represents the progress of the task. And if the task is completed in advance or postponed, changes will make to the length of the gray bar.
Create a simple Gantt chart in Excel
Create a dynamic Gantt chart showing task progress
Create an online Excel Gantt chart template
Easily create a Gantt chart with an amazing feature
Download the sample file
Video: Create gantt chart in Excel
Create a simple Gantt chart in Excel
Prepare data
Supposing you have a task table containing the task name, start date and end date columns as the below screenshot shown. For creating a simple Gantt chart, you still need to create a duration column.
Select a blank cell (in this case, I select D2 to output the first duration), enter formula =C2-B2 into it and press the Enter key. Select the formula cell, and then drag the Fill Handle all the way down until all durations are displayed.
Create Gantt chart
1. Select the start date column (without the header), go to the Insert tab, and then click Insert Column or Bar Chart > Stacked Bar. See screenshot:
2. Then the chart is inserted into worksheet, right click on the chart and then choose Select Data from the right clicking menu.
3. In the Select Data Source dialog, click the Add button.
4. Then the Edit Series dialog pops up. Please type in the series name, select the Duration column range (exclude header) in the Series values box, and then click the OK button.
5. When it returns to the Select Data Source dialog box, select the Series1 in the Legend Entries (Series) box and then click the Edit button in the Horizontal (Category) Axis Labels box.
6. In the Axis Labels dialog box, select the task description column range (without header), and then click the OK button.
7. When it returns to the Select Data Source dialog box again, you can see the original series numbers are replaced with the task descriptions. Click the OK button to save the change and close the dialog.
As you can see, the task descriptions on the chart are listed in reverse order. You need to rearrange it to match the order of the original data.
8. Select the Axis label, right click and select the Format Axis from the context menu.
9. In the Format Axis pane, check the Categories in reverse order box under the Axis Options tab.
Now the task descriptions are listed in a normal order.
10. Click any one of the blue bar to select them all, right click and select Format Data Series from the right-clicking menu.
11. In the Format Data Series pane, go to the Fill & Line tab, and then choose No fill and No line options separately in the Fill and Border sections.
Now all the blue bars are hidden in the Gantt chart.
12. Go to select the first start date in your range, right click it and select Format Cells option from the context menu. And in the Format Cells dialog box, click General in the Category list box, remember or write down the number on the right hand, and then click the Cancel button to close the dialog.
13. Click to select the whole date axis. Right click it and select Format Axis.
14. In the Format Axis pane, enter the number you have written down in step 12 into the Minimum box, and then adjust the Major and Minor units as you need.
Now the Gantt chart is displayed as the below screenshot shown.
You can apply other formatting to decorate the Gantt chart as desired.
Finally, the Gantt chart is displayed as the below screenshot shown.
Create a dynamic Gantt chart showing task progress
But in many cases, tasks may complete in advance or postpone. In this section, we are going to create a full Gantt chart not only showing the actual days you spent for tasks but also displaying the tasks progress.
First, prepare your data
1. Calculate the plan duration for each task. As the below screenshot shown, you need to manually create a duration column (If there is already a duration column in your table, ignore this step).
Select a cell to output the duration for the first task, enter the below formula into it and press the Enter key. And then drag the Fill Handle of this formula cell all the way down to get all durations.
=D4-C4
2. Create a new table containing the actual days you spent for tasks (the end dates may be different with the original ones). Calculate the actual durations with the below formula. Besides, add a percentage completion column.
=D16-C16
3. In cell I4, insert a data validation drop down list with source value “Plan duration” and “Actual duration”.
4. Finally, create the third project timeline table. We will create the Gantt chart based on the data of this table.
Notes:
Create a dynamic Gantt chart with the data of the Project timeline table
1. Firstly, do the same operation as above from step 1 to 1 to create a normal Gantt chart based on the data of the Project timeline table.
When the Gantt chart is displaying as the below screenshot shown, go ahead to add the task progress.
2. Select the invisible bars, click the Chart Elements button, and then check the Error Bars box. See screenshot:
3. Right click one of the error bars and then select Format Error Bars from the right-clicking menu.
4. In the Format Error Bars pane, you need to:
5. In the Custom Error Bars dialog box, select the Progress column in the Positive Error Value box and then click the OK button.
6. Now the Error bars are still selected, go to the Fill & Line tab in the Format Error Bars pane, specify a color and width for them.
Tips: You need to select “Actual duration” in the drop down list to display the task progress in the Gantt chart.
Now the Gantt chart with task progress is created as the below screenshot shown.
Tips: You can apply other formatting to decorate the Gantt chart as desired. Such as:
Here is the dynamic Gantt chart with task progress.
Create an online Excel Gantt chart template
Besides, Excel provides free online Gantt chart templates. In this section, we are going to show you how to create an Excel online Gantt chart template.
1. Click File > New.
2. Typing “Gantt” into the search box and then press the Enter key.
3. Now all Excel online Gantt chart templates are searched out. Click on one of the templates to create it as needed.
4. Then popping up a window contains the preview and introduction of the selected Gantt chart. Click the Create button.
5. Then the specific Gantt chart is created in a new workbook. You just need to replace the existing data with the data you need in order to make the Gantt chart useful.
Easily create a Gantt Chart in Excel
If the above methods are duplicate and time-consuming for you, here highly recommend the Gantt Chart utility of Kutools for Excel. With this feature, you can easily create a Gantt Chart in Excel with several clicks only as the below demo shown.
Download and try it now! 30-day free trail
Download the sample file
Video: Create gantt chart in Excel
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
