Employee turnover is the rate at which employees leave an organization over a given period of time. It is an important indicator of the health and performance of a business, as high turnover can indicate low employee satisfaction, poor management, or a lack of competitive advantage. On the other hand, low turnover can imply high employee engagement, loyalty, and productivity.
There are different ways to measure employee turnover, but one of the most common methods is to divide the number of employees who left the organization by the average number of employees during the period. This formula can be expressed as:
To calculate the employee turnover rate in Excel, we need to have the following data:
- The number of employees at the beginning of the period
- The number of employees at the end of the period
- The number of employees who left the organization during the period
We can use the AVERAGE function to calculate the average number of employees, and then use the formula above to calculate the employee turnover rate. Here are the steps to follow:
- Enter the data in a worksheet, as shown below:
| Month | Employees at Start | Employees at End | Employees Who Left |
|---|---|---|---|
| Jan | 100 | 95 | 5 |
| Feb | 95 | 90 | 5 |
| Mar | 90 | 85 | 5 |
| Apr | 85 | 80 | 5 |
| May | 80 | 75 | 5 |
| Jun | 75 | 70 | 5 |
- In cell E2, enter the formula
=AVERAGE(B2,C2)to calculate the average number of employees in January. Drag the formula down to cell E7 to fill the rest of the column. - In cell F2, enter the formula
=D2/E2*100to calculate the employee turnover rate in January. Drag the formula down to cell F7 to fill the rest of the column. - Format the cells in column F as percentages with one decimal place.
- The final result should look like this:
| Month | Employees at Start | Employees at End | Employees Who Left | Average Employees | Employee Turnover Rate |
|---|---|---|---|---|---|
| Jan | 100 | 95 | 5 | 97.5 | 5.1% |
| Feb | 95 | 90 | 5 | 92.5 | 5.4% |
| Mar | 90 | 85 | 5 | 87.5 | 5.7% |
| Apr | 85 | 80 | 5 | 82.5 | 6.1% |
| May | 80 | 75 | 5 | 77.5 | 6.5% |
| Jun | 75 | 70 | 5 | 72.5 | 6.9% |
- To calculate the average employee turnover rate for the whole period, we can use the
AVERAGEfunction again. In cell F8, enter the formula=AVERAGE(F2:F7)and format the cell as a percentage with one decimal place. The result should be 6.1%.
This is how we can calculate the employee turnover rate in Excel using the formula method. However, there are other approaches that we can use, such as:
- Using a pivot table to summarize the data and calculate the employee turnover rate for each month and the overall period.
- Using the
COUNTIFfunction to count the number of employees who left the organization based on a certain condition, such as their department, role, or tenure. - Using the
DATEDIFfunction to calculate the length of service of each employee who left the organization, and then use theAVERAGEfunction to calculate the average length of service of the employees who left.
