How to Track Cost Variance (CV)
by Project Manager in Excel

How to Calculate Cost Variance (CV) in Excel

1. Prepare the Variance Column

• Start with the key budget and cost fields in the dataset:
  • Original Budget
  • Approved Changes
  • Final Budget
  • Actual Cost
• Create a helper column for the over or under amount.
• Use a simple subtraction setup so the table clearly shows whether each job came in above or below the final budget.
• Keep this helper column in the dataset because it makes the summary calculations easier later.

2. Create a Unique List of Job Types

• Use the UNIQUE function on the Job Type column.
• Wrap it with the SORT function to organize the list alphabetically.
• Copy the results and paste them as values using Ctrl + Shift + V.
• This creates the column headers for the summary table.

3. Create a Unique List of Project Managers

• Use the UNIQUE function on the PM Name column.
• Wrap it with the SORT function to alphabetize the list.
• Use the TRANSPOSE function only where needed so the summary layout matches the structure you want.
• Copy and paste the results as values to remove formulas.

4. Build the Summary Table by PM and Job Type

• Set up a matrix with:
  • Project Managers on one axis
  • Job Types on the other axis
• Use the SUMIFS function to total the variance amount for each PM and Job Type combination.
• Use SUMIFS again to total the Final Budget for the same PM and Job Type combination.
• Divide the summarized variance by the summarized Final Budget to return the percentage result for each cell.
• Format the completed table as percentages with Ctrl + Shift + 5.

5. Fix the Cell Referencing Before Copying Across the Table

• Adjust the references so the PM field stays aligned correctly as the formula moves down.
• Adjust the Job Type field so it stays aligned correctly as the formula moves across.
• Keep the source data columns fixed so the formula always points to the correct fields in the dataset.
• After updating the references, copy and paste the formula across the table and then down the rows.

6. Create an Overall Result by Project Manager

• Build a second summary area that shows one overall result per PM.
• Use the same SUMIFS logic as before.
• Remove the Job Type condition from the calculation so the total is rolled up at the PM level only.
• Copy the formula down for each PM.
• Format the results as percentages.

7. Apply Conditional Formatting to the Table

• Highlight the PM by Job Type matrix and the overall PM results.
• Use Conditional Formatting to highlight values less than zero.
• Set those negative values to display in red.
• This creates the heat map effect and makes it easier to spot problem areas quickly.

8. Build the Chart

• Highlight the PM names and the overall percentage results.
• Hold Ctrl if you need to select non-adjacent ranges.
• Go to Insert and choose the chart style used in the video.
• Add Data Labels so each bar shows its value directly.
• Update the chart title to match the KPI name.

9. Final Review and Cleanup

• Check that the percentages are displaying correctly.
• Confirm the copied formulas are pulling the right PM and Job Type intersections.
• Review the heat map and chart together to see which project managers are consistently performing better or worse across job types.
• Keep the layout simple so the table and chart are easy to read at a glance.