What is the formula for Gantt chart?

Create Gantt Chart Using Formulas in Google SheetsByPrashanth-We can easily create a Gantt chart using two formulas in Google Sheets. One for highlighting and another for placing t

What is the formula for Gantt chart?

Create Gantt Chart Using Formulas in Google SheetsByPrashanth-

We can easily create a Gantt chart using two formulas in Google Sheets. One for highlighting and another for placing the labels at the end of the bars.

Earlier Ive published a few tutorials related to chart preparation in Google Sheets using Insert menu Chart.

It covered combination charts, line graphs, bar charts, progress curves, etc. Please see the CHARTS menu on the navigation bar above.

Now lets learn a simple way to prepare a bar chart using formulas and conditional formatting in Google Spreadsheet.

Yup! We can learn how to create a Gantt chart using formulas in Google Sheets.

Introduction

Gantt charts are similar to bar charts. The visual appearance is there, but the usage is different.

Gantt Charts are widely used in project schedule preparation.

A work start date and end date will be there for each activity. We will visually portray it by bars in the Gantt chart.

There are spreadsheet applications for creating Gantt Charts. Also, there are other options which might empty your pocket if you are using for individual purpose.

Ive used MS Project and Primavera sometime back in my carrier. Both are very flexible, and you can calculate man-hours, costs like several things on these applications.

But I think you may require some training before starting to use these software products.

Without paying a single penny, you can create a tricky Gantt chart using formulas in Google Sheets.

If you want to create a simple bar chart for your project, follow the below tutorial. We are preparing a Gantt chart using formulas in Google Sheets, the spreadsheet application of Google.

How to Create a Gantt Chart Using Formulas in Google Sheets

First, please create the below sample data in a Google Sheets file.

Gantt Chart Example Data Set

Due to some limitations, I could not capture the full screenshot of the data set.

Dont worry! I will share the link to my sample file after a few paras.

If you check cells B5 and C15, you can understand that the project start date is 1-Jul-2017, and the project end date is 21-Jul-2017.

So the date should be from 1-Jul-2017 to 21-Jul-2017 in the range E3:Y3.

In the above screenshot, you can see the visible range, i.e., E3:O3only.

In cell D4, you can just apply the below DATE formula to get the project duration automatically.=ARRAYFORMULA(DATEDIF(B5:B15,C5:C15,"D")+1)

Here is the link to my Google Sheets file, which contains the Gantt Chart. You can copy the sample data and the above formula from there.

Gantt Chart Link

Steps to Create GANTT Chart in Google Sheets (The Two Formulas)

The following first formula is for applying conditional formatting rules. The second one has to perform a different role.

Formula # 1=AND(E$3>=$B5,E$3<=$C5)

Formula # 2=IF(E$3=$C5+1,$A5,"")

You can prepare a Gantt Chart using the above two formulas in a tricky way in Google Sheets.

I will tell you where to apply these formulas. Before that, see the explanation below.

Steps to create Gantt Chart using formula

Formula # 1 (Highlight Rule)

See the above screenshot. You can see Red, Blue, Green, and Yellow rectangles around the cells. They are for explaining the formula in use.

Formula # 1 is coded using the AND logical function.

We can use the above Formula # 1 to check the date in cell E3 (Green) with the dates in cell B5 (Red) and cell C5 (Blue).

If the date in cell E3 falls between the dates in cell B5 and C5, or its the same, we want to apply a custom conditional rule in E5 (fill Green color), which is as below.

Keep the range E5:Y15 selected and go to Format > Conditional Formatting.

Conditional formatting rule to create Gantt Chart in Google Sheets

Apply the formula as above.

It will automatically color the cells based on the custom formula rule.

Unlike the formula applied in a cell, the formula applied in conditional formatting will behave differently.

We have selected the range E5:Y15.

So the custom formula (Formula # 1) rule will change the cell references based on the $ sign (relative and absolute references) in the formula.

Let us understand how the formula changes in Conditional formatting.

I am inserting Formula # 1 in cell E5 and then copy it to cells E6, F5, and F6.

Its just for an explanation purpose. You should not follow this.

See how the cell references change. Its what happens automatically within the conditional formatting.

gantt chart formula examples

Go to this link to learn the use of dollar symbols in spreadsheets.

Formula # 2 (Adding Labels to Gantt Chart Bars)

In the above step, I have explained how to create a Gantt Chart using a formula in Google Sheets.

Here is the use of the second formula.

Formula 2 above is not so important. Still, if you wish, you can use that.

I forgot to say the purpose.See the Gantt chart screenshot. It adds labels to the end of the Gantt Chart bars in Google Sheets.

finished custom formula based gantt chart in google sheets

You can see a description at the end of each bar. The above said logical formula takes care of that.

Just apply Formula # 2 directly in cell E5 and copy and paste it to all the other cells in the range E5:Y15.

Update on 20-08-2021:

You can replace formula # 2 with the below array formula.=ArrayFormula(if(E$3:X$3=$C5:$C15+1,$A5:$A15,))

Related: How to Use IF Function in Google Sheets  Advanced Tips.

How to apply it?

Select the cell range E5:Y15 and hit the Delete button to empty them.

Then insert the above array formula in cell E5.

Thats all.

I hope you could learn to prepare Gantt Chart in Google Sheets using formula and conditional formatting.

I wish to hear from you in the comments below.

Related:

  • Create Interactive and Beautiful Gantt Chart Online.
  • Create a GANTT Chart in Google Sheets Using Stacked Bar Chart.
  • Split a Task in Custom Gantt Chart in Google Sheets.
  • Create a Gantt Chart Using Sparkline in Google Sheets.
  • Multi-Color Gantt Chart in Google Sheets.
  • TAGS
  • Charts
  • Google Doc
  • SpreadsheetFacebookTwitterPinterestWhatsAppEmailPrashanth

Video liên quan