Excel for HR Analytics

Introduction to HR Analytics

• HR analytics involves using human resources data to aid decision-making.
• Another definition: using data to solve people's problems.
• Familiarity with analytical tools is essential; Microsoft Excel is a primary tool for this purpose.

Statistical Measures in HR

Basic Measures

• Basic count examples:
  • Workforce headcount
  • New hires
  • Exits
  • Employee birth dates

Calculated Measures

• Utilizing base counts in calculations:
  • Employee age calculation from birth dates
  • Turnover rate calculation from exits and headcount
  • Average employee salary calculation
  • Gender ratio calculation

Attributes

• Grouping data into categories (attributes):
  • Age bands (e.g., Baby Boomers, Gen X, Millennials, Gen Z)
  • Gender classification
  • Experience level, department, or qualifications classification

Measures of Central Tendency

• Mean, median, mode: essential for understanding distribution in HR data.

Practical Application of Excel for HR Analytics

Average Calculation

• Use the AVERAGE function to find the mean salary.
  • Example: =AVERAGE(range)

TODAY Function

• Use =TODAY() to return the current date.
• Useful for calculating employee age based on birth date.
• Format dates according to your laptop's settings.

Calculating Age in Excel

1. Subtract birth date from today's date using =TODAY() - birth_date.
2. Divide the result by 365 to convert days to years.
3. Use the INT function to round down to the nearest whole number.
4. Autofill to calculate age for multiple employees.

Average Workforce Age

• Average age formula: Total ages / Number of employees.

Median and Mode Calculation

• Median calculation: =MEDIAN(range)
• Mode calculation: =MODE(range)

Summary

• Understanding these Excel functions and statistical measures will aid HR professionals in making informed decisions based on data.