Step 1 – Create Project Layout
- Type your required Headline fields for your data. Here’s a screenshot that illustrates what the fields could look like.
- Enter the Project name.
- Input the task names one by one.
- Input the name of the person responsible for each task.
- Select the range of the cells where we want to put dates and select Datefrom the Format ribbon.
- Input the Starting Date of each task.
- Input the Due Date of each task.
- Input the spent days by each task.
- Calculate the days required by using the following formula in cell G2:
=E2-D2
- Press Enter.
- Drag the Fill Handle icon down to fill in the column.
- You will get the Days Required column.
- Calculate Progress by using the following formula in cell H2:
=F2/G2
- Press Enter.
- To convert the data into percentages, click on the Percentage icon in the Number section of the ribbon.
- Drag the Fill Handle icon.
- You will get the Progress column, as shown below.
- Input the progress status in the Statuscolumn.
- You will get the following output.
Step 2 – Build Tracker List for Project for Automation
- Copy some columns from the previous sheet by pressing Ctrl + C.
- Go to the Project Progres Tracker sheet and press Ctrl + V to paste them as shown below.
- In the remaining column, use the following formula in cell E2:
=1-D2
- Press Enter.
- Drag the Fill Handle icon. You will get the Remaining column, as shown below.
- To calculate the overall project completion, use the following formula in cell E16:
=AVERAGE(D2:D13)
The AVERAGE function returns the average of the twelve tasks which is almost 60 percent.
- Press Enter.
- To calculate the total days required, use the following formula in cell J5:
=SUM(C2:C13)
The SUM function returns the combined days of the Days Required column.
- Press Enter.
- To calculate the total days spent, use the following formula in cell J6:
=SUM(B2:B13)
The SUM function returns the combined days of the Days Spent column.
- Press Enter.
- To calculate the overall project progress, use the following formula in cell J7:
=J6/J5
- Press Enter.
- To create a dynamic summary like Task Status, use the following formula in cell J11:
=COUNTIF(F2:F13,"Completed")
The COUNTIF function will return the value by counting the number of cells of the range F2:F13 where thegiven condition is “Completed”. Using this approach, we will calculate how many tasks have been completed in this project.
- Press Enter.
- Use the following formula in cell J12:
=COUNTIF(F2:F13,"In Progress")
The COUNTIF function will return the value by counting the number of cells of the range F2:F13which satisfy the condition “In Progress”. This calculates how many tasks are in progress in this project.
- Press Enter.
- To calculate the percentage of completed tasks, use the following formula in cell K11:
=J11/(J11+J12)
- Press Enter.
- To calculate the percentage of tasks in progress, use the following formula in cell K12:
=J12/(J12+J11)
- Press Enter.
- We get the dynamic summary Task Status.
Step 3 – Insert Charts for Project Progress Tracker
- Select the range of data you want to chart and go to the Insert tab.
- Select the Stacked Column chart.
- As a consequence, you will get the following chart.
- To modify the chart style, select Chart Design and select your desired Styleoption from the Chart Stylesgroup.
- To create a Pie chart, select the range of data and go to the Insert tab.
- Select the 3-D Pie chart.
- You will get the following Pie chart.
- To modify the chart style, select Chart Design and select your desired Style option from the Chart Stylesgroup.
- To create a Doughnut chart, select the data range and go to the Insert tab.
- Select the Doughnut chart.
- You will get the following Doughnut chart.
- To modify the chart style, select Chart Design and select your desired Styleoption from the Chart Stylesgroup.
Step 4 – Generate Dynamic Project Progress Summary
- Create a new sheet and set the name of that sheet as Project Performance.
- Copy every chart by pressing Ctrl + C.
- Go to the ProjectPerformance sheet, and press Ctrl + V to paste the charts.
- You will get the final output like the following.
Read More: How to Track Multiple Projects in Excel
Download Template
Download this template while you are reading this article.
Track Project Progress.xlsx
Related Articles
- How to Create Project Pipeline in Excel
- How to Track Time Spent on Projects in Excel
