Gantt Chart using Google Spreadsheets and conditional formatting

Today's lesson explains how to implement a dynamic Gantt Chart using Google Spreadsheets and conditional formatting (change with rules option).

Take a look at this spreadsheet or copy this spreadsheet in your Google Apps Account

Implement the gantt chart
For explain the topic of this post I prepared a spreadsheet whit a simple work breakdown structure, with only four columns (WBS, activity, start date, finish date).

The question is: how do you do to change a date and update automatically the gantt chart?

You can think to solve the problem in this way, using a simple IF statement: for each activity, if the date's value, contained into the row 2 (in gray), is equal or grater than the activity's start date or the same date's value (row 2) is equal or less then the activity's finish date, add an "X" into the cell. Otherwise leave the cell blank. Formula for the cell C4 is:

=if(AND((E$2>=$C4),(E$2<=$D4)),"X","")

You can copy and paste this formula into the other cells.

So, if a cell satisfies the previous rule, you can use conditional formatting, to change the cell's background from the default color (white) to another color.

Click on cell background color icon and select Change with rules.



Select the following rules:

Text is exactly = X

... then, select the background color you want to apply to the cell.

How you can see in the first image, I used two color: yellow for activities and green for activities'tasks. You can repeat the previous rule changing the color from yellow to green for your tasks.

Take a look at this spreadsheet here.

Similar posts

Implement a Project Plan and manage activities with Google Spreadsheets
Project Management: a project plan with Excel (template) Google Spreadsheets: formulas tutorial