How to Track Projects Exceeding Budget % in Excel (Improve Project Cost Control)

How to Calculate Projects Exceeding Budget % in Excel

1. Set Up the Supporting Cost Variance Column

• Start with the project dataset that includes fields such as:
  • Project ID
  • Start date
  • End date
  • Market
  • Job type
  • Project manager
  • Budget
  • Projected final cost
  • Active or closed status
• Create a column that compares the projected final cost against the approved budget.
• Use a simple subtraction so each row shows whether the project is under or over budget.
• Positive values indicate the project is over budget.
• Negative values indicate the project is under budget.

2. Create a Unique List of Project Managers

• Use the UNIQUE function on the Project Manager column.
• This removes duplicates and gives you one list of PMs to analyze.
• Leave the list unsorted at first if you plan to sort it later based on performance values.
• Place this list beside your summary table so it can drive the rest of the calculations.

3. Add the Summary Columns

Create three summary columns next to the Project Manager list:

• Active Projects
• Projects Over Budget
• Percent Over Budget

This gives you the structure needed to calculate the KPI by project manager.

4. Count the Number of Active Projects for Each PM

• Use COUNTIFS to count how many projects belong to each project manager and are marked as active.
• Apply one criterion for the PM name.
• Apply another criterion for project status equal to active.
• Copy the formula down to populate the count for every project manager.

This gives you the total active project count for each person.

5. Count the Number of Active Projects That Are Over Budget

• Start with the same COUNTIFS logic used for active projects.
• Add one more criterion using the cost variance column.
• Count only rows where the variance is greater than zero.
• Keep the PM criterion and the active status criterion in place.
• Copy the formula down for the full list.

This gives you the number of active projects that are currently over budget for each project manager.

6. Calculate the Percent Over Budget

• Divide the number of projects over budget by the number of active projects.
• Format the result as a percentage.
• Copy the formula down for all PMs.

This produces the final KPI value for each project manager.

7. Build the Chart

• Highlight the Project Manager names.
• Hold Ctrl and also highlight the Percent Over Budget values.
• Go to Insert and choose a Clustered Column Chart.
• This creates a simple visual showing which PMs have the highest percentage of active projects over budget.

8. Sort the Results from Largest to Smallest

• Sort the summary table based on the Percent Over Budget values.
• Use Largest to Smallest so the highest percentages appear first.
• This makes the chart easier to read and immediately highlights the biggest budget risks.

9. Clean Up the Chart

• Update the chart title so it clearly reflects the KPI.
• Add Data Labels from the chart elements menu so the percentages display directly on the chart.
• Keep the design simple so the ranking is easy to interpret.

10. Extend the Analysis with an Additional Criterion

• To make the analysis more granular, add another dimension such as Job Type.
• Keep the same general setup.
• Add a second criterion to your counting logic.
• Recalculate the over-budget percentage at that more detailed level.

This lets you move from a PM-level view to a PM-by-job-type view using the same structure.