An end-to-end Excel HR analytics project analyzing workforce structure, compensation, performance, and employee turnover across a 3,000-record synthetic HR dataset.
The project demonstrates the complete analytics workflow from data quality validation and Power Query transformation to PivotTable analysis, Power Pivot / Data Model measures, interactive dashboarding, and business recommendations.
The analysis answers a central business question:
Where is employee turnover concentrated, and which workforce factors show the strongest relationship with attrition?
The project covers:
- Workforce structure
- Compensation
- Performance
- Job satisfaction
- Tenure
- Employee turnover
- Retention risk
- Business recommendations
The goal is not simply to produce reports, but to transform workforce data into business-focused insights that can support HR decision-making.
HR leadership needs to understand:
- Where is turnover concentrated?
- Which factors are most strongly associated with employee attrition?
- What actions should management prioritize to improve retention?
The analysis specifically evaluates turnover across:
- Departments
- Job roles
- Tenure
- Salary bands
- Performance ratings
- Job satisfaction
- Locations
- Source: Synthetic HR dataset created for portfolio and learning purposes
- Raw Records: 3,000
- Valid Records Analyzed: 2,996
- Records Flagged for Review: 4
- Departments: 7
- Locations: 5
Employee ID, Gender, Age, Education Level, Department, Job Role, Location, Hire Date, Termination Date, Employment Status, Base Salary, Annual Bonus, Overtime Hours, Performance Rating, Job Satisfaction, Tenure, and Absence Days.
- Microsoft Excel 2024
- Power Query — data cleaning and transformation
- Power Pivot / Data Model — measures and turnover calculations
- PivotTables — workforce and compensation analysis
- PivotCharts — dashboard visualizations
- Slicers — interactive dashboard filtering
- Excel Tables — structured data analysis
The project follows a structured data-cleaning workflow:
Employees (Raw)
↓
stg_Employees (Staging)
↓
Employees_Clean
↓
PivotTables / Data Model / Dashboard
Automated validation checks identify:
- Duplicate Employee IDs
- Missing values
- Invalid dates
- Future-dated records
- Other data-integrity issues
Invalid records are flagged through the data-quality process rather than silently removed.
The final analysis uses the 2,996 valid employee records.
Analytical fields are created and reused consistently across the project, including:
- Tenure Band
- Salary Band
- Performance Band
- Bonus Rate
The workbook separates general workforce analysis from turnover-specific analysis.
The Analysis sheet focuses on:
- Workforce composition
- Employee status
- Department and job-role distribution
- Tenure
- Salary
- Annual bonus
- Performance
- Job satisfaction
- Location
- Gender compensation
- Education-level compensation
- Cross-dimensional workforce analysis
The Analysis_Pivot_Tables sheet is dedicated to turnover analysis using the Excel Data Model.
It includes turnover analysis by:
- Department
- Tenure
- Salary Band
- Performance Rating
- Job Role
- Job Satisfaction
- Location
The workbook uses a Turnover Rate measure rather than external helper columns, allowing turnover calculations to respond dynamically to PivotTable filters and dashboard slicers.
The HR_Dashboard provides an executive-level view of the workforce.
- Total Employees
- Active Employees
- Terminated Employees
- Overall Turnover Rate
- Average Base Salary
- Average Bonus Rate
The dashboard highlights:
- Turnover by Department
- Average Salary by Department
- Turnover patterns across key workforce segments
- Additional supporting workforce metrics
Users can dynamically filter the dashboard by dimensions such as:
- Department
- Employment Status
- Performance Rating
- Tenure Band
- Job Role
| KPI | Value |
|---|---|
| Total Employees Analyzed | 2,996 |
| Active Employees | 2,381 |
| Terminated Employees | 615 |
| Overall Turnover Rate | 20.5% |
| Average Base Salary | 13,053 |
| Average Bonus Rate | 9.9% |
| Highest-Turnover Department | Sales (24.1%) |
| Lowest-Turnover Department | Marketing (16.9%) |
Employees with less than one year of tenure have a 58.2% turnover rate, compared with only 6.4% for employees with 5–10 years of tenure.
Turnover remains relatively close across salary bands, ranging from approximately 18.6% to 21.5%.
Turnover rates are broadly similar across performance ratings, indicating that high-performing employees may leave at rates comparable to other performance groups.
IT has the highest average salary but is not among the most stable departments, suggesting that compensation alone does not explain the observed turnover pattern.
Sales has the highest department turnover rate at 24.1%, followed by Finance at 23.3%.
The highest-turnover roles include:
- Sales Executive
- Senior Accountant
- Accountant
- Account Manager
- Data Analyst
-
Improve first-year retention through structured 30/60/90-day onboarding, mentoring, and early manager check-ins.
-
Investigate high-performer attrition through performance-segmented exit interviews and retention analysis.
-
Prioritize high-turnover departments and roles, particularly Sales, Finance, and the highest-risk job roles.
-
Avoid treating salary increases as the default retention solution because salary-band turnover differences are relatively limited.
-
Strengthen engagement monitoring by combining job satisfaction with engagement surveys, stay interviews, and manager feedback.
-
Continue pay-equity monitoring across comparable roles, departments, and employee groups.
HR_Workforce_Compensation_Analytics_Data_Final.xlsx
│
├── README
├── HR_Dashboard
├── Analysis
├── Analysis_Pivot_Tables
├── Data_Quality
├── Employees_Clean
├── stg_Employees
├── Employees
├── Departments
├── Locations
├── Employment_Status
├── Performance_Rating
└── Data_Dictionary
HR_Workforce_Compensation_Analytics_Data_Final.xlsx
Main Excel workbook containing the cleaned data,
PivotTables, Data Model, dashboard, and analysis.
HR_Workforce_Compensation_Insights.docx
Detailed findings, analysis, and business recommendations.
README.md
Project documentation.
-
Open
HR_Workforce_Compensation_Analytics_Data_Final.xlsxin Microsoft Excel 2024. -
Start with the
HR_Dashboardsheet for the executive summary. -
Use the dashboard slicers to explore different workforce segments.
-
Explore the
Analysissheet for detailed workforce and compensation analysis. -
Explore the
Analysis_Pivot_Tablessheet for Data Model-powered turnover analysis. -
Refresh the PivotTables and Data Model after modifying the underlying data.
-
Read
HR_Workforce_Compensation_Insights.docxfor the complete analytical findings and recommendations.
This project demonstrates an end-to-end Excel analytics workflow:
Raw Data
↓
Data Quality Validation
↓
Power Query Transformation
↓
Clean Analysis Dataset
↓
PivotTable Analysis
↓
Power Pivot / Data Model Measures
↓
Interactive Dashboard
↓
Business Insights
↓
Actionable Recommendations
The final solution combines technical Excel skills, analytical reasoning, dashboard development, and business storytelling to transform raw HR data into a decision-support analytics solution.
All employee data in this project is synthetic and was created for portfolio and learning purposes only.
No real employee or personally identifiable HR information is used.