Excel Gantt Chart (by Conditional Formatting) Ver.2

I’ve posted an easy Gantt Chart tutorial before that can be done by some smart use of formulas and conditional formatting. I’ve improved it due to a friends request by adding completion indicators. Now your Excel Gantt Chart will show the completion percentage of your jobs.

gantt chart 2If you didn’t read the first post you will struggle here. So I advice you to check it (it is brief). Almost everything will stay the same for this excel gantt chart too. But in order to have our desired changes, we will have to make change some little changes.

First we need to add a new column to left side which will be completion ratio. We will write the ratio of completion to this field and our jobs will partially change color according to this value.

Then we are going to change the formulation we use in our plot area. We need 3 seperate formatting for our plot are since there is now 3 possible conditions in our chart. No job, uncompleted job and completed job. Like we used “X” in previous tutorial, we are going to use “.”(dot) for completed parts and “,”(comma) for uncompleted parts.

We are going to use this formula (instead of the one we used in first tutorial):

=IF(AND(E$28>=$B11;E$28<=($B11+($C11*($D11)-1)));”.”;IF(AND(E$28>($B11+($C11*($D11)-1));E$28<=($B11+($C11)-1));”,”;””))

This formula will put a dot into the cells with completed job, comma into the cells with uncompleted jobs and leave the cells with no jobs blank.

Now arrange conditional formatting as instructed in the first tutorial but use the setting below:

  • First rule puts a left border on the cell which has the same date as cell BH7 (upper right corner). This way you can track current time and see how your project doing.
  • Second rule is for cells with no jobs. We don’t need formatting in them.
  • Third rule is for completed parts. It has green fill and green font color.
  • Forth rule is for uncompleted parts. It has a brown fill and brown font color.

Your Gantt Chart is finished. You can test and use it now.

If you like this chart, you can download the example workbook for this excel gantt chart and check it n detail.

For more charting tutorials make sure to visit Charts Category.

You may also like...

1 Response

  1. Fabio says:

    I would like to know how doing Johnson’s rule chart.

    Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *