How to Analyze Labor Cost Variance %
(Actual vs Budget) in Excel

How to Build Labor Cost Variance % (Actual vs Budget) in Excel

This tutorial walks through how to structure payroll transaction data, summarize actual labor cost by month, bring in the monthly budget, calculate the monthly variance, and visualize performance against an acceptable range in Excel.

1. Prepare the Source Data

• Start with your payroll transaction dataset.
• Use a helper column to confirm the payroll value that will feed the analysis.
• Create another helper column using the EOMONTH function so each transaction is assigned to its month-end date.
• Base the monthly analysis on period end, not pay period.
• Clean up the layout so the month-end values are easy to read and consistent across the file.

2. Create a Unique Monthly List

• Use the UNIQUE function on the month-end column to generate one row for each month.
• Make sure you are referencing the table or full data range correctly so new data can flow into the analysis.
• Format and center the month list so it becomes the structure for the summary table.

3. Bring in the Budget Data

• Go to the monthly budget tab and make sure the payroll budget is also organized by month-end date.
• Use the same EOMONTH logic there so the dates match the actual payroll table.
• Keep the month structure aligned between the transaction data and the budget data before building the lookup.

4. Calculate Actual Labor Cost by Month

• In the summary table, create the Actual column.
• Use SUMIFS to total payroll transactions for each month from the transaction dataset.
• Double-click the fill handle to copy the calculation down the full monthly list.
• This gives you the actual labor cost by month from the raw payroll data.

5. Pull the Monthly Budget into the Summary

• In the Budget column, bring in the matching payroll budget for each month.
• Use either INDEX/MATCH or XLOOKUP to connect the summary month to the budget tab.
• In the video, XLOOKUP is used to return the monthly payroll budget.
• Keep the match type exact so each month ties to the correct budget value.

6. Calculate the Dollar Variance and Variance Percentage

• Add a Variance Dollars column to compare actual payroll against budget by month.
• Then create a Variance % column to standardize the difference across all months.
• Format the dollar variance as currency and the variance rate as a percentage.
• This gives you a month-by-month view of whether labor cost came in above or below plan.

7. Set Up the Control Band for the Chart

• Add two helper columns to create the acceptable variance zone for the chart.
• Create a Lower Control Limit value for the lower edge of the range.
• Create a Band Height value for the size of the acceptable band.
• In the walkthrough, the band is set to show a range from negative 10% to positive 10%.
• These helper columns are only needed if you want the shaded band behind the variance chart.

8. Build the Variance Chart with the Acceptable Range

• Highlight the Month, Variance %, Lower Control Limit, and Band Height fields.
• Go to Insert and open Recommended Charts.
• Build the chart so the control-band helper series use Stacked Area formatting.
• This creates a shaded performance band that visually shows whether each monthly variance falls inside or outside the acceptable range.
• If you prefer, you can skip the band and use a simpler single-line chart instead.

9. Format the Band and Chart Appearance

• Change the shaded band color to a light gray or another subtle fill so the variance series stands out clearly.
• Adjust column widths and chart size so the report is easier to read.
• Rename the chart and summary fields so the output clearly communicates actual vs budget labor performance.

10. Clean Up the Month Labels

• Format the month-end dates to display more cleanly on the report and chart.
• Use Format Cells with Custom date formatting to shorten the display.
• You can show full month names, abbreviated month names, or compact month-year labels depending on how much space you have.
• This makes the chart easier to read without changing the underlying date values.

Result

You now have a monthly labor cost variance report that summarizes actual payroll, compares it to budget, calculates the variance percentage, and displays the result against an acceptable control band in Excel.