Excel for HR: Employee Data and Analytics

Employee Database

Essential Fields

• Employee ID
• Name
• Department
• Position
• Start Date
• Salary
• Manager

Calculated Fields

Tenure: =DATEDIF(StartDate, TODAY(), "Y") & " years"
Age: =DATEDIF(BirthDate, TODAY(), "Y")

Attendance Tracking

Monthly Template

• Employee names in rows
• Dates in columns
• Status codes: P (Present), A (Absent), L (Leave), H (Holiday)

Formulas

Days Present: =COUNTIF(range, "P")
Attendance Rate: =Present/WorkingDays*100

Performance Reviews

• Competency areas with ratings (1-5)
• Weighted scores
• Overall rating calculation

Weighted Score Formula

=SUMPRODUCT(Ratings, Weights)/SUM(Weights)

HR Analytics KPIs

• Headcount by department
• Turnover rate
• Average tenure
• Compensation analysis
• Diversity metrics

Turnover Formula

=(Separations/Average Headcount)*100

Leave Management

Balance: =Allocation - SUMIFS(Days, Employee, Name, Year, ThisYear)

Conclusion

Excel is powerful for HR data management, analytics, and reporting.