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
- First make the above layout in a new excel sheet
- Then we will add several columns in the end, one for each day (or week or month) of the project
- We will also designate 3 cells say $N$5, $Y$5, $AL$5 where we will maintain the following values,
- In cell $N$5, a selection option that will change the plan between “planned” and “actual” dates
- In cell $Y$5, a symbol that we can use to display finished portion of work
- In cell $AL$5, where we can enter the current week (or day or month)
- Now we will do some conditional formatting (ahem!) that will highlight a particular cell in the grid,
- If $N$5 has “Planned” and cell is between planned date and planned date + planned duration
- Else, cell is between actual date and actual date + actual duration
- 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. 😉
- 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.
23 Responses to “Learn Top 10 Excel Features”
What it looks like if excel without formula?? 🙂
It would be not excel it would just be fancy tables in which you could just use power point. (Chandoo) would Access be an alternative?
Awesome piece of work!!!
Great article.
Chandoo - my biggest interest in the article was the awesome word-graphic at the top - where did you go to get it done into a shape?
@Rich.. thank you. I used http://www.tagxedo.com/ to generate this word cloud. I took all the comments in the original post, pasted them in tagxedo website and set up the shape etc.
Awesome Chandoo.. You need always needs coffee to start up with. BTW , how did u created the Heart Shaped picture filled with High Repetitive text in it .. Please put it on your Next blog ...
Chandoo, good article. I’ve added a link to it from Connexion – our collection of the most useful and interesting spreadsheet-related articles from the web. See http://www.i-nth.com/resources/connexion
Hi,
Just one small question. Where the hell have been I in the past for not discovering this website sooner?
I've lost a job interview recently where even though I had the subject knowledge, I was not upto their mark in Excel.
Thank you for all the free tips, guidance and for creating this forum environment.
[PS: I've just been through the site for the 1st time, and have signed up for the newsletter. You can expect pretty stupid questions from me soon]
Hy Chandoo, you always inspire me with to explore something new in excel. This data structure table is only for excel 2007 or compatible to 2010. I recently installed latest excel version 2013 in my System and experience problems regarding operating according to previous one. I'm waiting your article relates to that excel version.
Thanks
Awesome article Mr. Chandoo and that is a awesome heart shaped pic you created. Great tips as well.
[...] Learn Top 10 Excel Features | Chandoo.org – Learn Microsoft Excel Online. [...]
Chandoo is awesome..
Thanks, i got better, And i always get 90.50 in my grade card but now i get 96.50 i improved because of the tutorials you gave, Thank You Very Much Chandoo Guy.
Hi chandoo, i am intersted in seeing the video or step by step done procedure of analysing the comments and presenting in the data percentage steps. I think this one would be first step in finding out how generally happens data calculation. Thank you.
As well i would like to know how to get that black shape art of your face which i see in chandoo. I am interested in making it for me.
Nice to see the features considered by Excel users to be most useful. It might be a good idea to also analyze StackOverflow Excel questions to see what keywords appear most often.
Here are my top 10 Excel Features (for advanced users):
http://www.analystcave.com/excel-10-top-excel-features/
Thanks a ton for this it totally helped with my homework ????
Very good effort
Thank you for this. Lots of learning in the links you've provided for this septuagenarian.
Pls send me new post
Dude, your humor ? ?
Loved your work.
Hello Sir,
I am Sanjeev Khakre and i from Indore City, India , I am your big follower and i have watch your videos and learnt a lots of excel trick or function and many more . thanks so much for all of your excellent support.
Your excel knowledge is real awesome.
Thanks
Sanjeev
Your work is excellent but pls willing to know more details about the features of microsoft excel
Chandoo Would Access be a better alternative than VB?