Track True Labor Costs: Average Compensation
by Role in Excel

How to Build Average Compensation by Role in Excel

1. Create the fully loaded payroll cost field

• Start by building the total payroll cost for each employee record.
• Combine the main compensation components into one fully loaded cost field:
  • Gross pay
  • FICA
  • Unemployment
  • Workers’ comp
  • Benefits
• This creates the total burdened cost that will later be summarized by month and by role.

2. Derive the month from the payroll period

• Use the payroll period end date to create a month field.
• Use the EOMONTH function so each payroll record rolls up to the correct month-end date.
• Make sure the month is based on the period end, not the pay date or period start.
• This gives you a consistent monthly grouping field for the report.

3. Generate a unique list of months

• Use the UNIQUE function to pull a distinct list of months from the month field you created.
• Because the data is stored in a table, the structured references make the formula easier to read.
• This list becomes the vertical axis of the report.

4. Generate a unique list of roles

• Use the UNIQUE function again to pull a distinct list of job roles.
• Then use the SORT function to place the roles in alphabetical order.
• Use the TRANSPOSE function so the role list runs across the top row instead of down a column.
• This creates the horizontal axis of the report.

5. Sum the total payroll cost by month and role

• Use the SUMIFS function to calculate total payroll cost for each role in each month.
• Set the sum range to the fully loaded payroll cost field.
• Apply criteria for:
  • The selected month
  • The selected role
• This gives you the total burdened payroll cost for that role in that month.

6. Count the number of employees in each role for each month

• To calculate an average, you cannot simply count rows because payroll records may repeat employees across pay periods.
• Instead, build the employee count carefully:
  • Use FILTER to return employee names that match the selected role and month
  • Use UNIQUE to remove duplicate names
  • Use COUNTA to count the unique employees returned
• This ensures the denominator reflects the actual number of employees in that role for that month.

7. Divide total payroll cost by unique employee count

• Take the total payroll cost result and divide it by the unique employee count.
• This gives you the average fully loaded compensation for each role by month.
• At this point, you have the core calculation for the KPI matrix.

8. Lock the references before copying across the table

• Adjust the cell references so the formula copies correctly across rows and columns.
• Lock the month reference so it stays fixed correctly as you copy across roles.
• Lock the role reference so it stays fixed correctly as you copy down months.
• Use the F4 key or add dollar signs manually to control which row or column remains fixed.

9. Copy the completed formula across the full matrix

• Once the references are locked correctly, copy the formula across the entire report grid.
• Fill it across all roles and all months.
• This creates the completed Average Compensation by Role matrix.

10. Apply a heat map to highlight higher and lower values

• Select the completed matrix.
• Go to conditional formatting and apply Color Scales.
• Use More Rules if you want to customize the colors.
• Choose a scale where darker shading represents higher compensation values.
• This makes it easier to spot the most expensive roles and identify month-to-month changes visually.

11. Adjust formatting for readability

• Resize columns or rows as needed so the table is easier to read.
• Review the color scale to make sure the numbers remain visible.
• Fine-tune the formatting so the final output is clean and easy to interpret.

12. Review the final role-by-month compensation pattern

• Once the table is complete, you can scan it to identify changes in compensation by role over time.
• The darker cells indicate higher average compensation months.
• This final layout makes it easy to compare roles side by side and monitor changes month by month.