How to Track Revenue per Employee in Excel
for Better HR and Business Performance

Revenue per Employee in Excel

1. Prepare the Payroll Data

• Ensure you have a total payroll cost column that includes:
  • Gross pay
  • Payroll taxes (FICA, unemployment)
  • Workers compensation
  • Employee benefits
• Use the SUM function to combine these into a single total payroll cost field.
• This represents your fully loaded labor cost for each employee.

2. Create a Monthly Time Dimension

• Use the EOMONTH function to convert each record into a month-end date.
• Base this on the end of the pay period, not the pay date.
• Format the result as a date so Excel displays it properly.

This step ensures all data can be grouped consistently by month.

3. Generate a Unique List of Months

• Use the UNIQUE function on the month column.
• This creates a clean list of reporting periods for your analysis.
• Format the results as dates for readability.

This will serve as the foundation for your monthly KPI table.

4. Retrieve Monthly Revenue

• Revenue values repeat across rows for each month, so you cannot sum them.
• Use the XLOOKUP function (or INDEX + MATCH) to pull the correct revenue for each month.
• Match each month in your summary table to the revenue dataset.

This ensures each month only shows one accurate revenue value.

5. Calculate the Number of Employees per Month

• Use the FILTER function to isolate employees who were paid in each month.
• Wrap it with the UNIQUE function to remove duplicates since employees may appear multiple times.
• Use the COUNTA function to count the number of employees.

This gives you an accurate employee count for each month.

6. Calculate Revenue per Employee

• Divide total monthly revenue by the number of employees.
• Drag the calculation down to populate all months.

This produces your monthly Revenue per Employee KPI.

7. Create a Target Benchmark

• Define an annual target per employee either calculated or manually entered.
• Convert it to a monthly target by spreading it across periods.
• Reference this value so it updates dynamically if the target changes.

This gives you a consistent benchmark to compare against.

8. Build the Chart

1. Select:
   • Month
   • Revenue per Employee
   • Target
2. Go to Insert and choose Recommended Charts, then Combo Chart
3. Configure:
   • Revenue per Employee as columns
   • Target as a line

This creates a clear visual comparison of performance versus target.

9. Format the Visualization

• Add a chart title such as “Revenue per Employee”
• Optionally add data labels to the columns
• Adjust label formatting if the chart becomes too crowded
• Format the target line:
  • Make it thinner
  • Use a contrasting color
  • Optionally apply a dotted style

This improves readability and makes the benchmark easy to interpret.

10. Interpret the Results

• Columns above the target line indicate strong performance
• Columns below the line indicate underperformance
• The trend over time shows whether efficiency is improving or declining

Result

You now have a structured Excel model that calculates Revenue per Employee by month, tracks employee counts accurately, and visualizes performance against a target benchmark.