Day 27: HR & People Analytics in Excel

Attrition Rate = (Employees who left / Average headcount) × 100. Average headcount = (Opening headcount + Closing headcount) / 2. Example: =B2/AVERAGE(C2,D2)*100 where B2 = leavers, C2 = opening count, D2 = closing count. Monthly attrition is annualised by multiplying by 12: =B2/AVERAGE(C2,D2)*100*12. Calculate department-wise using SUMIF for leavers and AVERAGEIF for headcount.

Years: =DATEDIF(JoiningDate, TODAY(), "Y"). Complete months beyond years: =DATEDIF(JoiningDate, TODAY(), "YM"). Combined text: =DATEDIF(A2,TODAY(),"Y")&" years "&DATEDIF(A2,TODAY(),"YM")&" months". For employees who have left, replace TODAY() with their leaving date. Group tenure into bands using IF or IFS: =IFS(years<1,"<1 Year",years<3,"1-3 Years",years<5,"3-5 Years",TRUE,">5 Years").

Using AVERAGEIF: =AVERAGEIF(DepartmentColumn, "Sales", SalaryColumn). For all departments at once: use a Pivot Table with Department in Rows and Salary in Values (set to Average). For percentile-based salary analysis by department: use AVERAGEIFS with PERCENTILE in a more complex array formula, or analyse each department separately in Power Query using Group By.

Step 1: calculate each employee's age: =DATEDIF(DOB, TODAY(), "Y"). Step 2: create an age band column using IFS: =IFS(age<25,"<25",age<30,"25-29",age<35,"30-34",age<40,"35-39",age<50,"40-49",TRUE,"50+"). Step 3: use COUNTIF or a Pivot Table to count employees in each band. Step 4: create a column chart sorted by age band. This shows the age distribution of the workforce — important for succession planning and retirement risk analysis.

Structure the leave data as a flat table: EmployeeID, EmployeeName, Department, LeaveType (Casual/Sick/Earned), StartDate, EndDate, Days. Calculate days with NETWORKDAYS(StartDate, EndDate) for working days only. Analysis with Pivot Table: rows = Department, columns = LeaveType, values = Sum of Days. Key metrics: average leave days per employee (=Total Days / Headcount), leave pattern by month (group StartDate by month in Pivot Table), employees with more than N sick leave days (COUNTIFS or filter on the flat table).