Gantt chart in Microsoft Excel

Microsoft Excel is termed to be one of the essential spreadsheet tools, and it could be used to visualize the data by just making use of charts as one of the core features of this powerful tool.

Microsoft Excel usually offers a wide range of chart types, each with a specific purpose. Gantt chart is one of the specific types of Microsoft Excel chart that allows users to track their progress in a particular project, task, or other activities.

This article discusses the brief introduction of the Gantt chart, which is effectively used in Microsoft Excel. We also cover the basic steps to create a Gantt chart in Excel easily. The Gantt chart is a beneficial chart type for project managers.

What do we mean by the Gantt chart in Microsoft Excel?

Microsoft Excel is used to represent the breakdown structure of any project by just displaying project start dates and end dates, along with the intermediate relationships between relevant activities. This chart mainly shows projects or related tasks through cascading horizontal bars, which helps us to monitor the project's overall performance for a defined timeline or planned milestone.

Moreover, Gantt charts are essential for visually representing tasks or activities against pre-determined standards. It helps the users to regularly track the progress of tasks, projects, or any relevant activity. Gantt chart is one of the essential tools in the field of project management. Unfortunately, the Gantt chart is not a part of the Excel inbuilt chart. Instead, it is created using a 2-D stacked bar chart that includes durations for tasks and specific formatting.

Note: The Gantt chart is named after Henry Gantt, an American mechanical engineer and management consultant who first introduced this chart type around the 1910s.

What are the uses of the Gantt chart in Microsoft Excel?

The Gantt Charts are mainly used to plan or schedule products, and project managers use this chart type to simplify the entire project in easy steps that can be completed sequentially. Moreover, the chart also helps to determine the project's real-time progress as it progresses. We can easily find out the use of Gantt Charts in almost every industry to plan, schedule and execute specific projects, including construction, marketing, manufacturing, software development, consultation, and event planning.

When should we make use of the Gantt chart in Microsoft Excel?

When it comes to deciding whether we should make use of the Gantt charts or not, we should examine the following requirements:

1. If in case the project has a specific deadline.
2. If multiple people or teams are working on the same project, and must be coordinated as well.
3. If team members work on different project parts, the manager or leader must arrange their workload to make the overall project fluid.
4. If the project leader wants to analyze the visual timeline and progress of the project from start to end.
5. If the project is complex and we need to simplify the workflow in a specific order.
6. If we have an estimate of how much time each project task may take and how many people should be involved to complete a project within the given time.

If any of the above requirements apply to our project, we can consider using the Excel Gantt Chart respectively.

How to create a Gantt chart in Microsoft Excel?

And when it comes to creating Gantt charts in Excel, it is unfortunate that we do not have a direct option to select this particular chart from the Charts section in one click like other popular Excel charts. We should use a bar chart in Microsoft Excel and then arrange its formatting and styles to make it look like a Gantt chart. Each bar plotted in the plot area represents the specific tasks and the time taken to complete the specified tasks.

Creating a Gantt chart is a bit lengthy but easy to create. The same process works for all the Excel versions, including Microsoft Excel 2019, 2016, 2013, and others.

What are the Steps needed to create a Gantt chart in Microsoft Excel?

We are required to follow the below steps to create or insert a Gantt chart in our Microsoft Excel sheet as well:

Step 1: First of all, we are required to create a project table (entering data) in an Excel sheet with the columns, such as the tasks, start dates, and durations. These columns are necessary to create a Gantt chart. Despite this, each task must be listed in a separate row in the column named tasks.

Step 2: Next, we must select the Start Dates with header and insert a Stacked Bar Chart from Insert > Charts > Bar > Stacked Bar, respectively.

Step 3: After that, we must right-click the inserted chart and click on the 'Select Data' option to add the second necessary column data, which means the "Duration Data."

Step 4: After selecting the desired data, such as the start dates and durations, our stacked bar chart with default formatting will be inserted into our sheet.

Step 5: Like in the previous step, we must supply task descriptions to our chart respectively.

Step 6: We need to convert the Stacked Bar Chart to Excel Gantt Chart. We must format the Data Series of Start dates and make the corresponding data transparent or hidden. Since the supplied tasks appear in reverse order by default, we need to format axis options and put the categories in reverse order to arrange the tasks properly.

That is how we can easily insert or create a Gantt chart in Microsoft Excel. Although the process is lengthy, it is better to understand it with an example.

Now let us understand the step-by-step tutorial on creating a Gantt chart in Microsoft Excel with the help of an example and relevant images:

Example: Creating a Gantt chart to track Program Schedule

Now let us consider our monthly activities for software development as an example. We must enter day-to-day activities in an Excel sheet and mention start times, durations, and end times, including the activity names. Each activity's start times and corresponding durations are essential, while the end times are used as references. After creating a project table, our example sheet looks like this:

And in the above sheet, we have to apply the following formulas as well:

• =DATE (year, month, date): Now, to enter start dates and end dates, we are required to use this formula as well.
• = (C2-B2): This is used to calculate the durations.

We need to create a Gantt chart for our month's activities to track our schedule easily. For this, we are required to perform the following steps respectively:

Step 1: First, we must select the start times of the activities (B1:B9). Next, we need to go to the Insert tab and then click on the stacked bar chart just under the Bar Chart section.

Step 2: When we click on the stacked chart, our stacked bar chart will be inserted into our sheet. Our example data looks like this:

Step 3: After that, we must insert the duration series data into the chart. For this, we need to right-click on the inserted chart and then click on the 'Select Data' option, as depicted below:

Step 4: And on the "Select Data Source window," we must click on the Add button respectively.

Step 5: Next, we are required to select the cell D1 for the series name, and then we need to select cells that range from D2 to D9 for series values. After that, we need to click the OK button to add the duration series data to our inserted chart.

Step 6: And after adding the durations in the list just under the "Data Sources window," we just need select Series 1 (or Start Date) and then will click the Edit button as well.

And in the "Edit window," we just need to insert vertical axis values; it usually requires the activities we have entered in the respective sheet.

Step 7: Now, in this step, just after adding the "vertical axis values," we must click on the OK button, and our chart will then add data for other series and the activities. Our example chart looks like this:

And since the "inserted chart" is a stacked bar chart, we must adjust some of the formatting and the preferences to transform it into a Gantt chart, respectively.

Step 8: Next, we are required to remove the "legend," to achieve this, we need to right-click on the "legend" and then click on the option 'Delete.'

Step 9: We need to transform the respective "inserted stacked bar chart" into a "Gantt Chart." Therefore, we must select any of the start dates bars (blue bar in our example), press right-click, and select the option "Format Data Series."

Step 10: In this step, we must select 'No fill' for the 'Fill' option and 'No line' for the 'Border Colour' in the next window, respectively.

And this will make "start date bars invisible," as depicted below:

Step 11: Since our activities in the "inserted chart" are listed in reverse order (from the bottom activity to the top activity per our table), we just need to fix it. And to achieve this, we must double-click on the list of the activities in the "chart area" and select the checkbox for the option 'Categories in reverse order' from Axis Options.

And by doing this, our activities are primarily appropriately arranged, and corresponding date markers are moved to the top side of the chart, as depicted below:

Step 12: Although the desired Gantt chart has been inserted, it is good to improve its design by just removing the empty spaces on the left-hand side, changing out the colors, adding a title, adding the data labels, etc. After making some changes, our example Gantt chart will look like this:

Saving Microsoft Excel Gantt chart as Template

Since creating a Gantt chart in Microsoft Excel is a bit lengthy, it is good to save the created Gantt chart as an Excel template. So, whenever we need to use the Gantt chart again, we can efficiently work on the saved Template and modify the data accordingly.

And to save the inserted Excel Gantt chart as an Excel template, we are required to complete the below-mentioned steps effectively:

Step 1: While saving the chart as a "template" in Microsoft Excel 2013 and the higher versions, we are required to press right-click on the inserted chart and then click on the 'Save as Template' option.

Moreover, in Microsoft Excel 2010 and the lower versions, we are required to go to the Design tab that we can access after selecting the inserted chart" as well. Later on, we need to click on the tile or the particular shortcuts that are associated with the 'Save As Template' option, as depicted below:

Step 2: And just after clicking on the 'Save as Template,' we will see a 'Save Chart Template' dialogue box; we can quickly enter the desired name for a template and choose a location to save it.

By default, Microsoft Excel saves the chart templates in a specific folder on a computer so that the templates are automatically added to the Templates section. If we save a template in a default location suggested by Excel, we can select the Template quickly from the Insert Chart or Changer Chart Type dialogue boxes.

What are the Advantages of using Microsoft Excel Gantt Chart?

Some of the significant advantages of using Microsoft Excel Gantt charts are listed below:

1. Gantt charts help to represent complex data sets in a single diagram graphically in an effective manner. It quickly communicates practical or analytical insights into the works to the readers.
2. With some knowledge of Microsoft Excel charts, one can quickly determine the specified tasks' current status or progress in a Gantt chart.
3. Proper planning is the primary step of any task, project, or activity, and it is an excellent practice to make the plan as realistic as possible. The "Gantt chart" in Microsoft Excel is a unique tool that helps project managers visualize the entire movement realistically.

What are the various disadvantages associated with using the Microsoft Excel Gantt Chart?

Some of the significant disadvantages of making use of the Microsoft Excel Gantt charts are listed below:

1. Making a Gantt chart with many tasks or activities sometimes takes time to understand and looks messy.
2. The bar sizes in Gantt Charts do not properly indicate the overall weight of the particular tasks.
3. Gantt charts require regular forecasting or updating, which is usually time-consuming.

What are the essential things to remember about using the Gantt chart in Microsoft Excel?

The various essential things that need to be remembered by making use of the Gantt chart in Microsoft Excel are as follows:

1. We should determine the tasks to be completed in the projects and specify the time required for each task. This will simplify the overall process in Gantt charts.
2. We must avoid using complex data structures while creating or working with Gantt Charts.
3. We should avoid using too many values for the X and Y-axis. Using several x or y-axis values, the inserted chart will become so long that it won't look nice and reader-friendly.