Unsure which chart type to use? Click here to find the right chart for your data and the ideas that you want to convey.
What is a Gantt Chart?
Gantt charts are used to visualize project schedules by allotting a bar for each stage of the project. The length of each bar depends on the length of time allotted for each step, thus showing which stages are expected to take a long time and which ones are expected to be easily completed.
Creating a Gantt chart is a great strategy for project management, as it allows managers to visualize the schedule of a project and monitor progress compared to expectations.
Google Sheets does not have a built-in Gantt chart format, but we can work around it by modifying stacked bar charts to make a Gantt chart in Google Sheets. In this tutorial, we will learn how to format the data for a Gantt chart and then make a Gantt chart from a generic Google Sheets chart. We will also provide a Gantt chart template that you can use to easily create your own chart in Google Sheets.
How to format your data for a Gantt chart
To understand how to format your data for a Gantt chart in Google Sheets, we will need to look at our sample data:
The data for the Gantt chart mostly includes the lengths of time for each step in the project, but it can sometimes include dates. We need to modify the data so Google Sheets can easily convert it into a chart.
We will make a Gantt chart by selecting the stacked bar chart option on the drop-down list. For that, we need to have three columns of data:
- First column: label for each step
- Second column: the total period of time before the step
- Third column: the total period of time of the step
When we format the chart, the data from the second column will be the one originally placed closer to the label on the left side of the chart. We will use a simple trick to hide it after Google Sheets generates the original chart.
If you want to make a Google Sheets Gantt chart with progress bar, you will need to have four columns of data:
- First column: label for each step
- Second column: the total period of time before the step
- Third column: the progress in the task, in terms of the period of time
- Fourth column: the remaining percentage of the task not yet done, in terms of the period of time
The sum of the values of the third and fourth column should equal the length of time allotted for the given task. Often, the progress is described in terms of the percentage: a task at the midway point can be said to be 50% complete while a task that is nearing completion can be said to be 80% complete, 90% complete, or even 99% complete! Given the length of time and the percentage of the task done, how can we determine the values for the third and fourth column? We can use a pair of formulas to do so:
Third column:
= TIME_ALLOTED * PERCENT_DONE
Fourth column:
= TIME_ALLOTED * (1 - PERCENT DONE)
We can add the values for the TIME_ALLOTED and the PERCENT_DONE in separate columns, then use these formulas to the third and fourth column, respectively. This also allows us to easily update the Google Sheets Gantt chart when there is progress. You can see this in action in this section.
How to make a Gantt chart in Google Sheets
For a simple Gantt chart, we will have the following data:
Here are the steps:
1. Select the data, then click Insert on the main menu, then select Chart.
2. In the Chart editor sidebar on the right side of Google Sheets, click the drop-down box under the Chart type label, then select Stacked bar chart.
The chart initially looks as shown below:
To clean it up for a Gantt chart, first delete the legend by clicking on it and pressing Delete. The chart will now look as shown below:
3. To add an axis label, click the Customize tab, then click the Chart axis and titles. On the drop-down box, click the option for the Horizontal axis title.
Type the title text in the box below.
The chart now looks as shown below:
4. Click the Customize tab, then click Series. Select the series by clicking the drop-down box with the text Apply to all series.
Select the blue series from the options shown.
This step is meant to delete the blue bars that are unnecessary to the final Google Sheets Gantt chart, but are important for creating the correct format.
Change the Fill color by clicking the color box under it, then selecting the white color on the palette that will appear. This makes the useless columns invisible, allowing the Gantt chart to only display the important information.
Below is the final Gantt chart with the title modified:
How to make a Gantt chart with progress data
For the Gantt chart with progress data, we start with the following data:
We have added additional columns for the total number of days and the progress percentage for each stage. This way, the Gantt chart can show how many days each step will take as well as how much of the step has been completed.
1. Select the data, then click Insert on the main menu, then select Chart.
2. In the Chart editor sidebar on the right side of Google Sheets, click the drop-down box under the Chart type label, then select Stacked bar chart.
Google Sheets initially creates a chart that looks like this:
To clean it up for a Gantt chart, first delete the legend by clicking on it and pressing Delete. The chart will now look as shown below:
3. To add an x-axis label, click the Customize tab, then click the Chart axis and titles. On the drop-down box, click the Horizontal axis title.
Type the title text on the box below.
The chart now looks as shown below:
5. To hide the unnecessary elements, click the Customize tab, then click Series. Select the series by clicking the drop-down box with the text Apply to all series. Select the extra series from the options shown.
6. Change the Fill color by clicking the color box under it and selecting the white color from the palette.
In our example, we want to delete the blue parts of each bar from our Gantt chart. We selected the blue series, then changed its color to white.
The Gantt chart now looks like this:
As you can see, Google Sheets made relatively good choices on the colors in our Gantt chart. However, if you still want to replace them, follow the steps below.
As an example, we want to replace the orange color with a more reddish color. As you may remember, the columns for the progress and what remains of the task are called “Progress” and “Remaining,” respectively. You may have a different set of names, so take note of what you used.
To change the colors, click the Customize tab, then click Series. Select the series by clicking the drop-down box with the text Apply to all series. Select the orange series from the options shown. Change the Fill color by clicking the color box under it, then select the light red color from the palette.
The final chart:
Key takeaways
We learned one workaround in Google Sheets for creating a Gantt Chart by using the stacked bar chart and setting one important column as white. This is a simple solution that further shows the flexibility of Google Sheets to adapt to your needs.
There are some disadvantages to this method, however:
- You cannot move the horizontal axis to the top. Gantt charts have the x-axis, which contains the date, at the top.
- The date cannot be used for x-axis. If the dates are given, then you must convert them to the number of days.
Gantt Chart template in Google Sheets
Click the link below to find our free Gantt chart template:
You can easily modify this Gantt chart template to fit your needs by modifying the data, causing the chart to automatically update. However, if you need to make wholesale changes to this Gantt chart template, we might recommend following our tutorial to create Gantt charts from scratch.
Recommended articles
More resources
