HR Employee Analytics
A complete HR analytics project built in Excel, covering data cleaning, KPI calculations, exploratory data analysis, pivot tables, dashboards, and business insights.
Author: Younes Boumouh Role: Data Analyst
📌 Project Summary
This project analyzes HR data for ~690 employees across multiple countries and centers. The goal is to assess:
Compensation fairness
Workforce distribution
Tenure & performance trends
Workload & absence patterns
The analysis delivers practical recommendations to improve compensation equity, reduce burnout, and optimize staffing.
📍 Business Problem & Goals Problem
The company needs to confirm that:
Its compensation structure is fair
Workload is balanced
Absence patterns do not affect productivity
Staffing is aligned with operational needs
Without this analysis, the organization risks:
Losing high-performing employees
Overpaying in some roles or locations
Suffering productivity drops
Burnout due to excessive overtime
Project Goals
Compensation Analysis by gender, department, country, and center
Workforce Demographics (gender, locations, department distribution)
Performance & Workload (Job Rate, Overtime Hours)
Absence Trends: Sick Leaves & Unpaid Leaves
Actionable Recommendations for staffing, compensation, and operations
📊 Dataset Overview
Rows: 690 Columns: 15
Column Type Description EmployeeID Numeric Unique identifier First/Last Name Text Identification (not used in aggregates) Gender Text Male/Female Start Date Date Used to compute tenure Years Numeric Tenure in years Department Text Functional department Country Text Geographic location Center Text Branch/office Monthly Salary Numeric Compensation Annual Salary Numeric Derived (Monthly × 12) Job Rate Numeric Performance indicator Sick Leaves Numeric Absence indicator Unpaid Leaves Numeric Absence indicator Overtime Hours Numeric Workload indicator 🛠 Methods & Excel Techniques
- Data Import & Organization
Imported raw Excel file
Converted data to Excel Tables
Standardized column formats
Added filters, sorting, and header styles
- Data Cleaning
Removed duplicates
Standardized text (TRIM, UPPER/LOWER)
Normalized dates
Filled or flagged missing values
Created calculated fields:
Total Leaves = Sick + Unpaid
Annual Salary check
- KPI Calculations
Total employees
Median & average salary
Total overtime hours
Average sick leaves
Tenure distribution
- Exploratory Data Analysis (EDA)
PivotTables for salary, leaves, overtime, gender representation
PivotCharts for patterns & trends
Descriptive statistics
- Dashboard
Interactive slicers (Gender, Country, Department)
KPI cards
PivotCharts
Summary insights
📈 Key Findings
Gender ratio: 65% male / 35% female
Biggest workforce locations: Egypt (55%), UAE (23%), KSA (13%)
Pay Equity: Gender pay gap <1% → strong fairness
Overtime: Males record higher overtime overall
High-leave departments: Small specialized teams show elevated absenteeism
Job Rate & Tenure: Higher tenure strongly aligns with higher job rate
Salary Trends: Technical & engineering roles receive highest compensation
✔ Recommendations
Review high-overtime departments to prevent burnout
Monitor high-leave, low-headcount teams for continuity risks
Maintain pay equity and review specialized roles for market adjustment
Promote diversity hiring in underrepresented departments
Publish monthly dashboards for continuous monitoring
📂 Repository Structure
/workbook ├── 01_Data_Cleaning.xlsx ├── 02_KPIs_and_EDA.xlsx └── 03_Dashboard.xlsx
/visuals ├── Employee_per_country.png ├── JobRate_vs_YearsOfService.png ├── TotalLeaves_vs_Overtime.png └── TotalLeaves.png
/docs └── Methodology.md
/dataset ├── Employees_original.xlsx └── Employees_cleaned.xlsx
README.md 🧭 How to Explore This Project
Data Cleaning: workbook/01_Data_Cleaning.xlsx
KPIs & EDA: workbook/02_KPIs_and_EDA.xlsx
Dashboard: workbook/03_Dashboard.xlsx
Visuals: /visuals if you cannot open Excel
📬 Contact
Younes Boumouh GitHub: B0m0 Email: Younesboumouh97@gmail.com
License: MIT