How to Track Cost per Hire in Excel for
Better Hiring Decisions

How to Calculate Cost per Hire in Excel

1. Create the Month Grouping Column

• Start by identifying the fill date for each role.
• Use the EOMONTH function to convert each fill date into its corresponding month-end date.
• This groups each completed hire into a monthly cohort, making it easier to analyze hiring activity by month.
• The result is a clean month field that will be used throughout the rest of the analysis.

2. Build a Unique List of Hiring Months

• Use the UNIQUE function on the month column you just created.
• This generates one row for each month in which hiring activity occurred.
• Format the dates so they display as months instead of full date values.
• This becomes the summary table where monthly hiring metrics will be calculated.

3. Count the Number of Hires per Month

• Create a hires column in your summary table.
• Use the COUNTIFS function to count how many candidates accepted an offer in each month.
• This step uses multiple criteria, including:
  • whether the offer was accepted
  • the hiring month tied to the fill date
• The result shows how many successful hires were made in each month.

4. Calculate the Average Sourcing Cost per Hire

• Create a column for sourcing cost.
• Use the SUMIFS function to total the sourcing costs for each month.
• Then divide that monthly total by the number of hires for the same month.
• This gives you the average sourcing cost per hire, rather than just the total amount spent.

5. Repeat the Process for Each Cost Category

• Use the same SUMIFS approach for each remaining hiring cost category:
  • referral costs
  • sign-on bonus costs
  • recruiter fees
  • onboarding costs
• For each category, total the monthly cost and divide by the number of hires in that month.
• This creates an average cost per hire for every part of the recruiting process.

6. Lock the Correct References Before Copying Across

• When building the formulas across multiple cost columns, lock the necessary references so the month and hire count stay fixed where needed.
• This helps prevent errors when copying formulas across the table.
• Because the data is in a table structure, some references may still need to be adjusted manually by column.
• After updating the formulas, use the fill handle to copy them down for all months.

7. Calculate Average Cost per Hire

• Add a new column for total average cost.
• Use the SUM function to add together all average cost components:
  • sourcing
  • referral
  • sign-on
  • recruiter
  • onboarding
• This produces the full average cost per hire for each month.

8. Calculate Total Hiring Cost

• Add another column for total monthly cost.
• Multiply the average cost per hire by the number of hires in that month.
• This gives you the full recruiting spend for each monthly hiring cohort.
• It also lets you compare monthly volume against total hiring investment.

9. Create the Stacked Column Chart

• Highlight the month column and only the cost component columns.
• Do not include hires, total cost, or average cost in this chart selection.
• Go to Insert and choose a chart layout that can be changed into a stacked column chart.
• In the chart settings, change each series to Stacked Column.
• This creates a visual breakdown of how each hiring cost category contributes to total cost per hire by month.

10. Clean Up the Chart Display

• Adjust the horizontal axis depending on how you want empty months to appear.
• If you do not want months with no hiring activity to display, change the axis type from date-based to text-based.
• Format the value axis to remove unnecessary decimals.
• Update colors, bold labels, font sizes, or title styling as needed to match your reporting style.

11. Use the Same Structure to Analyze Other Hiring Views

• Once this model is built, you can reuse the same logic to analyze hiring costs in other ways.
• For example, instead of grouping by month, you can group by:
  • recruiting source
  • job type
  • hiring channel
• The same functions still apply. You are simply changing how the data is grouped and summarized.

12. Finalize the Monthly Cost per Hire View

• At the end of the process, you will have:
  • total hires by month
  • average cost per hire by cost category
  • total hiring cost by month
  • a stacked column chart showing cost composition over time
• This gives you a structured way to track how recruiting spend changes and where the biggest hiring costs are coming from.