0
159

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 Gannt chart using formulas.

Gannt charts are similar to bar charts. The visual appearance is there. But the usage is different. Gannt 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 Gannt chart.

There are spread sheet applications for creating Gannt 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 Gannt chart. If you want to create a simple bar chart for your project, follow the below tutorial. We are preparing a Gannt 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 Gannt Chart Using Formulas, the above two formulas, in a tricky way in Google Spreadsheet.  See the usage below.

Usage of first formula in Gannt 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 Gannt 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 Gannt Chart in Google Sheet using formula and conditional formatting. Wish to hear in comments below.

SHARE
A technology enthusiast and addictive blogger who likes to travel and wish to mingle with different community of people from around the world.