0
421

Earlier I’ve published few tutorials related to chart preparation in Google Spreadsheets using the Insert menu, Chart. It covered combination charts, line graph, bar charts, progress curves etc. Now learn a simple way to prepare a bar chart using formulas and conditional formatting in Google Spreadsheet. Yup! We can learn how to create Gantt chart using formulas.

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 activities and this will visually portray by bars in Gantt chart.

There are spread sheet applications for creating Gantt Charts. Also there are other options which might empty your pocket if you are using for individual purpose. I’ve used MS Project and Primavera sometime back in my carrier. Both are very flexible and you can calculate man hours, cost like several things on these applications.

Without paying a single penny you can create a tricky Gantt chart. 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 Doc.

Steps:
The first formula to be applied on conditional formatting rules.

=and(E\$3>=\$B5,E\$3<=\$C5)

=if(E\$3=\$C5+1,\$A5,””)

You can prepare a Gantt Chart Using Formulas, the above two formulas, in a tricky way in Google Spreadsheet.  See the usage below.

Usage of first formula in Gantt Chart.

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

In the formula “=and(E\$3>=\$B5,E\$3<=\$C5)”, we 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 date in B5 and C5, or same, we apply custom conditional rule in the yellow highlighted range as below.

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

Apply the formula as above. This will automatically colour the cells based on the custom formula rule. This rule you can copy paste to other rows in your data.

Some of you may ask how and why I put the Dollar Symbols between the formula in a different order. Go to this link to learn use of dollar symbols in spreadsheets.

Now the second formula, “=if(E\$3=\$C5+1,\$A5,“”)”,  which is not important. Still if you wish use it.

See the Gantt chart screenshot on top of this page. You can see a description after the bar. The above said logical function will do that. This formula directly apply on cell E5 and copy and paste to other cells.

Hope you learn to prepare Gantt Chart in Google Sheet using formula and conditional formatting. Wish to hear in comments below.