SubscribeIssue Trackers & Risk Management using Excel [Project Management using Excel - Part 5 of 6]
This is the fifth installment of project management using excel series.
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
Part 5: Issue Trackers & Risk Management
Project Status Reporting – Dashboard
Bonus Post: Using Burn Down Charts to Understand Project Progress
Tracking issues and risks is where most of the project management time goes. Once the project planning and organizing activities are in good shape, most of the project management activities are around risk management and issue tracking. In this installment of project management using excel, we will learn how to create a simple issue tracker template using excel and how to analyze issues using excel.
Issue Tracker Template
Excel is perfect for making an issue tracker template. Its grid structure and easy interface makes it totally easy to create and maintain an issue log. Here is a simple issue tracker template you can create in less than a minute.
The above template becomes very easy to manage with excel features like data validation, filters and tables (lists in 2003 and earlier).
More Robust Issue Log Template
While the above issue tracker template is good for most project needs, often you might need something little more robust. Of course, doing this is just a matter of adding few columns. For eg. it is common for project managers to keep track of the various types of issues and who is logging them, who is closing the issues. Here is an issue log template that is more robust.
Analyzing and Reporting Issue Status
Issues are part of everyday project management. It is important to keep track of various issues in the project and understand their progress. There are various ways to monitor the progress of issues using excel charts and pivot tables. In this tutorial, we will learn how to make the open vs. closed issues chart (see below).
- To make the chart, we will use the issue tracker data from the template shown above.
- We need to generate issue counts for the last 30 days from a chosen date like this:
- The counts can be easily generated by using the COUNTIF Excel formula [tutorial] like this:
=COUNTIF(issueOpenDates,Date) - We can easily make the counts cumulative.
- Finally select the 3 columns above and make a line chart with 2 series. Adjust the chart formatting you have a simple “open vs. closed issues in the last 30 days chart”
- The above chart can be a great way to start discussion about issue run rate.
Risk Management using Excel
We can use similar ideas to prepare a risk management plan using excel. The risk log is similar to issue log. But when it comes to risk analysis, the usual practice is to make a risk matrix to highlight key risks. This can be easily done in excel with the help of heat maps. This is your home work to figure out.
Download the Issue Tracker Templates
You can download the excel issue log template from here. Click the below link based on your excel version and the file type you prefer.
- Download Issue Tracker Template [Excel 2003 and earlier]
- Download Issue Tracker Template [Excel 2007 and above]
- Download Issue Tracker Template [Both files in a zip]
- Download 24 Project Management Templates for Excel
What next?
The ideas presented here can be extended to do more complex analysis of issues and risks in your project. However the issues tracker systems can only go so far if we don’t ask right questions. Often when the project is going through a rough patch, it might be better to keep the issue trackers simple and focus on the work.
In the next installment of project management using excel, we will combine all the five parts to build a project status reporting dashboard.
If you are new to the series, please read the first 4 parts as well.
- 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 Sheeet Templates and Resource Management using Excel
- While at it, also check out the bonus post about Burn Down Charts.
What is your experience with issue tracker systems
Share your ideas and opinions on using issue trackers. What is the best and worst you have seen? In one project, we have used a very complicated issue log (actually a defect log) that took almost 5 minutes to create an issue. The system would produce nice looking 3d bar and 3d pie charts depicting the issue distribution, ownership and status. Our morning scrums were a disaster when someone choose to present these. What about you?
Trackbacks & Pingbacks
- Pingback by Project Management: Show Milestones in a Timeline [Excel Template and Tutorial] | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on September 8, 2009 @ 12:25 pm
- Pingback by Burn Down Charts - Download burn down chart excel templates, learn how to make one using this tutorial | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on September 8, 2009 @ 12:27 pm
- Pingback by Project Status Dashboard, Project Status Report using Excel - Templates and Downloads | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on October 6, 2009 @ 10:14 am
- Pingback by Excel Timesheet Templates, Resource Management Templates - Project Management using Excel Spreadsheets | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on October 6, 2009 @ 2:07 pm
- Pingback by Excel Todo List for Project Management : Template, Tutorial and Dowload | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on October 6, 2009 @ 2:12 pm
- Pingback by schlossBlog » #314 VisualPM: Dashboards im PM on January 8, 2010 @ 3:50 pm
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Right good posts i come to know how to get solution for Issue Tracker and Risk Management using excel.
Normally i use Filter,Conditional formatting and pivot tables but i have never thought we can use it this way.
Thanks.
It seems to me that with issue tracking, most people think they need more than what they really need, when usually a simple worksheet along the lines of what you describe does the job perfectly well. Joel on Software had a good piece on using Excel to do scheduling a while back:
http://www.joelonsoftware.com/articles/fog0000000245.html
The issue I see with using Excel for issue tracking is if you work in a team. Sharing / editing the document, assigning the tasks to team members and keeping a log of changes doesn’t work great with an Excel document. This is where web-based systems start to be useful. I have been using FogBugz for my issue tracking since a while, and have been pretty happy with it.
@Mathias Great post, now I have to figure how to politely recommend my Firms IT and Software managers read about scheduling. Which they really do not do. Also I will check out FogBugz looks good at least for software development as a Project Management tool. Although doing this in excel on a decent size project I think would not be a good idea. Still better to use excel and have good information than junk information in good software.
Another way to use the data is to track issues-aging. For open issues, this is real simple way to highlight an issue that is staying open too long. You can also try calculating a moving average of days-aging to note problem issues/responsible parties. Both are simple and lend themselves to being utilized in an executive dashboard.
@Peter… welcome
Yes, filters, conditional formatting, pivot tables are tools, but we can do wonderful things when we put them together with a little bit of glue.
@Mathias: I think lot of times companies waste time and money on making things complex. As you say, for most scenarios a simple excel sheet would suffice. Have you seen the netflix’s presentation on organization culture and processes ? http://www.slideshare.net/reed2001/culture-1798664 It gives great insight in to how amazing companies can be built by keeping things simple.
Thanks for the reference to Joel’s article btw. I am a huge fan of Joel’s philosophy of software development.
I havent tried fogbugz though, but I heard lots of good things about it.
@Brian: “Still better to use excel and have good information than junk information in good software.” totally agree. It is not the system that matters, but what information and insight it is able to provide for better project management that matters.
@Tim: Good ideas. I am going to consider all these for the next installment of this series where we discuss project status dashboard.
If you have to keep track of the history of the topic (opened, passed to X, fixed,closed, reopened etc., how would you manage it in a clear way? A row for each event would look quite messy, IMHO. A bit of VBA, maybe?
Thanks!
PLEASE help me, sir. I love your project tracker and downloaded it to help me with a project at work.
However, I cannot for the life of me figure out how to remove the special comments on the sheet.
For example, they say “Team Member Who Opened the Issue” and “Team Member Who Closed the Issue”.
Note that these are not normal cell comments.
Can you help? Or anyone?
Please! Thank you so much for your time.
@Allie… you can easily remove these by selecting the cells and then going to “Data validation” dialog. There in the 2nd tab, remove the message (or change it to the one you prefer)
Thank you SO much Chandoo… that worked perfectly! I really appreciate the quick response.
Great!!!! Thank you very much! The only thing I cant figure out is how to redirect the chart to take input from the second Issue Log?
Thank you!