Let’s see how to make a basic GANTT Chart in Google Sheets with the help of the Stacked Bar Chart.
It is purely a basic GANTT chart and not suitable for big projects. But I am sure it will be handy for your personal use/projects.
I am telling this because this Gantt chart has no features like grouping the component tasks, linking tasks, or set dependencies.
Update: I have improved this chart a little bit using the new chart features in Google Sheets. See the chart (series color blue) at the end.
If you want more control, you can depend on Conditional formatting or use Sparklines. I’ll give you the relevant links in the last part of this guide.
We can see how this basic GANTT chart in Google Sheets visualizes the breakdown of a project and its component tasks.
How to Make a Basic GANTT Chart Using Stacked Bar Chart in Google Sheets
Sample Data for the basic Stacked Bar Gantt Chart:
First, prepare the above dataset in a blank sheet keeping the above data range A1:C10. After that, we need to create a new dataset from this data that suits our basic Gantt chart in Google Sheets.
To save your valuable few minutes, I am leaving a copy of my sheet that contains the data and the chart. The relevant tab name is “Sheet1”. There is one more tab named “Copy of Sheet1,” which contains our updated chart. I’ll come to that later.
Steps to Follow (Data Formatting)
From the above sample data, copy and paste only the Tasks in A2:A10 to A12:A20. Please refer to the below screenshot.
1. Let’s first find after how many days from the “Project Start”, each task starts.
Use the following formula in cell B13, i.e., under “Start on Day,” and then copy and paste it down
=DATEVALUE(B3)-DATEVALUE($B$3)
It would return the number of days from the project start date of each task.
Alternatively, insert the below DAYS array formula in cell B13 to generate the above number of days in the range B13:B20.
=ArrayFormula(days(B3:B10,B3))
You May Like: How to Utilise Google Sheets Date Functions [Complete Guide].
2. Finding the duration of each task.
To find the duration of each task, insert the following formula in Cell C13, under “Task Duration,” and then copy and paste it down.
=DATEDIF(B3,C3,"D")
I know some of you may like Array Formulas. If so, use the below formula in cell C13 as an alternative to the DATEDIF above.
=ArrayFormula(days(C3:C10,B3:B10))
We can now use the data in source A12:C20 to make the Gantt chart using the built-in stacked bar chart. Here is how.
Stacked (GANTT) Bar Chart Preparation
Select the data in A12:C20, then go to the menu Insert > Chart.
From the chart editor panel, change the “Chart Type” to “Stacked Bar Chart.” Now the tricky part. Under Series, change the “Start on Day” series color to ‘None’ and voila!
Our Basic Gannt chart using the Stacked Bar chart is ready in Google Sheets.
Enhancement in the Basic Stacked Bar Based GANTT Chart
Google has updated its charts interface dramatically. So time to modify our chart too.
Now we don’t require the helper cell range A12:C20. Instead, we need a new cell range alongside the existing data in A2:C10.
Yep! in cell D2, type “Task Duration.”
In D3, key in =ArrayFormula(days(C3:C10,B3:B10))
Here are the important chart settings to make the above basic Gantt chart based on Stacked Bar in Google Sheets.
Setup and Customize Settings (Within Chart Editor)
Setup:
Chart Type – Stacked bar chart.
Stacking – Standard.
Data range – A2:D10.
Y-axis – Task.
Series 1 is “Project Start” and 2 is “Task Duration.”
Customize:
- Series 1 (Project Start) – Set the Fill color to White.
- Series 2 (Task Duration) – Set the Fill color to Blue.
- Horizontal Axis:
- Min value (input 43058, which is the date value of the project start date, i.e., 19-11-2017.)
- Number format (choose Custom > Date and Time)
- Check/enable “Allow bouds to hide data.”
That’s all. You can check these settings within my above-shared sample sheet. The tab name is “Copy of Sheet1.”
Resources:
- Create Gantt Chart Using Formulas in Google Spreadsheet.
- How to Create a Percentage Progress Bar in Google Sheets.
- Create a Gantt Chart Using Wrike Online Project Management Software.
- Create a Gantt Chart Using Sparkline in Google Sheets.
- Split a Task in Custom Gantt Chart in Google Sheets.
- Multi-Color Gantt Chart in Google Sheets.