HomeGoogle DocsSpreadsheetBasic GANTT Chart in Google Sheets Using Stacked Bar Chart

Basic GANTT Chart in Google Sheets Using Stacked Bar Chart

Published on

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.

Basic Stacked Bar Based Gantt Chart

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:

Data for 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.

Sample Data and Chart

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.

Data Formatting - Stacked Bar - Sheets

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))

Setup and Customize - Updated Settings

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:

  1. Series 1 (Project Start) – Set the Fill color to White.
  2. Series 2 (Task Duration) – Set the Fill color to Blue.
  3. Horizontal Axis:
    1. Min value (input 43058, which is the date value of the project start date, i.e., 19-11-2017.)
    2. Number format (choose Custom > Date and Time)
    3. 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:

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here