Employee Data Analysis

Description

This repository contains an extensive analysis of employee data, addressing key HR questions and deriving insights through data quality enhancements, pivot tables, charts, and more. The analysis is aimed at improving data-driven decision-making within HR departments.

Analysis Overview

1. Data Quality Enhancements:

   • Converted datasets into table format for easier manipulation.
   • Standardized date formats across all datasets.
   • Filled in missing data where appropriate (e.g., 'StillWorking' for employees without exit dates).
   • Removed irrelevant columns and standardized data formats for consistency.

2. Data Consolidation:

   • Combined all relevant datasets into a single workbook to facilitate comprehensive analysis.

3. Key Analytical Outputs:

   • Total Employees by Department: Created a pivot table to count employees in each department.
   • Conditional Formatting for Low Performance: Highlighted employees with a performance score below 3.
   • Average Satisfaction by Gender: Calculated average satisfaction scores for male and female employees.
   • Work-Life Balance Distribution: Visualized work-life balance scores across job functions.
   • Terminated Employees Analysis: Identified the most common termination types.
   • Average Engagement by Department: Calculated average engagement scores per department.
   • Supervisor's Email Lookup: Used VLOOKUP to find supervisors' email addresses.
   • Highest Average Employee Rating by Department: Determined the department with the highest average employee rating.
   • Training Duration vs. Cost: Created a scatter plot to explore the relationship between training duration and cost.
   • Employee Count by Race and Gender: Built a pivot table to show employee counts by race and gender.
   • Training Program Lookup: Used INDEX and MATCH to find training programs for specific employee IDs.
   • Performance Score Analysis: Analyzed performance scores by business unit and job function.
   • Dynamic Performance Chart: Created an interactive chart to visualize employee performance over time.
   • Training Cost Analysis: Displayed total training costs by program in a bar chart.
   • Top and Bottom Employee Ratings: Applied conditional formatting to highlight top and bottom performers.
   • Yearly Engagement Scores: Calculated average engagement scores per year using a calculated field.
   • Macro for Pivot Table Refresh: Built a macro to automate updating and refreshing all pivot tables.
   • Exit Date Distribution: Created a histogram to understand the distribution of exit dates.
   • Dynamic Training Cost by Location: Used SUMIF and dropdowns to dynamically calculate training costs by location.
   • HR Dashboard: Developed a dashboard with charts and pivot tables to provide an overview of key HR metrics.