Track Internal Promotion Rate %:
Are You Building Leaders or Hiring Them?

How to Calculate Internal Promotion Rate % in Excel

1. Create a promotion indicator column

• Start with the hire source field that identifies whether each hire was external or internal.
• Add a new column to flag whether the hire was a promotion.
• Use the IF function to assign one value for external hires and another value for internal hires.
• This creates a simple numeric field you can count later when summarizing internal and external hires.

2. Create an end-of-month column

• Add another helper column based on the fill date.
• Use the EOMONTH function to convert each fill date into its month-end date.
• This allows you to roll all hires into monthly totals, even when the actual fill dates differ within the month.

3. Build a unique monthly list for the summary table

• In your summary area, create a monthly list using the end-of-month helper column.
• Use the UNIQUE function to generate one row for each month.
• Paste the results as values if you want to stop them from being formula-driven.
• If a month had no hires, manually insert that missing month so your chart remains continuous and accurate.

4. Count total job postings by month

• Add a column for total jobs or total filled roles.
• Use the COUNTIF function to count how many times each month appears in the end-of-month column.
• This gives you the total number of filled positions for each month in the summary table.

5. Count external hires by month

• Add a column for external hires.
• Use the COUNTIFS function so you can count records based on two conditions:
  • the selected month
  • the promotion flag value for external hires
• This gives you the monthly count of externally filled roles.

6. Calculate internal hires

• Add a column for internal hires.
• Instead of building another count from scratch, subtract external hires from total jobs.
• This gives you the number of internally filled roles for each month.

7. Calculate the internal promotion percentage

• Add a column for the monthly internal promotion rate.
• Divide internal hires by total jobs.
• Wrap the calculation with IFERROR so months with no hires return zero instead of an error.
• Format the result as a percentage.

8. Add the 12-month rolling average

• Create a new column for the rolling average.
• Start once you have enough monthly data to calculate a full 12-month period.
• Use the SUM function to total internal hires across the prior 12 months.
• Then use SUM again to total all jobs across the same 12 months.
• Divide those two rolling totals to create a smoother trend line that avoids the month-to-month volatility of raw percentages.

9. Add a target line

• Add a target column beside the rolling average.
• Enter your target percentage once at the top of the section.
• Repeat that value down the rest of the rows so it becomes a flat benchmark line across the chart.

10. Build the combo chart

• Highlight the month column along with:
  • external hires
  • internal hires
  • 12-month rolling average
  • target
• Go to Insert and create a Combo Chart.
• Set:
  • external hires as a stacked column
  • internal hires as a stacked column
  • rolling average as a line
  • target as a line
• Place the percentage-based series on the secondary axis.

11. Remove the first year from the chart view

• Because the rolling average needs historical data, the early portion of the chart can look incomplete.
• Adjust the chart series so the visual starts after the first year of setup data.
• This keeps the final chart focused on the period where the rolling average and target are fully meaningful.

12. Clean up the chart formatting

• Adjust the primary axis so the hire counts display clearly.
• Adjust the secondary axis so the percentage lines are easier to read.
• Change line thickness, colors, and spacing based on visual preference.
• Flatten or resize the chart if needed so the columns and lines are easier to interpret.

13. Review the final visual

• The stacked columns show the mix of external and internal hires each month.
• The rolling average line shows the longer-term trend in internal promotions.
• The target line shows whether the organization is above or below its benchmark over time.

14. Keep the model updated

• As new hiring data is added, update the helper columns and monthly summary.
• The chart will continue to refresh as the data grows over time.
• This makes the file useful as an ongoing tracking tool rather than a one-time report.