Excel Gantt Chart (Conditional Formatting)

Do you need an Excel Gantt Chart to track progress of your project or plan or production schedule? Here is a very quick and easy way to do one.

Gantt charts are good for presenting a plan on a timeline and track its progress. It is widely used for representing project plans and production schedules. This chart is basically consist of boxes that represents jobs positioned on a timeline. Length of the boxes vary depending on the length of the job it represents.

We are going to use conditional formatting to make a Gantt Chart in this tutorial. Here are the steps:

1-      We need to make a layout for our chart.  There has to be 3 columns for jobs, start date and job duration. This will make our vertical axis.

2-      For the horizontal axis, we need to specify dates/hours depending on our requirement. It is important to arrange the amount of cells according to minimum time unit you intend to use (if you will use ½hr detail, than arrange one cell for every 1/2hr).

3-      Now resize your cells on horizontal axis (actually your plot area) to make them small boxes.

4-      Input your job name, start time and duration data into related columns.

5-      Select top left cell of your plot area (Cell D14 in below picture) and input following formula: =IF(AND(D$31>=$B14;D$31<=($B14+$C14-1));”X”;””)

6-      Copy this formula to all cells of your plot area. You will get a gantt chart. But it will have X characters instead of boxes. Set font color to a color you intend to use for your boxes.

7-      Now select all of your plot area cells (tiny boxes) and add conditional formatting (conditional formatting -> New Rule -> Use formula to determine …).

8-      Enter this formula for the top left cell:  =D14<>”” .

9-      Set cell fill color to same color as your “X” characters. This colored boxes will be displayed instead of X.

10-   Make sure to check your conditional formatting rule applies to all cells of plot area (=$D$14:$J$30 for my workbook).

You finished functional part of the chart at this stage. It should look similar to the picture below.

Now make some formatting as you like and your gantt chart is ready to serve.

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

For more charting tutorials make sure to visit Charts Category.