Excel As Your Project Tracker: How to Make Real Progress Bars

Track project progress visually to keep your teams motivated and informed. Excel’s versatile formatting options make it easy to add real progress bars that show how much of a task or project is complete. In this article, we’ll show how you can use Excel as your project tracker by making a real progress bar.

Step 1: Set Up Your Data Table

Insert the Require Data:

• Create a table with columns like Project, Task list, Progress (%), and Status.
• In the Tasks column, insert checkbox:
  • Go to the Insert tab >> select Checkbox from Controls.

Calculate the Progress (%):

• To calculate the percentage completed for each project in the Progress (%) column, insert the following formula.
• Drag down the formula for other projects.
• From the Number tab >> select Percentage.

Formula:

=COUNTIF(B2:F2,TRUE)/COUNTA(B2:F2)

This formula divides the count of TRUE values by the total count of non-empty cells. This calculates the proportion (or percentage) of cells with TRUE values relative to all filled cells in the range.

Determine the Current Status of Projects:

• To calculate the status of each project in the Status column, insert the following formula.
• Drag down the formula for other projects.

Formula:

=IF(AND(G2>=0%,G2<75%),"In Progress",IF(AND(G2>=75%,G2<=99%),"Near Completion",IF(G2=100%,"Completed","Invalid")))

This formula evaluates the percentage value in cell G2 and assigns a corresponding status based on its range. It uses nested IF statements with AND conditions to check specific intervals.

• If G2 is between 0% and 74%, it returns “In Progress”.
• If G2 is between 75% and 99%, it returns “Near Completion”.
• If G2 is exactly 100%, it returns “Completed”. If none of these conditions are met, it returns “Invalid”.

Step 2: Insert Data Bars as Progress Indicators

• Select the cells in the Progress (%) column.
• Go to the Home tab >> from Conditional Formatting >> select Data Bars >> select Gradient Fill.

The cells in the Progress (%) column will display horizontal bars that grow in length based on the percentage.

Step 3: Add Dynamic Color to Represent Status

• Select the Status column.
• Go to the Home tab >> from Conditional Formatting >> select New Rule.

• Select Rule Type: Format only cells that contain.
• Format only cells with:
  • Cell Value: Specific Text >> Containing: In Progress >> Format: select Yellow fill color >> click on OK.
  • Cell Value: Specific Text >> Containing: Near Completion >> Format: select Orange fill color >> click on OK.
  • Cell Value: Specific Text >> Containing: Completed >> Format: select Green fill color >> click on OK.

This step will highlight tasks with color-coded status, making it easy to spot where each task stands.

Step 4: Insert Chart for Visualization

• Select the Progress (%) column.
• Go to the Insert tab >> from All Charts >> select Bar >> choose your required Bar char >> click OK.

• Format the chart to show the data more efficiently.
  • Go to the Chart Design tab >> from the Chart Styles >> select any Chart.

To Manually Customize Chart:

• Select Data Labels >> Center >> More Options >> Fill Color >> Gradient.
• Remove the series axis to give a clean look.
• Add chart’s Fill color >> Gradient.
• Add a Chart Title.

Output: 

You can use this project tracker with progress bars to track your project in Excel.

Step 5: Add a Dashboard Summary

Calculate Average:

• To calculate overall progress, you can use the AVERAGE or SUBTOTAL function. Insert the following formula in a proper cell.

Formula:

=SUBTOTAL(1,G2:G7)

This formula calculates the average of the values in the range G2:G7, while also allowing for flexibility in filtering.

Add Data Bars and Bar Chart:

• Select the average progress cell.
• Go to the Home tab >> from Conditional Formatting >> select Data Bars >> select Gradient Fill.
• Select the Average Progress (%) data and insert a bar chart following Step 4 to see overall project completion visually.

Add Filter:

• Select the Projects column.
• Go to the Data tab >> select Filter.

• Apply a filter to see how the progress bars update dynamically. Here selected Projects 1 & 2 the entire Excel sheet is automatically updated for this selection.

Conclusion

With this setup, you can use Excel as your project tracker. By following all the steps you can create a dynamic progress bar that will effectively serve as a project tracker, keeping you and your team on top of your projects visually and effectively. As your projects evolve, progress percentages will automatically adjust the progress bars and statuses, making this tracker a flexible, powerful tool for managing projects in Excel.