Task Allocation Excel Sheet from MS Project Plan

When managing projects it is always possible that you might end up managing a small team who will need to know their tasks. Microsoft Project has a feature called group which gives the option of viewing resource data in different views and also export it to an excel sheet.

Sample Task Allocation Report
Task Allocation Excel Sheet

Advantages of this Approach

  1. If you are already using Microsoft Project you will NOT need any additional tool.
  2. The approach will utilize some of the well know and widely used features.
  3. You can export the data and views easily to excel or any other spreadsheet for reports.
  4. The task allocation sheet can then be distributed to the team for their information. 
  5. Gives a clear view of resources and their tasks.
  6. Last and the best - your resource allocations will be driven by project allocations. 

# : The Approach - Start with the resource pool

Resource Pool Manager Resource MS Project
If you have multiple project then this approach will work when you have a Master Project Plan and a Resource Pool implemented. If you have a single project plan then you can do the below configurations in your resource sheet.

For the resource views to work you will need that all the projects share the same resource pool. To start with we set the appropriate group for each resource in the resource pool.

Open the resource pool and set the group as per your needs - a group is generally a team name. I have used four groups - IT, OPS, Legal and Service.

# : The Resource Usage View

Go to your master project plan and under the Views tab select "Resource Usage". You should be able to see all the resources working on all the sub-projects in the Master Project plan.  

Next select the Group By option and under the options select Resource Group. You should be able to see the view as below. 

This view gives an overall idea of the work allocation sorted by team and then resource.  You can display the available capacity by selecting what you want to display in the right side of the view.

Manage Resource MS Project
Resource Usage View 
You can also view the data at resource level or team level - which is a very useful feature. You can add additional columns like Project, Remaining Work, etc as per your reporting needs. 

You can change timeline on the right to view tasks details on a daily or monthly or yearly level. You can use the daily view if you want to generate a daily status report.

# : Export your resource allocations

You can easily move the data in the Resource Usage view to excel which you can use to customize and publish to your team.  This report will be the single document which the team can use to plan their tasks.

Follow the below steps to export to excel - 
  1. Right click on the columns for resource names and copy the columns you need and paste it into excel. 
  2. Right click on the assignments columns and copy it to adjacent to the previous copied columns.
  3. Delete whatever is not needed.
  4. Apply any formatting changes including highlight the resource names so that they are easily highlighted, remove unwanted rows and columns etc.

Sample Task Allocation Report
Task Allocation Excel Sheet

# : Some disadvantageous on this approach - 

  1. Not suitable for large number of project as MS Project becomes slow and can crash often.
  2. Manual process of generating reports is error prone.
  3. Can get quite complex to manage and requires good knowledge about the tool.
  4. Even if a minor error in the report you will need to generate the whole report again.
  5. If all the Project Plans are not updated then the report can be incorrect.

Swapnil Wale

Written by

Swapnil Wale is an IT Professional based in Sydney, Australia with over 10 years of experience in technology and project management. He is a passionate blogger and focuses on project management and BRMS articles.


© 2013 Techno-PM. All rights resevered. Designed by Templateism

Back To Top
Real Time Web Analytics