Gantt Charts – Project Management Using Excel [Part 1 of 6]

Starting this week we are starting a new series of posts on project management using Microsoft excel. I have been working in various projects in the last 6 years and almost in all cases we have been using excel to manage, measure and track various aspects of project. These posts represent few of the things related to project management using excel that I have learned over the years.

Part 1: Preparing & tracking a project plan using Gantt Charts
Team To Do Lists – Project Tracking Tools
Project Status Reporting – Create a Timeline to display milestones
Time sheets and Resource management
Issue Trackers & Risk Management
Project Status Reporting – Dashboard
Bonus Post: Using Burn Down Charts to Understand Project Progress

Excel, because of its grid nature provides a great way to prepare and manage project plans. In this part of the project management using Microsoft excel series we will learn how to prepare and track a project plan using gantt chart in excel.

Preparing a project plan

Not all project plans are same. But most of the project plans have a list of,

• All activities / phases of project
• Planned start date of the activity
• Planned duration of the activity

From tracking perspective, we can add the following,

• Actual start date of the activity
• Actual duration of the activity
• % of the activity completed as of date

As you can see, excel provides a great way to manage such plan. Look at an example project plan made in excel.

But the above plan is more or less static. Using Excel’s features we can make a dynamic gantt chart that can,

• Update the Gantt chart when dates change
• Display a separate bar that will grow based on the % completion of each activity
• Highlight current week / day in a subtle way

In essence, we will create something like this:

Steps for preparing an Gantt Chart

1. First make the above layout in a new excel sheet
2. Then we will add several columns in the end, one for each day (or week or month) of the project
3. We will also designate 3 cells say $N$5, $Y$5, $AL$5 where we will maintain the following values,
   1. In cell $N$5, a selection option that will change the plan between “planned” and “actual” dates
   2. In cell $Y$5, a symbol that we can use to display finished portion of work
   3. In cell $AL$5, where we can enter the current week (or day or month)
4. Now we will do some conditional formatting (ahem!) that will highlight a particular cell in the grid,
   1. If $N$5 has “Planned” and cell is between planned date and planned date + planned duration
   2. Else, cell is between actual date and actual date + actual duration
5. We will also write formulas in all the cells (same formula pasted over the entire range) which displays a symbol like solid rectangle. For finding out if we should fill in the symbol or not, we use the % completed column of the gantt chart. Figuring out this formula is part of your home work. 😉
6. Finally we will adjust formatting like column widths, fonts, colors etc. and freeze top row so that it is easy to scroll and still know what you are looking at.

Once you prepare such plan it is easy to track, find out the status of individual activities and take necessary corrective actions as needed.

Download Excel Gantt Chart Template and Make your own project plan

Feel free to download gantt chart project plan template and make your own project plans using Microsoft Excel.

Download 7 Gantt Chart Templates and 17 other Project Management Templates for Excel – Click here

What next?

In the next part of this series we will understand how to manage day to day activities of projects using to do lists in excel.

Resources for Project Managers

Check out my Project Management using Excel page for more resources and helpful information on project management.

Also check out below pages:

• Project Status Dashboard – Excel template
• Project Portfolio Dashboard
• Gantt Box chart – for showing uncertainty in project
• Excel Risk Map Template

Your Thoughts and Suggestions

Do you work a lot on project management activities? Do you find this content useful? share your feedback and experiences through comments.