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

Posted on June 16th, 2009 in Charts and Graphs , Featured , Learn Excel - 65 comments

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.
Gantt Chart - Excel - Project Plan

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.

Your Thoughts and Suggestions

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

Project Management Templates for Excel

More...
Subscribe to Chandoo.org Email updates and get a free excel e-book with 95 tips & tricks


Trackbacks & Pingbacks

Comments
marco June 16, 2009

ninja =)

Bruno@Brasil June 16, 2009

Superb!

AlexJ June 17, 2009

Chandoo,
One concern with this approach is that it is often very important to display the Planned and Actual schedules superimposed on each other (not available here). Also, it is not uncommon to save multiple benchmarked versions of the plan along with current re-plan, and to display multiple versions of the plan simultaneously.

This leads me to the thought that the Gantt chart you present will be very effective for a certain class of project, but not for others (ie. where Primavera or MS Project are more appropriate for their power). Can we identify those cases?

Also, it is very clear that Excel is a critically important tool for schedule analysis even when the high-power tools are used. Some of the best uses are for data export/import and graph analysis that far surpass the big boys in flexibility.

Chandoo June 17, 2009

@Alex… I like your point. I have thought about it when writing this post, but I didnt mention the exact method to achieve it here. One reason is I wanted to keep the series and this tutorial simple and yet share powerful ideas.

I think excel based gantt charts are less powerful when you compare with MS project or some other project management tool. But excel has one great advantage that almost everyone in the workplace has it. This is particularly true for large and complex projects where there are several tasks and plans always change.

For eg. in MS Project, you can specify dependencies of a particular task and the gantt and schedules will be arranged automatically. I am sure we can do this in excel too, but it would be too many functions and scaling it would be an issue.

rohit1409 June 17, 2009

Thankyou Sir for great works done by you, I have a point .. I used the following formula to shade the weekly cell and then used conditional formating.. I found the INDEX formula bit complicated.. though i didnt make provision for % done..

=IF($M$7=”Planned”,(IF(AND(H$8>=$C9,H$8=$E9,H$8<=($E9+$F9-1)),”2″,”")))

The value 1 & 2, I have assigned with shades of my choice..
(to which email I can send you my file)..your comments please…

Regards
Rohit Chadha

Pankaj Verma June 18, 2009

Chandoo,

Could you please share link to an excel file for download instead of zip file.
The Zip file hits the firewall roadblock.

Chandoo June 18, 2009

@Rohit: Thanks for sharing your formula. Can you upload the file on skydrive and leave the url here… that way everyone can see your work.. :)

@Pankaj: You can download the .xls file from here

http://cid-b663e096d6c08c74.skydrive.live.com/self.aspx/Public/gantt-chart-project-management-template.xls

Sanjoo June 21, 2009

nice…

Sal Paradise June 29, 2009

One concern with this approach is that it is often very important to display the Planned and Actual schedules superimposed on each other (not available here).

I’m sure it wouldn’t be difficult to finagle the conditional formatting to outline the target, and fill in the actual (or vice versa), or some other form of formatting that will superimpose the two.

The alternative, of course, is creating a graph that does the same, and shows actual versus projected…

Chandoo June 29, 2009

@Sal: I agree. Excel 2003 has a limitation on the number of 3 conditional formats applied on a range of cells. Also the formats stop if true. Excel 2007 none of these limitations. We can use that to show planned and actual both on the grid along with % completion.

Jon July 1, 2009

Ive been playing (learning) with this and took cognisance of the need to show planned and actual on the same graph.
I used the following formula in the cell
=IF(AND(J$8>=$E9,J$8<=($E9+$F9-1),$E9=$C9,J$8<=($C9+$D9))

This got the same effect without the need for a 3rd conditional format.

however id like to use that 3rd conditional format to colour the text “?” based of the %complete column. Could Index be used ?

ill try upload the file later but im at work now. Thanks

jon July 1, 2009

hmm timed out and didnt upload properly ill update later when i get time

Chandoo July 2, 2009

@Jon: Cool, thanks for sharing your formula. However the formula is not working. I think it missed the other two parameters and only specifies the parameters for AND(). If possible, please upload your file somewhere or email it to me at chandoo.d @ gmail.com so that I will be able to upgrade the version in the post.

jon July 2, 2009

thanks for getting back. i tried to update again last night but it didnt work( and i didnt want to look like i was spamming :) )

http://cid-8d9d5762da62b814.skydrive.live.com/self.aspx/.Public/Copy%20of%20gantt-chart-project-management-template.xls

I looked at the comments from the other users and also noticed that the %complete line shows 100% even if its only 90% in column G.
Please note i have only changed Rows 9 and 10.
After having a play i came up with the following formulas.

in the cell formula i have (note addition of the -1)
=IF(AND(H$8>=$E9,H$8<=($E9+$F9-1),$E9=$C9,H$8<=($C9+$D9))
-and-
=H$8=$AL$6

This frees up the 3rd conditional format. I was hoping to reflect the % complete by changing the colour of the “?” based on the %’s and colours in columns AO and AP and was hoping Index could do this.

your advice would be greatly appreciated :)

jon July 2, 2009

(really doesnt like the way im typing this so ill try without the paragraphs. It should read -)
Please note i have only changed Rows 9 and 10.
After having a play i came up with the following formulas.
in the cell formula i have (note addition of the -1)
=IF(AND(H$8>=$E9,H$8<=($E9+$F9-1),$E9=$C9,H$8<=($C9+$D9))
-and-
=H$8=$AL$6

jon July 2, 2009

grr
in the conditional format formulas i have
=AND(H$8>=$C9,H$8<=($C9+$D9))

Chandoo July 9, 2009

@Jon… Thanks for such an elegant solution. I am trying to update the template and provide another download. I will do it this weekend.

Kelly July 13, 2009

I’m feeling very inadequate after reading through these posts (and the tips above). I am a project manager that is trying to get an ‘old fashioned’ (defunked) program under control by making everything uniform. I thought I was doing well with my chart, until it came to learning actual/planned, conditional formatting, and the formulas needed. I’ve been able to figure out a lot of what I’m doing on my own, but I’ve hit a wall with this last part. Can you please explain what the two different colors on the chart represent (light blue, dark blue) and the difference between the proposed and actual bars? I’ve probably taken on more than I should have with a gantt chart, but I’m too far along to give up now. Besides, it’ll drive me crazy until I figure it out. THANK YOU in advance for any help you can lend.

Chandoo July 13, 2009

@Kelly: Welcome to PHD and thanks for your comments.

The spreadsheet uses slightly intricate formulas to show planned or actual gantt chart. The dark blue bars are that. The light blue ones are the portion of work that is finished. This is based on the “% complete” values you enter. I am not sure if my answer is helping you. Let me know if you want more help.

Espri July 17, 2009

Hi Chandoo

Fantastic stuff! How can I change the colors of the cells? I want to change the light blue one.

Chandoo July 17, 2009

@Espri… Welcome to PHD and thanks.

You can change the light blue color (actually it is white, due to the blue background it looks like light blue) by selecting the entire grid and changing the font color. :)

Ta July 28, 2009

Hi, everyone, I just found the very interesting web that open my eye on excel. Can I learn how to create the box with world ‘plan’ and ‘actual’ inside. Also I notice there is a legend sheet, what is that? Thanl You.

Clive July 30, 2009

Hi,
I’m an Excel noob so apologies in advance if the following statements seem foolish. I’m wondering if the following could become part of the tutorial.
I’m currently planning a home extension & will be the de facto project manager. Is it possible to include budgets as a % of total build cost, contractor’s quotes against actual payments to them, etc.
Won’t go into too much detail yet in case this sort of enquiry is inappropriate to the thread.
Thanks,
Clive

Sundeep July 30, 2009

This is really great stuff – easy tool to use. One quick question – How do i change the grid to display 52 weeks or say 35 to 52 weeks. I am no excel guru so any help is welcome.

Chandoo August 3, 2009

@Ta: welcome to PHD. you can learn about adding validation drop down list to a cell by reading this article: http://chandoo.org/wp/2008/08/07/excel-add-drop-down-list/

@Clive: Very good idea. I havent really dedicated a whole post to cost aspect. Since cost is directly proportional to effort I left it out. I will be discussing about effort estimation as part of the 4th installment of this series (will be posting it this week). Meanwhile, you can edit the downloadable workbook on this post and add budget details as well. You might want to take a look at the burn-down charts post here: http://chandoo.org/wp/2009/07/21/burn-down-charts/ and may be use it to indicate budget burn down too.

@Sundeep: Welcome to PHD. You can easily add more columns.If you are familiar with how excel formulas (and conditional formatting) work then it should be fairly easy. Otherwise spend a few minutes reading articles on conditional formatting (here: http://chandoo.org/wp/tag/conditional-formatting/ ) and formulas (here: http://chandoo.org/wp/tag/formulas/ ) and then try it. Good luck.

DES August 12, 2009

Hi,
Thanks for the site.
Is there a way to add milestones to the chart with no duration (similar to MS Project) like start, or meeting, or other no duration events?

Sree August 18, 2009

Great post! I am trying the project management on excel.
Thanks for the tips.

Study Management August 25, 2009

Great stuff. This info post helps me lot…. thanks so much for sharing

Brian September 2, 2009

Not to put a damper into this series of post but using excel as a project management tool (other than unique one/few time use needs) is really a poor idea. Excel is a poor mans database and should only be used with cost reporting for regularly used reports. Even then I do not think it is a great idea. Don’t get me wrong I use it all the time for project management. Not because I want to use excel to create the reports let alone collect the data. It is because my company is to cheap and ignorant (of the processes) to buy the software to do project management functions more effectively in less time. Excel is used in way to many cases instead of a properly designed database by us Finance/Accounting professionals.

Steve September 4, 2009

I needed a quick & inexpensive way to build a decent-looking Gantt chart and this fit the bill perfectly. Thanks for posting it.

Chandoo September 6, 2009

@Brian: I disagree with you on “using excel as a project management tool … is really a poor idea.” part. While excel cannot fulfill a large scale project’s management needs, it works very well for day to day project management stuff be it maintaining issue logs, change logs, listing and tracking activities, reporting, analyzing. It is simple to learn and easy to work with. I have used MS project and a handful of agile software project management tools, but I find excel quite simple and intuitive compared to these complicated tools.

However, excel has a ton of limitations too. To start with, there is no straight forward way to do even the simplest project management activities using excel. You have to make templates, created models and charts before starting to use it for a real project. That is where this series comes in to picture.

My experience is very small and limited. From what I have seen, excel seems like a decent fit when the other choice is learning a complex tool or paying money through nose. But, I am sure you have different experiences and thus different opinion. :)

@Sree, Study and Steve.. thank you :)

Brian September 8, 2009

@Chandoo: I partly agree with your statement that is why I said “(other than unique one/few time use needs)” in my message. I should of added as I did in another post “except for small and simple projects.”
The problem with excel is scalability and multiple users of the same info. Excel is great for making personal logs and list that very few people will use (and have limited amounts of records and fields). I will also say excel is fine for creating very simple schedules. This I agree with, which my post probably did not convey.

However, when a lot of people start using the same sheet, then you run into a whole host of problems. Excel is only there to help one do your job better. My point is you need to look at the alternatives which includes total life cycle cost , creation time, and who will use the information. Spending a little time looking at better ways to do things (which includes taking into consideration everyone’s fully burdened hourly rate) instead of defaulting to excel can save you a lot of money in the long run. There is no point in recreating the wheel if there is software that cost a firm what they pay for 2 or 3 hours of your time.

Jae September 9, 2009

This is an excellent series – and perfect for this situations where (even though you have access to some fancy and more complicated software) – once this is set up – it can be used as a template for those smaller projects (like the ones I always seem to get)…

Thank you for the time you put into this presentation!

Certainly we can all agree that there can be changes made to suite each of our needs – but that’s the neat thing about it…add/remove away – alter as you like…make it your own…

Having said that – Chandoo – I sent you and email to inquire about Pivot Table usage with regards to some portion of this…

Thanks again for the awesome presentation!

Sincerely,
Jae

CS October 25, 2009

..Follow-up to question regarding the colours and the legend sheet

Could you explain exactly how the legen sheet is used and what (and where) the link is between the two sheets

And about the colours, I managed to change the colors related to the ‘actual’ element of the grid by selecting the entire grid and changing the font color, but how do I change the color linked to the ‘planned’ part of the grid – thanks for a great series…!

ben October 27, 2009

Hi,

Really liking this, however the %’s seem to have very little impact aside from 0 and 100. is their a way to make them reflect the %?

Hope that makes sense. Basically if i put 25% or 75% in the amount of area covered is exactly the same.

Thanks

Ben

Chandoo October 27, 2009

@CS: legend sheet is used to define the symbols used in filling the gantt chart completion bars and labels for Planned and actual words. The color is same for both planned and actual. You can edit these colors from conditional formatting dialog. If you are not familiar with conditional formatting, read up this article: http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/

@Ben: I am using a variety of charts called as in-cell charts. That is why may be you are seeing bars in jumps. You can replace the in-cell charts with traditional bar charts so that you can get more fine grained bars…

Also, try with more duration than 2 days (say 30 days) and you should see bars of varying lengths.

Joe Samek November 2, 2009

How easy would it be to also incorporate a ‘Budget’ section showing a vertical bar chart presenting the target budget and actual costs incurred in implementing the project? It would be a nice addition to this or the dashboard template.

Chandoo November 2, 2009

@Joe.. this is very easy to do. Infact you get a gantt chart template (template #1) in the project management bundle that has a vertical bar chart showing activity completions. You can easily configure this to show budget vs. actual spend per activity. See more here: http://chandoo.org/wp/project-management-templates/

Nicole February 2, 2010

I realize this may be very basic but I cannot seem to figure out how to highlight the week column. Some assistance please.

jon February 3, 2010

Nicole as far as i can remember its in Format – Conditional Formatting

Fakhar February 28, 2010

Dear Chandoo,
Today I started this Project Management Series but realized that even after reading Chandoo’s blog I can’t independently prepare Gantt chart as I say this because Chandoo is our hero and champion of novices and non-professionals like me and his blogs are not meant only for MVPs. So my request is please explain the above steps like, how we arrive at % done, how you created the graphs, how you created the combo boxes & lists etc., in laymen terms that is, the hallmark of Chanoo’s blogs, as you explained in an excellent way the conditional formatting and formulas so that even a layman like myself can prepare a gantt chart after reading your blog and say ” long live Chandoo”.
Thanks and kind regards
Fakhar

Jamie Regan March 19, 2010

I don’t understand the difference between actual and % Complete.

In any plans I have, I have a Plan and a % complete. If the Plan changes, as long as you’ve followed a process to justify that change, I don’t see the need to keep the original plan and then have a ‘new plan’, which I assume is what the Actual is being used for here.

Chandoo March 19, 2010

@Jamie… Very good point. I have used Actual vs. Planned view as some times in projects even though plans change, managers (and sponsors) stick to original view and want to ask for justifications for schedule slippages. I have seen this happen in several of the projects where I worked. If you follow agile methodologies or similar ideas in project management, this should not be a problem, otherwise you may want to have actual and planned view of gantt to find differences.

Ashu April 6, 2010

hi chandoo,

this series of yours is very cool. i saw the template which is quite flawless.
however, when i tried to make my own, i couldn’t. i am simply very poor at using excel.

please help me out. it is quite urgent for me..

thanks a ton.. :)

pratik May 17, 2010

hi chandoo,
i like ur work but i want to know the tracking system how to done in ASP.NET…
if u knw about ASP.NET den plz tell me on my e–mail and u have any example of this den also send me….
and i request all my friend who read this comment if u know den u also tell me…
Thank You…

liewi June 22, 2010

hi chandoo, just a line to say thanks a lot dude! that’s very helpful.

Chris July 9, 2010

Love the chart, one thing; I’d like to change the “planned” and “actual” start days from weeks to days. So for example “Activity 1″ has a planned start date of “1″ and actual of “1″
I would like to change to: “Activity 1″ has a planned start date of “7/9/10″ and actual of “7/9/10″.
How can this be done without breaking the formulas??

Thanks,
Chris

Chandoo July 10, 2010

@Chris… Thanks for the comments :) You can easily change everything from numbers to dates. Make sure you have changed the numbers in top row to dates of successive weeks as well. It might take sometime to figure out how this works, but you should be able to do it easily without writing drastically new formulas.

Naveen Agrawal August 29, 2010

Awesome……………………

RSS feed for comments on this post. TrackBack URI

Leave a comment

   Name (required)

   E-mail (required, never displayed)

   URL