Unsure which chart type to use? Click here to find the right chart for your data and the ideas that you want to convey.
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.
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:
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:
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.
For a simple Gantt chart, we will have the following data:
Here are the steps:
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:
Type the title text in the box below.
The chart now looks as shown below:
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:
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.
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:
Type the title text on the box below.
The chart now looks as shown below:
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:
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:
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.
If you enjoyed this article, you might also like our article on how to unmerge all cells in Google Sheets or our article on how to make a gantt chart in Google Sheets.
If you want to learn how to use email address data validation in Google Sheets, we also suggest checking out our detailed guide.
