f

INTRODUCTION

Human Resource (HR) Analytics is the process of collecting,

analyzing, and interpreting employee data to support better business
decisions. Organizations use HR analytics to monitor employee
performance, salary trends, training effectiveness, workforce
distribution, and attrition risk. This project, "HR Employee Analytics
Dashboard", was developed in Microsoft Excel using a dataset of
approximately 693 employee records. The dataset includes Employee
ID, Name, Gender, Age, Department, Job Title, City, Hire Date,
Employment Type, Salary, Performance Rating, Training Hours, Leave
Days, Overtime Hours, and Attrition Risk.
The project involves data cleaning, Excel formulas, Pivot Tables,
Pivot Charts, KPI Cards, Slicers, and an interactive dashboard to
transform raw HR data into meaningful insights. The dashboard helps
HR managers analyze employee performance, salary distribution,
department-wise workforce, training hours, hiring trends, and attrition
risk.
This project demonstrates practical Excel data analysis skills and
shows how HR data can be converted into an interactive dashboard for
effective decision-making and business reporting.
 OBJECTIVES OF THE PROJECT

 The main objectives of this project are:

1. To analyze employee data using Microsoft Excel.

2. To clean and organize the employee data.

3. To calculate important KPIs using Excel formulas.

4. To create Pivot Tables for easy data analysis.

5. To create Pivot Charts for better visualization.

6. To build an interactive HR Analytics Dashboard.

7. To analyze employees by Department, Gender, City, and

Employment Type.
8. To compare employee salary and performance.

9. To identify employees with high attrition risk.

 TOOLS USED
 The following tools and features were used to complete this HR
Employee Analytics project:

 Microsoft Excel

Purpose: Usedfor data cleaning, data analysis, KPI calculations,

dashboard creation, and report preparation.
 Pivot Tables

Purpose: Used to summarize, organize, and analyze employee data

efficiently.
 Pivot Charts

Purpose: Used to create interactive charts for visualizing HR insights

and trends.
 Excel Formulas

Purpose: Usedto calculate KPIs and perform data analysis using

functions such as IF, COUNTIF, SUMIF, AVERAGEIF.
 Slicers

Purpose: Usedto filter dashboard data interactively by Department,

Gender, City, Employment Type, Performance Rating, and Attrition
Risk.
 Charts & Graphs

Purpose: Used to present employee performance, salary distribution,

attrition analysis, and other HR metrics in a visual format
  DATASET DESCRIPTION
 Descripyion

The dataset contains employee information for HR analysis. It is

used in Microsoft Excel to analyze employee performance, salary,
training, leave, overtime, and attrition through KPIs, Pivot Tables,
Pivot Charts, and an interactive dashboard.

Field Description
Employee_ID Unique employee identification number
FirstName Employee's first name
LastName Employee's last name
Gender Gender of the employee
Age Age of the employee
Department Department in which the employee works
Job Title Employee's job designation
City Employee's working city
State Employee's working state
Hire Date Employee joining date
Employment
Full-Time, Part-Time, or Contract
Type
Salary Monthly salary of the employee
Performance
Employee performance score (Out of 5)
Rating
Manager Reporting manager of the employee
Training Hours Total training hours completed
Leave Days Total leave days taken
Overtime Hours Total overtime hours worked

Attrition Risk Employee attrition risk (Low, Medium, High)

Calculated
Description
Column
 Field Description
Indicates whether an employee is Experienced or
Experienced
Junior.
Joined Before Shows if the employee joined the company before
2020 2020 (Yes/No).
Salary
Categorizes salary as Low, Medium, or High.
Classification
Indicates whether the employee is eligible for a
Bonus Eligible bonus based on performance (Eligible/Not
Eligible).

Purpose of the Dataset

 To analyze employee information efficiently.
 To monitor employee performance and productivity.
 To evaluate salary distribution across departments.
 To analyze training hours and employee development.
 To track employee leave and overtime records.
 To identify employees with high attrition risk.
 To create Key Performance Indicators (KPIs).
 To summarize data using Pivot Tables.
 To visualize HR data using Pivot Charts.
 To build an interactive HR Analytics Dashboard in Microsoft
Excel.

RAW DATA SHEET

 DESCRIPTION:

The Raw Data sheet contains the original employee dataset used
for this HR Employee Analytics project. It includes complete
information about employees such as their personal details, department,
job role, salary, performance rating, manager, training hours, leave
days, overtime hours, and attrition risk.

This data serves as the foundation for the entire project. Before
analysis, the dataset was reviewed for accuracy and consistency. It was
then used to perform data cleaning, calculate KPIs, create Pivot Tables
and Pivot Charts, and finally develop an interactive HR Analytics
Dashboard in Microsoft Excel.

The dataset provides valuable information that helps analyze employee

performance, workforce distribution, salary trends, training activities,
and employee attrition across different departments.

Figure 1: Screenshot of the raw employee dataset containing employee

details such as Employee ID, Name, Department, Gender, Salary,
Performance Rating, Training Hours, Leave Days, Employment Type,
and other HR-related information before data cleaning.
 Figure [Link] Employee Dataset

Purpose of the Raw Data

 Store complete employee information.
 Perform data cleaning and validation.

DATA CLEANING
 Description

Data cleaning is an important step in the data analysis process. It

improves the quality, accuracy, and consistency of the dataset before
performing analysis. In this project, the raw employee dataset was
reviewed and cleaned using Microsoft Excel to ensure reliable results
for KPIs, Pivot Tables, Pivot Charts, and the HR Analytics Dashboard.

The cleaned dataset contains standardized employee information with

corrected values and additional calculated columns. This ensures that
all analyses and visualizations are based on accurate and well-
organized data.

 Data Cleaning Steps Performed

 Reviewed the raw employee dataset for errors and

Figure [Link] Employee Dataset
inconsistencies.
  Checked for duplicate employee records and removed duplicates
where necessary.

 Verified that important columns did not contain missing or

incorrect values.

 Standardized text values such as Department, Gender,

Employment Type, City, and Manager names.

 Ensured that the Hire Date column was in a consistent date

format.

 Verified that numeric columns (Age, Salary, Performance Rating,

Training Hours, Leave Days, and Overtime Hours) contained
valid numerical values.

 Corrected formatting issues, including unnecessary spaces and

inconsistent text entries.
 Created additional calculated columns using Excel formulas:
o Experienced (Experienced / Junior)
o Bonus Eligible (Eligible / Not Eligible)
o Salary Classification (High / Medium / Low)
o Joined Before 2020 (Yes / No)

 Converted the cleaned data into an Excel Table to simplify

filtering, sorting, and analysis.

 Validated the cleaned dataset before creating KPIs, Pivot Tables,

Pivot Charts, and the interactive dashboard.

 Cleaning Outcome

After the data cleaning process:

o The dataset became accurate and consistent.

 o Employee records were standardized.
o Date and numeric values were correctly formatted.
o Calculated columns were added for better analysis.
o The data was ready for KPI calculations and dashboard creation.

 Benefits of Data Cleaning

o Improved data accuracy.

o Reduced errors during analysis.
o Better Pivot Table and Pivot Chart results.
o More reliable KPI calculations.
o Professional and interactive HR Analytics Dashboard.
o Faster filtering, sorting, and reporting.

 Tools Used for Data Cleaning

o Microsoft Excel
o Excel Table
o Sort & Filter
o Find & Replace
o Conditional Formatting
o Excel Formulas (IF, COUNTIF, SUMIF, AVERAGEIF)

Figure 2: creenshot of the cleaned employee dataset after data

preprocessing and calculated columns were added.

DATASET DESCRIPTION
 Overview
The HR Employee Analytics Dataset contains employee
information collected from different departments of an organization.
The dataset is designed to perform HR analytics using Microsoft Excel.
It includes employee personal details, job information, salary,
performance, attendance, training, and attrition-related data. After
cleaning and processing the data, several KPIs, Pivot Tables, Pivot
Charts, and an interactive dashboard were created to generate
meaningful business insights.

The dataset contains approximately 693 employee records and includes

information from multiple departments such as HR, IT, Finance,
Marketing, Operations, and Sales. The data also covers different cities,
employment types, performance ratings, managers, and employee
experience levels.

 Calculation column:

KPI Formula Used Purpose

Total Employees COUNTA Counts total employees.
Total Female
COUNTIF Counts female employees.
Employees
Total Male Employees COUNTIF Counts male employees.
Experienced Counts experienced
COUNTIF
Employees employees.
COUNTIF /
Total Departments Counts total departments.
UNIQUE
Calculates total IT
Total Salary (IT) SUMIF
department salary.
Average Salary Finds average Finance
AVERAGEIF
(Finance) salary.
Average Training Calculates average HR
AVERAGEIF
Hours (HR) training hours.
Total Leave Days Calculates total Marketing
SUMIF
(Marketing) leave days.
Sales Employees with Counts Sales employees
COUNTIFS
High Attrition with High attrition risk.
KPI Formula Used Purpose
Calculates average
Average Salary AVERAGE
employee salary.
Highest Salary MAX Finds the highest salary.
Lowest Salary MIN Finds the lowest salary.
Average Performance Calculates average
AVERAGE
Rating performance rating.
Calculates total training
Total Training Hours SUM
hours.
Calculates average leave
Average Leave Days AVERAGE
days.
High Attrition Risk Counts employees with
COUNTIF
Employees High attrition risk.

Purpose of the Dataset

 Analyse employee data.

 Evaluate employee performance.
 Compare salaries across departments.
 Track training and leave records.
 Monitor overtime and attrition.
 Generate HR KPIs.
 Create Pivot Tables and Pivot Charts.
 Build an interactive HR Dashboard.
 Figure [Link] Employee Analytics – KPI Dashboard

Figure 3: Screenshot of the KPI section showing important HR metrics

such as Total Employees, Average Salary, Performance Rating,
Training Hours, Leave Days, High Attrition Risk Employees, and other
key calculations generated using Microsoft Excel.

Pivot Table Analysis

 Introduction:
Pivot Tables are one of the most powerful features of Microsoft
Excel used to summarize, organize, and analyze large datasets. In this
project, Pivot Tables were created to convert the raw employee data
into meaningful summaries. These summaries help identify employee
trends, department performance, salary distribution, hiring trends,
training hours, and attrition risk.

Figure [Link]-wise Pivot Table Analysis

 Explaination:
Pivot Table 1: Department-wise Employee Count
Section : Details
Purpose : To count employees in each department.
Rows: Department
Fields Used :
Values: Count of EmployeeID
Explanation : Shows the number of employees in each department. It
helps compare workforce distribution across
Section : Details
departments.

Pivot Table 2: Department-wise Salary

Section : Details
Purpose : To calculate the total salary of each department.
Rows: Department
Fields Used : Values: Sum of Salary
Filter: City
Displays the total salary paid by each department. It
Explanation : helps HR analyze payroll expenses and budget
allocation.

Pivot Table 3: Department-wise Performance Rating

Section Details
Purpose : To analyze department performance ratings.
Rows: Department
Fields Used : Values: Sum of Performance Rating
Filter: Manager
Compares overall employee performance across
Explanation :
departments and helps identify high-performing teams.

Pivot Table 4: Gender Distribution

Section Details
Purpose : To count male and female employees.
Rows: Gender
Fields Used :
Values: Count of EmployeeID
Section Details
Shows the gender distribution in the organization and
Explanation :
supports workforce diversity analysis.

Pivot Table 5: Hiring Trend by Year

Section Details
Purpose : To analyze yearly employee hiring.
Rows: Hire Date (Grouped by Year)
Fields Used : Values: Count of EmployeeID
Filter: Department
Displays the number of employees hired each year and
Explanation :
helps identify recruitment trends.

Pivot Table 6: Attrition Risk Analysis

Section Details
Purpose : To analyze employees based on attrition risk.
Rows: Attrition Risk
Fields Used : Values: Count of EmployeeID
Filter: Department
Categorizes employees into High, Medium, and Low
Explanation :
attrition risk, helping HR improve employee retention.

Pivot Table 7: Employment Type Distribution

Section Details
Purpose : To count employees by employment type.
Rows: Employment Type
Fields Used : Values: Count of EmployeeID
Filter: Department
Section Details
Shows the distribution of Contract, Full-Time, and Part-
Explanation :
Time employees for workforce planning.

Pivot Table 8: Department-wise Training Hours

Section Details
Purpose : To calculate total training hours by department.
Rows: Department
Fields Used : Values: Sum of Training Hours
Filter: City
Displays training hours completed by each department
Explanation : and helps evaluate employee learning and
development.

 Purpos of Pivot Charts

 Easy to understand large datasets.
 Provides quick visual analysis.
 Compares HR data effectively.
 Supports interactive filtering.
 Updates automatically with Pivot Tables.
 Helps identify trends and patterns.
 Improves business decision-making.
 Creates professional and interactive dashboards.

Overall Conclusion
The Pivot Tables summarize the employee dataset into meaningful
information. They help analyze department-wise employee count,
salary, performance, gender distribution, hiring trends, attrition risk,
employment type, and training hours. These summaries form the
foundation for creating Pivot Charts, KPI reports, and the interactive
HR Analytics Dashboard, enabling better HR decision-making.

Figure 4: Screenshot of the Pivot Tables used to summarize and

analyze employee data.

Pivot Chart Analysis

Introduction:
Pivot Charts are graphical representations of Pivot Tables. They
help transform summarized data into visual charts, making it easier to
identify trends, comparisons, and patterns. In this project, Pivot Charts
were created using Microsoft Excel to present HR data in a clear and
interactive format. These charts are connected to Pivot Tables and can
be filtered using slicers for dynamic analysis.
Pivot Chart 1: Department-wise Employee Count
Section Details
Purpose To compare employees in each department.
Chart Type Bar Chart
Shows the employee count across departments for
Explanation
easy comparison.

Pivot Chart 2: Total Salary by Department

Section Details
Purpose To compare the total salary of each department.
Chart Type Column Chart
Displays department-wise salary expenses for
Explanation
payroll analysis.
Pivot Chart 3: Performance Rating by Department
Section Details
Purpose To compare department performance ratings.
Chart Type Column Chart
Explanation Shows the performance level of each department.

Pivot Chart 4: City-wise Employee Count

Section Details
Purpose To show employee distribution by city.
Chart Type Pie Chart
Displays the percentage of employees in different
Explanation
cities.

Pivot Chart 5: Year-wise Employee Joining Count

Section Details
Purpose To analyze yearly hiring trends.
Chart Type Bar Chart
Explanation Shows the number of employees hired each year.

Pivot Chart 6: Employee Count by Attrition Level

Section Details
Purpose To compare employees by attrition risk.
Chart Type Line Chart
Displays employees with High, Medium, and Low
Explanation
attrition risk.

Pivot Chart 7: Gender Distribution

Section Details
Purpose To compare male and female employees.
Chart Type Pie Chart
Explanation Shows the gender distribution of employees.

Pivot Chart 8: Department-wise Training Hours

Section Details
Purpose To compare training hours by department.
Chart Type Doughnut Chart
Displays the total training hours completed by each
Explanation
department.
  Benefits of Pivot Charts
 Easy to understand large datasets.
 Provides quick visual analysis.
 Compares HR data effectively.
 Supports interactive filtering.
 Updates automatically with Pivot Tables.
 Helps identify trends and patterns.
 Improves business decision-making.
 Creates professional and interactive dashboards.

 Conclusion:
The Pivot Charts provide a visual summary of key HR metrics such
as employee count, salary, performance rating, hiring trends, gender
distribution, attrition risk, and training hours. They make complex
employee data easier to understand and form an important part of the
interactive HR Employee Analytics Dashboard.

Figure 5: Screenshot of the Pivot Charts showing visual analysis of

employee data.
HR EMPLOYEE ANALYTICS
DASHBOARD
Dashboard Overview
 The HR Employee Analytics Dashboard was created in
Microsoft Excel to present employee data in a clear,
interactive, and visual format. It combines KPIs, Pivot Charts,
and Slicers into a single dashboard, enabling HR managers to
quickly analyze employee information and make informed
decisions.
 The dashboard provides insights into employee count, salary,
gender distribution, performance
Figure rating, training hours, hiring
[Link] Dashboard
trends, employment type, and attrition risk. Interactive slicers
allow users to filter data by Department, Gender, City,
Employment Type, Performance Rating, Attrition Risk, and Year
of Joining, making the dashboard dynamic and easy to use.

 Dashboard Components
Component Description
Display key HR metrics such as Total Employees,
Female Employees, Male Employees, Average Salary,
KPI Cards
Performance Rating, High Attrition Risk, and Training
Hours.
Visualize HR data for quick comparison and trend
Pivot Charts
analysis.
Allow users to filter the dashboard by Department,
Slicers Gender, City, Employment Type, Performance Rating,
Attrition Risk, and Hire Year.
Interactive Updates all charts and KPI cards automatically when a
 Component Description
Dashboard slicer is selected.

 KPIs Created

 Total Employees
 Total Female Employees
 Total Male Employees
 Average Salary
 Average Performance Rating
 High Attrition Risk Employees
 Average Training Hours

 Charts Created

1. Department-wise Employee Count

2. Total Salary by Department
3. Gender Distribution
4. Employment Type Distribution
5. Department-wise Training Hours
6. Year-wise Employee Joining Trend
7. Attrition Risk Analysis
8. Department-wise Performance Rating

 Slicers Used

Slicer Purpose
Department Filter employee data by department.
View data for male and female employees
Gender
separately.
Analyze employees based on different city
City
locations.
Filter Contract, Full-Time, and Part-Time
Employment Type
employees.
Slicer Purpose
Analyze employees based on performance
Performance Rating
scores.
View employees with High, Medium, or Low
Attrition Risk
attrition risk.
Year of Joining (Hire Analyze employees based on their joining
Year) year.

 Features of Slicers

 Interactive and user-friendly filtering.

 Filters all Pivot Tables and Pivot Charts simultaneously.
 Helps compare HR data across different categories.
 Enables quick analysis without changing the original dataset.
 Makes the dashboard dynamic and easy to use.
 Improves report readability and decision-making.
 Allows users to focus on specific employee groups instantly.
 Saves time by providing real-time filtered results.

Features of the Dashboard

 Interactive and user-friendly design

 Real-time filtering using slicers
 Visual representation of HR data
 Automatic updates with Pivot Tables
 Professional layout with KPI cards
 Easy comparison of departments and employee data
 Supports quick HR analysis and reporting

 Benefits of the Dashboard

 Simplifies large HR datasets into visual reports.

 Helps monitor employee performance and workforce trends.
 Enables quick comparison between departments.
 Supports HR decision-making with real-time insights.
 Saves time by automating data analysis.
 Makes reports easy to understand and present.

Figure 6: Screenshot of the interactive HR Employee Analytics

Dashboard displaying KPIs, charts, and slicers for data analysis.

 Conclusion

The HR Employee Analytics Dashboard was successfully

developed using Microsoft Excel. The project included data
cleaning, creating calculated columns, applying Excel formulas,
generating KPIs, building Pivot Tables and Pivot Charts, and
designing an interactive dashboard. These processes helped convert
raw employee data into meaningful and easy-to-understand
information.

The dashboard provides valuable insights into employee count,

salary, performance, training hours, gender distribution, employment
type, hiring trends, and attrition risk. With the help of interactive
slicers, users can quickly filter and analyze data based on different
categories. Overall, this project enhanced my Excel and data
analysis skills while demonstrating how HR data can be effectively
analyzed to support better business and workforce decisions.