Tracking Projects in MS EXCEL

homepic6There are a number of good Project Management applications. MS Project is probably the most well known application, but there are other applications that will help you plan, schedule, and control projects. The real question one faces when choosing a tool is which tool will best serve the project manager based on the organization’s IT infrastructure, Project Management culture, and the size of the project.

 

An important question that is not usually addressed is how aggressively the project manager needs to, or wants to control the project. Often a project manager will use MS Project or some other application without taking advantage of the product’s functionality. This is usually done because the project is small, aggressive control is not required by the organization, or the project manager doesn’t know how to use the application’s features. In effect, the PM uses the application like a spreadsheet to track the project. This can be a costly alternative if the organization has purchased copies of MS Project or some other tool and the projects managed by the software do not require such a robust tool.

 

In these situations, it makes sense to use MS Excel, or some other spreadsheet to manage projects. Having said that, just because a PM is using MS Excel, doesn’t mean that planning, scheduling, and controlling projects can’t support a solid project management methodology. MS Excel and other robust spreadsheets can by modified to automate many tracking and reporting tasks required by a PM methodology.

 

Converting a spreadsheet into an effective project management information system can be accomplished through:

 

  • Menu Pages
  • Macros
  • Validation Lists
  • Linked Multiple Worksheets

 

A summary Menu Page can be created to direct users to appropriate worksheets in the workbook. This navigational aid will not only enable quick navigation to the correct worksheet but also augment the professional appearance of the workbook.

 

Macros can be built to perform calculations that:

 

  • Develop budgets
  • Analyze risks
  • Facilitate Change Control
  • Manage schedule tracking
  • Manage budget performance
  • Provide real time reporting
  • Sort information for quicker analysis of project performance

 

The obvious use of validation lists is that they facilitate data entry. These lists can also improve analysis by ensuring that everyone uses the same vocabulary to describe actions and that data items are all spelled correctly for sorting analysis.

 

Linking multiple worksheets will allow the project manager to keep each worksheet to a manageable size, as well as providing opportunities for specialization of worksheets to provide the information needs of specific departments, and stakeholders. The links can ensure that all worksheets receive real time updates when a component of one worksheet is modified.

 

A person does not have to be a spreadsheet expert to create workbooks that will accomplish this effort, but one would need to be an intermediate level user who is comfortable enough with MS Excel, or whatever spreadsheet application is used, to figure out the macros, validation lists, sorting needs, etc with the use of the application help system. Of more importance is an understanding of project management methodology requirements. If there is no-one in house with the skills to produce the workbooks, templates can be purchased on-line starting at $25 to get a list of some sites you can google ‘project management templates’ or you can go to http://project-mgt-tools.com/page2.html for an example of a MS Excel Project Management template. http://techknowledgysolutions.com/wordpress/

This entry was posted in Project Management - Three Variables. Bookmark the permalink.

One Response to Tracking Projects in MS EXCEL

  1. Michael Lynn says:

    Great article – nice job. I produced a Microsoft Excel Spreadsheet Template to help manage, track and report progress of the projects on which I work. I wrote about it and offer a free download over at my blog… http://www.mlynn.org/2012/09/excel-project-planning-spreadsheet-updated-version-3/

    Enjoy!

    Regards,
    Michael

Leave a Reply