A Gantt chart in Excel is a specialized visual tool that shows the various stages of completion of multiple related tasks. This kind of chart helps to organize activities that may depend on the start or end times of other activities. For example, the editing of a book cannot begin until the author has submitted a draft of the book to the publisher.
Fortunately, Excel provides a handy template for creating and editing a Gantt chart. You just have to fill in the details, and then you can easily track the progress of your projects! In fact, you can begin using Gantt charts even if you have very little experience in Excel (in which case, you might want to check out this helpful link: What is Microsoft Excel).
This article will explain everything you need to know to build and use Gantt charts.
Gantt Charts: Scheduling
We’ll walk you through the process using a (hopefully) familiar example: scheduling classes at a college or university.
Suppose that your major program requires the following courses, along with the indicated restrictions. (Since this is just a made-up example, only course numbers will be given.)
- 1000 (Entry level course) — one semester course
- 1001 (must have passed 1000) — one semester course
- 1050 (must have passed 1000) — two semester course
- 2100 (must have passed 1001) — one semester course
- 2101 (must have passed 2100) — one semester course
- 3001 (must have passed 1001 and be enrolled in or have passed 2100) — two semester course
- 3300 (cannot start until the second semester of 3001) — two semester course
- 4500 (must have passed 3300) — two semester course
- 4700 (must have passed 3001) — three semester course
Ok, that’s a lot of classes to fit into your schedule, right? And we didn’t even list any of the electives! Let’s use a Gantt chart to keep track of our class dependencies so that you can graduate on time.
Setting up a Gantt Chart
First open Excel, and then choose the tile called Gantt project planner.
Then hit Create, and that takes you to the main Excel sheet where you may edit the items in your chart. Don’t forget to save your chart in a handy location.
You might begin by changing the title to “Course Schedule.” Then list the various courses under “ACTIVITY” (column B). At this point, the chart will still have pre-filled numbers in “PLAN START” and other columns. There will also be a number of colored bars to the right. Those bars are actually responsive to the numbers entered into columns C–G. In other words, as soon as you change a number, the bars to the right will update to reflect those changes.
With the pre-filled values, here is what your chart should look like at this point. Notice that I’ve deleted the rows that will not be used in this example.
Here is a description of what the numerical columns do.
- PLAN START (column C): The starting time period for each activity. In our course-scheduling example, the time periods are semesters.
- PLAN DURATION (column D): The total number of time periods required to complete each activity.
- ACTUAL START (column E): This column can be used to record whether an activity started as scheduled/planned, or if it actually started in a different period.
- ACTUAL DURATION (column F): Sometimes activities may take longer or shorter than planned. This column allows you to record the actual duration of the activity after it has been completed.
- PERCENT COMPLETE (column G): As you work through your project (or, in our example, attend courses from semester to semester), you can update the chart to show what percentage of each activity has been completed.
Entering Start Times and Durations
Assuming that we are just planning and have not started attending any of these classes yet, let’s delete the numbers in columns E, F, and G.
Now, the challenging part of building any schedule is in lining up the start times of activities that depend on completion of other activities. But this is where the interactive features of Excel truly come to the rescue!
First, because the duration of each course is specified ahead of time in our example, let’s fill in column D before updating the start times. (Just leave the numbers in column C whatever default values they were in Excel for now.) Each duration is the number of semesters for each course.
Next, we use the restrictions to update start times. Course 1000 definitely comes first (PLAN START = 1), because that’s the entry level course. Everything else seems to depend on completing 1000 or another course that leads back to 1000.
Change the PLAN START for course 1001 to 2. That way, the start time for 1001 will line up with the end of 1000. Do the same thing for course 1050.
Now course 2100 can’t start until 1001 finishes. So we look to see when 1001 will be complete; according to our chart, 2100 can begin in period 3.
Continue down the list, making sure that each course begins no earlier than its restrictions allow it to.
Updating the Chart
Setting up a Gantt chart is just the beginning! Now as the project proceeds and activities get completed, you will continually update the chart using columns E, F, and G (ACTUAL START, ACTUAL DURATION, and PERCENT COMPLETE).
For example, suppose that you completed course 1000 on schedule. Good for you! Now you can fill in the ACTUAL START and DURATION and put 100% in PERCENT COMPLETE for that course.
However, maybe you didn’t pass 1001 the first time through, and so you’ll have to repeat it the following semester. So you’ll need to update the ACTUAL START for course 1001 to be 3 (and keep the ACTUAL DURATION as 1). Since you are halfway through 1050 at this point, go ahead and put 50% in column G for that course.
Unfortunately every course that depended upon completion of 1001 must now be shifted one semester later. Fortunately, it’s easy to do this by just adding 1 to the PLAN START, or by increasing the ACTUAL START for each course by 1 unit (which is the route we take in this example, but it’s up to you). Keep in mind, nothing will display unless you also have nonzero durations filled in.
Here’s what your Gantt chart would look like now:
Notice the different color scheme for “ACTUAL” versus “PLAN” items.
Suppose now that you worked very hard this semester and did pass 1001 along with the second half of 1050. You can now put 100% in column G for both of those courses.
Presto! Excel correctly shades in the completed activities for you!
Finally, the Excel template for a Gantt chart provides a means to highlight a single period. Simply click on the number next to Period Highlight and choose a number from the drop-down menu.
Here’s what that looks like:
You may notice that this screenshot shows a lot more solid color. I’ve filled in the percentages as if you’ve passed or started the remaining courses on schedule and have just one semester left. In particular, 4500 is halfway done, and 4700 is two-thirds completed.
One quirk of Excel is that the percentage must be rounded (not truncated) in order to show the correct shading in the chart. For example, 66% will only correspond to one out of three blocks shaded, while 67% correctly shades two out of three blocks. This is because 2/3 = 66.6666….%, which rounds to 67% (rather than 66%).
In this article, we saw how a Gantt chart can be used to build and maintain a complex schedule of activities, some of which depend on the completion of others before they can begin.
- Gantt charts can be used in many applications in school, business, and industry.
- Various colors, shading, and placement of blocks correspond to start times and durations.
- Both the planned schedule and the actual schedule can be developed and updated on the same chart.
- Excel has built-in features that can be used to create and edit Gantt charts.