Excel for HR: Employee Data, Tracking, and Reporting

Employee Master List Structure

A solid employee database needs these columns:

• Employee ID (unique identifier)
• Full name (first, last in separate columns for sorting)
• Department and job title
• Hire date, termination date (if applicable)
• Manager (reference to another Employee ID)
• Employment status (active, leave, terminated)
• Salary/hourly rate information

Format as an Excel Table for automatic expansion and structured references.

Attendance and PTO Tracking

Key formulas for attendance:

Days worked: =NETWORKDAYS(StartDate, EndDate, Holidays)

PTO balance: =AccruedPTO - UsedPTO + CarryOver

Accrual calculation: =YEAR(TODAY())-YEAR(HireDate) for years of service

Tenure and Anniversary Tracking

Calculate employee tenure with DATEDIF:

=DATEDIF(HireDate, TODAY(), “Y”) & “ years, ” & DATEDIF(HireDate, TODAY(), “YM”) & “ months”

Find upcoming anniversaries:

=IF(DATE(YEAR(TODAY()),MONTH(HireDate),DAY(HireDate))>=TODAY(), DATE(YEAR(TODAY()),MONTH(HireDate),DAY(HireDate)), DATE(YEAR(TODAY())+1,MONTH(HireDate),DAY(HireDate)))

Performance Review Tracking

Track review cycles with:

• Last review date
• Next review due (=EDATE(LastReview, 12) for annual)
• Rating scores with data validation dropdowns
• Status column (Scheduled, Completed, Overdue)

Conditional formatting can highlight overdue reviews in red.

Headcount Reporting

Common HR metrics:

• Active headcount: =COUNTIF(Status, “Active”)
• By department: =COUNTIFS(Status, “Active”, Dept, “Sales”)
• New hires this month: =COUNTIFS(HireDate, “>=”&MonthStart, HireDate, “<=”&MonthEnd)
• Turnover rate: =(Terminations/AverageHeadcount)*100

Protecting Sensitive Data

HR data requires protection:

• Use sheet protection to prevent accidental edits
• Hide sensitive columns (salary info) from printouts
• Password-protect the workbook
• Create separate views for managers vs. HR staff

Official Resources

• DATEDIF function — tenure calculations
• NETWORKDAYS.INTL — working days by location