Microsoft

Pro tip: Create a Gantt chart in Excel

You don't need specialized or expensive software to create a simple Gantt chart. Instead, you can use Excel. Susan Harkins explains how.

Gantt chart

Project managers use a Gantt chart to display scheduled tasks and events in relationship to one another. At a glance, you can ascertain several things about your project:

  • The project's tasks and events
  • When each task begins and ends
  • Which tasks overlap

As the project is underway, you can quickly glean each task's progress. In this article, I'll show you how to create the chart shown in Figure A. You can also download the .xls or .xlsx demonstration file. There are many ways to create a Gantt chart, and we're going to use one of the simplest, which combines conditional formatting and a few formulas. You'll also learn how to enhance the simple chart to make it even more effective.

Figure A

Figure A

You'll learn how to create this sample chart.

The data structure

A Gantt chart lists a project's tasks to the left. Above the chart's bars, you'll enter a timeline of sorts -- a set of values that denote the project's time, which can be days, weeks, or even months. Our example uses days. The chart doesn't list dates, but rather, it tracks a specific number of consecutive days. The start and end values are the days that each task is scheduled to begin and end. For instance, 102 starts on day 4 within the project's scope and ends on day 7, 103 starts on day 8 and ends on day 10, and so on.

The formulas

We'll start by creating the simple chart (without enhancements) shown in Figure B.

Figure B

Figure B

A simple chart.

Use this figure as a guide to enter the labels and values if you want to build this chart on your own. Once you enter the labels and values, you can enter the formulas that generate the bars. This simple formula compares each task's start and end values to the timeline values in F4:Q4. In the case of this example, these values represent days.

This formula takes the following form:

=IF(AND(timelinevalue>=startvalue,timelinevalue<=endvalue),"")

In a nutshell, if the corresponding value in row 4 is greater than or equal to the project's start day, and the row 4 value is less than or equal to the project's end date, the formula returns an empty string. To complete this step, enter the following formula into F5 and copy it to fill the chart area (F5:Q8):

=IF(AND(F$4>=$D5,F$4<=$E5),"")

At this point, you'll see a lot of FALSE values and seemingly empty cells, as shown in Figure C. Those cells aren't truly empty, but rather, they contain an empty string. The conditional format rules that we'll apply next depend on these two values.

Figure C

Figure C

FALSE values and seemingly empty cells.

Two conditional formats

Now, we're ready to apply two conditional format rules that will use the results of those formulas to display the chart's bars. Specifically, a fill color will format the cells containing an empty string and a font color will hide the FALSE values. To implement this step, do the following:

  1. Select F5:Q8.
  2. On the Home tab, click the Conditional Formatting drop-down in the Styles group and choose New Rule. In Excel 2003, choose Conditional Formatting from the Format menu.
  3. In the top pane, select Use a formula to determine which cells to format. In Excel 2003, choose Formula Is from the first drop-down.
  4. In the lower pane, enter the simple expression =F5="" and click Format.
  5. Click the Fill tab, choose the color you want for the bars, and click OK twice. In Excel 2003, click the Pattern tab. Figure D shows the formula and color I chose for the bars in the Preview.

Figure D

Figure D

The formula and color I chose for the bars.

Figure E shows the first rule in place -- your bars! Now, you need to apply a second rule to hide the FALSE values. Repeat steps 1 through 6, but in step 4, enter the formula =F5=FALSE -- and in step 5, click the Font tab and choose white. Doing so hides the FALSE values. If the cells in your sheet are shaded another color, choose that color (not white). I don't normally advise users to hide values, but in this case, I'm making an exception. The results were shown earlier in Figure B .

Figure E

Figure E

The first rule in place.

Display what's completed

At this point, you've got a simple Gantt chart in place and you could stop. But you can make it even more effective by visually distinguishing the amount of the project that's complete. To accommodate this enhancement, insert two columns between E and F. Don't worry about the existing structure and conditional rules -- everything will update accordingly, as shown in Figure F.

Figure F

Figure F

Everything will update accordingly.

The new column F (Dur) contains the following formula to return the total number of days planned for each task:

=end-start+1

To complete this column, enter =E5-D5+1 into F5 and copy it to the rest of the column. Column G is a literal value that represents the task's current status -- how much of it is completed. You'll enter these values as percentages and modify them as your project progresses. (The easiest way to enter a percentage value is to enter the value followed by the % sign.)

Next, enter the following formula into H5 and update the remaining chart area (H5:S8):

=IF(AND($D5+$F5*$G5>H$4,H$4>=$D5,H$4<=$E5),"C",IF(AND(H$4>=$D5,H$4<=$E5),""))

This will generate the chart shown in Figure F.

The new formula is more complex than the first; the logic accounts for the days that are completed and updates those cells accordingly by returning the character C (for complete).

Now, we need a conditional format for the letter C. Using the instructions supplied earlier, enter a new conditional format rule. Specifically, enter the rule =H5="C" and set the font and fill color to the same color. Notice in Figure G that the Preview doesn't show any characters.

Figure G

Figure G

The Preview doesn't show any characters.

Figure H shows the new chart. The lighter green represents how much of the task is completed.

Figure H

Figure H

The new chart.

It's important to note, at this point, that the completed format isn't a true mathematical representation of the completed days. For instance, if a task has only two days and you enter a Done value of 75%, the conditional rule will show both days as completed. It can't break down the percentage by individual cells. It's a close representation, but it won't always be accurate. You'll want to enter Done values that take this into consideration. If this isn't accurate enough, you can break down your timeline values into smaller components, such as hours. It would be more complex than what I've shown above, but it's certainly doable.

Displaying today

Another nice addition would be a highlight that denotes the current day within the life of the project (not the current date). I chose a vertical red border around the appropriate column, as shown much earlier in Figure A. For instance, if C1 contains the value 5, the red border will surround day 5 in the chart area. By doing so, a quick glance tells you that the first two tasks are falling behind schedule.

To implement this second enhancement, enter a literal value in C1. Now, you're ready to add the format rules that use that value. To include the row above the timeline in the moving today format, select the row above the timeline row, H3:S3, and apply a new conditional rule format:

Rule: =$C$1=H$4

Format: Click the Border tab and apply a red line to the left, top, and right borders, as shown in Figure I.

Figure I

Figure I

The new conditional rule format.

Next, select the chart area and the timeline row, H4:S8, and using the same rule, apply a red line to the left and right borders (but not the top as before). Finally, click the View tab and uncheck Gridlines in the Show group. I also added a few more visual tweaks, but nothing that changes the technique. The finished chart is shown in Figure A.

You can continue to add enhancements now that I've shown you the basics. Just remember to update your timeline values to accommodate your project's scope.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible: For instance, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
johan.martens
johan.martens

Oow come on ...

As a project manager, I want thinks to work. I am not going to expensive spend my time in order to imitate something that can be bought off the shelf. The cost of creating something fancy in Excel, will be larger than buying a professional tool. Besides that, there are plenty of free tools out there that allow you to create a ganttchart in no time.

Editor's Picks