This excel task management dashboard and tracker can be used to manage set of tasks. The dashboard graphs shows 3 key areas of tracking - Task Information, Task Status and Resource Load.
|Excel Task Management Dashboard|
Advantages and Features of Excel Task Management Dashboard
- Track task status based on % complete.
- Highlights overdue and complete tasks for better visualization of problem tasks.
- Task progress is reported using traffic light indicators.
- Can handle a list of 20 pre-defined holidays. Holidays are reduced from the task duration.
- Calculates % Complete Forecast based on start and finish dates.
- End date is auto calculated based on start date and duration of the tasks entered.
- Provides a high level graphical view of task information, task status and resource load.
- Task Information Graphs show the following task breakup - Total, Not Started, In-Progress, Complete and Overdue.
- Task Status shows the task breakup for the On track, Concern and Delayed.
- Resource Load shows the work load on the resources in days.
Some key points about task statues
- Task Progress is reported based on difference between Actual % Complete and Forecast % Complete.
- A task is highlighted as green in the progress column if it is on track. So, the % complete should be equal or greater than the forecast % complete.
- A task is highlighted as amber when the task is less than 5% behind schedule. So, the delay should be between 1 - 5%.
- A task is highlighted as red when the task is delayed by more than 5% complete.
- A task is highlighted as overdue when the end date has passed and the task is not complete.
- A task is highlighted as complete if the actual % complete is 100%.
How to use the Excel Task Management Dashboard
|How to use the task tracker and dashboard|
Step 1: In the data tab of the sheet define the resource names and public holidays in your region.
Step 2: Create the task by copying an existing row. It is important that you copy existing row so that the formulas get copied.
Step 3: Add the basic task details like Sl. No, Task, Resource Name/Assigned (drop down) and Project Name.
Step 4 (Key Step): Enter ONLY the start date and the duration.
End date, Forecast Complete %, Progress, Overdue and complete column will be auto populated. DON'T CHANGE THE VALUES IN THE COLUMNS WITH BLUE HEADINGS.
Once the basic steps are complete then the steps 2,3 and 4 will have to be repeated on regular basis. It is recommended that you update Actual % Complete on regular basis so that the charts will give the latest status. Please see the below image for different types of charts in the dashboard.
|Task Information, Task Status and Resource Load Charts|
Some limitations of the Excel Task Management Dashboard
- Cannot handle individual resource holidays.
- Can handle up to 10 resource names and 20 public holidays.
- With so many columns easy to override the values in the columns with formulas.
- No data validations on any columns.