Excel Project Management – Free Templates, Resources & Information
Project Management is one of my favorite topics. In my earlier job I have been part of several projects and in almost all of them, we have used Microsoft Excel heavily to perform various project management tasks. In this page you can find some resources, help and articles on Project Management using MS Excel.
Click on the links to access relevant section:
- Gantt Charts & Project Planning
- Project Reporting, Project Dashboards
- Time-sheets, Trackers and Other Project Tools
- Charts Depicting Project Status etc.
- MS Excel Formulas for Project Managers
- Excel Project Management Templates
- Resources & Links
- Excel for Project Managers – Training Program
Gantt Charts & Project Planning using MS Excel
A Gantt Chart is a traditional way to prepare and track a project plan. It shows activities on the left and dates on top. Intersection of an activity and date cell is highlighted if that activity is to be done on that date. Gantt Charts can provide these optional information too,
- Planned vs. Actual view of the project
- % Completion of activities
- Overall project progress / status
- Activities on Critical Path
An Example Gantt Chart:
Preparing Gantt Charts in Microsoft Excel:
Excel, because of its grid nature provides a great way to prepare and manage project plans. We can use Excel features like Conditional Formatting and Formulas to prepare a comprehensive gantt chart depicting almost any project. Since Excel is a very common software and most managers know how to use it, using excel to prepare and maintain gantt charts can be very productive and easy.
Free Gantt Chart Templates:
- A Simple Gantt Chart using Excel Conditional Formatting
- Gantt chart with Planned vs. Actual Comparison
- Gantt Chart using Excel Stacked Bar Chart
- Gantt Chart with Activity Grouping
Project Reporting and Project Management Dashboards
Reporting is one of the most important aspects of project management. When I was leading projects, I used to get questions like,
- How is the project going?
- What are the important issues we should worry about?
- What is the progress of activity x?
- Are we on track with respect to budget, resources etc?
from my project stake-holders and sponsorship team.
Answering these questions will be very easy if you have prepared a good project management dashboard. A Project Management Dashboard provides all the crucial information about a project in a snapshot.
An Example Project Management Dashboard:
How to make Project Management Dashboards in Excel?
Excel shines again as the perfect tool for creating project dashboards. Due to various powerful features like excel charts, conditional formatting it is very easy to setup and update a comprehensive project status report in excel. I have used these concepts to construct several project management dashboards in excel. Follow these links,
- Project Management Dashboard using Excel
- Project Status Dashboard with Short Messages a la Twitter
- More resources, templates and examples on Excel Dashboards
Timesheets and Project Management Tracker Tools
One of my bosses used to say “If you cannot measure, you cannot manage“. A good project manager always stays on top of various things going on in a project. And that is where tracking comes in to picture. On any given day, a project manager is found tracking,
- Issues in the project
- Risks in the project
- On going Activities in the project
- To do items of various team members
- Timesheets and Billing
- Budget and Money
Issue Tracker using Excel – An Example
Using Excel to Prepare Project Tracking Spreadsheets
Excel is the natural choice for tracking various project items. Due to its simplicity and ease of use, project managers can easily create a template in excel to track something and then share the template with team members to track it. Below are some examples of excel based tracking tools for project management.
- Excel Timesheet Template
- Excel Issue Tracker Template
- Excel To Do List Template
- Excel Scope Items Template (Gantt Chart with Plan vs. Actual View)
Project Management Charts
Charts or visualizations help project managers in getting a feel of where the project is heading. There are various kinds of charts that a PM would use on day to day basis. Some examples are,
- Burn Down Charts: Depict how much scope of project is complete.
- Resource Loading Charts: Show how well the project resources are occupied and help PM in deciding whether to add or remove resources from the team.
- Project Milestone Charts: Convey the overall project progress against a timeline and provide a macro view of various events in the project.
- Open vs. Closed Issues: Clearly tells how well the issues in the project are getting resolved and give an indication of the trend.
- Budget vs. Actual Charts: Show how the project scope, plans, expenditure is going wrt planned budgets.
Open vs. Closed Issues – An Example Project Management Chart
Excel Chart Templates for Project Management
Microsoft Excel has several using charting features. PMs can easily create almost any type of project management related chart using MS Excel in minutes. I have written extensively about how to create various PM charts thru Excel. Just follow these links to get the Project Management Charting Templates and Tutorials.
- Burn Down Chart
- Resource Loading Chart
- Project Milestone Time Line Chart
- Open vs. Closed Issues Chart
- Budget vs. Actual Charts
- Thermo-meter Chart
- Gantt Charts
- More ExcelCharting Tutorials and Templates
MS Excel Formulas for Project Managers
Learning a few excel formulas can greatly improve the productivity of a project manager. Most of the day to day questions of PMs can be easily answered if they know how to write some simple excel formulas like VLOOKUP (ex: what is the progress of a particular activity), SUMIF (ex: How many hours team memer x clocked this week?), COUNTIF (ex: How many issues are open?), NETWORKDAYS (ex: How many working days are there in this month?) etc.
At PHD, we have extensively discussed about various excel formulas and their application to the day to day work of managers, analysts and other office users. Go thru below articles to get a thorough understanding of various important excel formulas and improve your productivity.
- VLOOKUP Excel Formula
- SUMIF Excel Formula
- COUNTIF Excel Formula
- NETWORKDAYS Excel Formula
- SMALL and LARGE Excel Formulas
- SUMPRODUCT Excel Formula
- SUMIFS Excel Formula
- SUBTOTAL Excel Formula
- More Excel Formula Tutorials, Examples and Downloads
- Recommended: 75 Excel Formulas – E-book
Excel Project Management Templates
While it is true that MS Excel is easy to use and learn, most project managers are hard pressed for time and always look for ways to boost their productivity. This is where using a set of pre-defined templates can help you. Based on my 4+ years of experience with large projects, I have prepared a set of Excel Templates that can help you become productive and efficient while managing projects. These templates can help you in,
- Project Planning thru Gantt Charts
- Project Tracking thru Time Sheets, Issue Trackers, Risk Trackers
- Project Management thru To do Lists
- Project Reporting thru Statur Reports, Charts and Dashboards
The template set contains 24 ready to use templates based on MS Excel. Consider getting a copy of the project management templates if you want to save
time and focus more on project management than excel work.
Click here to get a copy of Project Management Excel Templates
Resources & Links for Project Managers
Project management is a topic very close to my heart and I learn about it whenever possible. Here is a list of links I find useful.
- Project Management Templates from Vertex42
- Project Management Resources from Microsoft
- Project Management Topics & Learning
- Project Management Topics from Wikipedia
- Project Management Institute – for latest info on PM Practices, Certifications and Trends
- Project Planning Tool for Excel
If you want to share a link related to Project Management (and PM using Excel), please email me at chandoo.d @ gmail.com.
Join our Upcoming Excel for Project Managers course
I am happy to announce our new course, Excel for PMs. The aim of this course is to make you an awesome project manager by teaching various Excel concepts required for Project Management. We are planning to launch this course from November first week. Please enter your name and email address if you would like to know more.
[if you are not able see the signup form – click here]