Making A Gantt Chart In Excel (Quick & Easy!)

In this article, we will look at how to create a Gantt chart in Microsoft Excel. Gantt charts assist with project planning since they allow you to schedule and plan project activities.

If you would like to learn more about other advanced features and functions in Excel then please join one of our top-rated Excel courses.

 

What Is A Gantt Chart?

A Gantt chart is a type of visualisation tool that is used to display a project plan. Usually, one will see a list of activities/tasks on the left hand side while the right hand side graphically shows the duration of each activity/task.

The Gantt chart was developed by Henry Gantt during the early 1900s and it is named after him. He was a mechanical engineer and management consultant. He developed the Gantt chart to record the daily activities of employees, in terms of the time it took to reach milestones.

Want to learn more about mathematics in Excel? Read our guide here all about Percentages in Excel.

You can use Gantt chart software such as MS Project or Smartsheet to create a Gantt chart.

Microsoft Excel does not have a built-in Gantt chart option, but you can create one easily by using a template or by modifying an existing Stacked Bar Chart.

In this post, we are going to review these two techniques.

Learn more about how to create charts in Excel here.

 

Gantt Chart Template – Simple Example

Microsoft provides a number of useful Excel templates that you can utilize to create customized spreadsheets. So, let’s see how to create a simple Gantt chart in Excel by using a template.

 

Description of the Data Set

A hypothetical aspiring author plans to write a short story.

A short story is typically a brief work of prose or fiction, that is much shorter than a standard novel. Short stories are usually between 1000 and 7500 words.

The main advantages of writing and publishing short stories in literary magazines, is that they allow new writers to quickly build a portfolio of work and thus attract readers.

 

The author wants to use a Gantt chart to schedule the main tasks/activities involved in writing the short story. The activities involved are the following.

She would like to start the project on 10 March 2022.

  • She wants to spend 7 days choosing a setting for her short story.
  • Consequently, she plans to spend 12 days deciding on the central message or theme of her short story.
  • She then wants to spend 10 days developing the character profiles of the two main characters in her short story.
  • She wants to spend 8 days outlining the plot of her short story, afterwards.
  • She then wants to spend 2 days actually writing her short story, using the building blocks she completed thus far.

The author would like to take a two day break between each activity.

Gantt charts are also great tool for planning SEO projects. For more check out this article on Excel for SEO training.

Step 1:

With a blank workbook opened, go to the File Tab and click New.

 

Screenshot showing the New Option in the File Tab, highlighted.

 

Step 2:

Type Gantt Chart in the Search Box and click to start searching.

 

Screenshot showing the term Gantt Chart entered into the Search Box.

 

Select the Simple Gantt Chart template.

This is a template provided by Vertex42 and can also be downloaded directly from their site.

 

Screenshot showing the Simple Gantt Chart template selected and highlighted.

 

Click Create.

 

Screenshot showing the Create button highlighted.

 

You should see the following.

 

Screenshot showing the spreadsheet generated from the template.

 

Step 3:

We will now customise the template to suit our needs.

Firstly, we will enter the new title which is Writing The Short Story in cell B1. We will then delete Company Name and Project Lead.

 

Screenshot showing the first stage of customization of the Gantt Chart template.

 

Now we will change the Project Start Date to 10 March 2022. You will see cell E9 automatically updates with the new Project Start Date, due to the formula that is in cell E9.

 

Screenshot highlighting the new start date and the value updating in cell E9 due to the formula.

 

Now we will change the text Phase 1 Title to Schedule of the main activities involved in writing the short story. We will change the values in range B9 to B13 to reflect the main activities.

In range C9:C13 we will enter Author, since the same person is responsible for all the activities in this case.

In cell D9, we will enter 0 as the progress since nothing has been completed, as yet.

You should see the following.

 

Screenshot showing the results of customizing the template.

 

We will change the end date for our first activity, to reflect that we want our end date to be 7 days after our start date. The formula in cell F9 is currently =E9+3 and we are going to change this to E9+7.

You should see the following.

 

Screenshot showing the formula in cell F9.

 

Now after doing this, you should see that the formula in cell E10, which shows the start date for the next activity automatically updates to reflect the end date of the previous activity.

Now this is fine in the case where one starts the next activity as soon as the previous one has been completed. In our case however, the author would like to take a two day break between each activity.

So, to reflect this in cell E10 enter the formula =F9+2. In cell F10, which reflects the end date for this activity change the formula to =E10+12. Since the author wants to take 12 days from the start of this activity to decide on the central message or theme.

Change the progress to 0%.

You should see the following.

 

Screenshot showing the template which has been customised with the user data.

 

Change the rest of the start dates and end dates accordingly. Change the progress to 0% for all activities.

 

Screenshot showing the rest of the spreadsheet populated with start dates and end dates for each of the activities listed.

 

If you’d like to see the last activities then you can either manually scroll across or change the Display Week value to 5, to see this view of the Gantt chart from the perspective of week 5.

 

Screenshot showing the Gantt chart with the Display Week value changed to 5.

 

Step 4:

Since we are not using rows 14 to 31, we can delete these rows.

So, select rows 14-31 and go to the Home Tab (Step 1 in the image) and in the Cells Group, click on the Drop-Down arrow under the Delete button and select Delete Sheet Rows (Step 2 in the image).

 

Screenshot showing the Delete Sheet Rows option.

 

You should now see the following.

 

Screenshot showing the Gantt Chart.

 

Step 5:

In order to use this Excel Gantt Chart to track progress, you would change the percentages accordingly in column D.

So, let’s say the project has started and the author has completed 74% of the first activity. All we would need to do is enter 74 in cell D9 and the Gantt chart will be updated as shown below.

 

Screenshot showing the Gantt chart with the progress for the first activity highlighted.

 

Tip: If you unhide column H, you will see the total duration of each activity including the start day.

If you would like to learn more about how to summarise a workflow in Excel using a flow chart then read our guide here.

 

How To Make A Gantt Chart In Excel – Step By Step

We will look at how to modify a stacked bar chart to create an Excel Gantt chart. We will use the same data set as in the above example.

 

Step 1:

So, the first thing that we have to do is set up our project table. You will need to set up your data in this way in order to create the Gantt chart.

So, enter the activity names in the first column. The start dates for each activity in the second column, and the duration in the third column.

 

Screenshot showing the Project Table.

Step 2:

Now select the range A4:C9. Go to the Insert Tab (Step 1 in the image) and in the Charts Group, click on the drop-down arrow by Column chart and in the 2-D Bar options, select Stacked Bar (Step 2 in the image).

 

Screenshot showing the 2D Stacked Bar Chart option highlighted.

 

You should see the following.

 

Screenshot showing the chart that has been created.

 

Step 3:

We are now going to modify this chart to create our Gantt chart.

So, click on the + sign for Chart Elements and uncheck Legend.

 

Screenshot showing the Legend option unchecked.

 

Change the Chart Title to Project Gantt Chart.

We can see that the order of our activities has been reversed. In order to change this, select the activities on the chart and right-click and choose Format Axis.

 

Screenshot showing the Format Axis...option highlighted.

 

Using the Format Axis Pane, check Categories in reverse order.

 

Screenshot showing the Categories in reverse order checked.

 

You should see the following.

 

Screenshot showing the chart with the activities in the correct order.

 

Now select all the blue bars by clicking on one of them. Go to the Home Tab (Step 1 in the image) and in the Font Group, select the Fill Colour option and choose No Fill (Step 2 in the image).

 

Screenshot showing the No Fill option.

 

You should see the following.

 

Screenshot showing the Gantt Chart with the blue fills no longer visible.

 

Step 4:

So now what we’d like to do, is modify the chart further in order for the first bar to start on the date of the first activity.

To do this we have to get the general format for the first date. So select cell B5 and press Ctrl+1 on the keyboard to open the Format Cells Dialog Box.

Note the number that appears when you click on the General option. This is 44630 in this case.

 

Screenshot showing the Format Cells Dialog Box with the General format number highlighted.

 

Now select the dates on the chart and right-click and select Format Axis.

 

Screenshot showing Format Axis... highlighted.

 

In the Bounds section change minimum to 44630.

 

Screenshot showing the Minimum Bound option highlighted and changed to the new value.

 

You should see the following.

 

Screenshot showing the Gantt Chart.

 

For more on Excel and Visual Data Representation, view our guide here on Data Models In Excel.

 

Customize Your Gantt Chart

You now can change the appearance of your Gantt chart by changing the theme. Themes in Office, are collections of co-ordinated colours, fonts and effects.

Themes can be applied to a document, presentation or workbook in order to change the overall appearance.

So go to the Page Layout Tab (Step 1 in the image) and in the Themes Group, choose the Themes drop down arrow and select one of the themes. In this case we will choose Gallery (Step 2 in the image).

 

Screenshot showing the Gallery theme highlighted.

 

You should see the following.

 

Screenshot showing the Gantt chart.

 

Now you know how to use a Gantt Chart, why not move on to more? Read our guide here on the Growth Function In Excel.

 

Gantt Chart Use Cases

Gantt charts are utilized in many industries. Gantt charts are used to show the progress of the activities on construction sites and by event planners to structure and plan events.

 

Learning Objectives

You now know how to:

  • Download and modify a template to create a Gantt Chart in Excel
  • Create a Gantt Chart by modifying a stacked bar chart

Additionally, you have an understanding of:

  • When to use a Gantt Chart

 

Conclusion

You can use Gantt charts to track milestones and monitor project tasks. Excel Gantt Charts are useful for both the beginner and professional user to know about.

Looking for more Excel tips? Check out the Ultimate Guide To Advanced Filters here!

Special thank you to Taryn Nefdt for collaborating on this article!

 

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.