ignn
INTERNSHIP PROJECT REPORT
Mubina Patel
PROJECT TITLE
Employee Performance Analysis Using
Excel
Submitted By: Mubina Yunus Patel
Department : BCA
College : KRP College Islampur
Internship : Disha Computer Institute Islampur
INTRODUCTION
Employee Performance Analysis is the process of evaluating employee performance using
different performance indicators such as KPI Score, Attendance, Task Completion,
Department, Role, and Performance Rating. This project has been developed in Microsoft
Excel to analyze employee performance and present meaningful insights through formulas,
Pivot Tables, Pivot Charts, and an interactive Dashboard.
The project helps identify top-performing employees, employees who need improvement,
department-wise performance, attendance trends, and task completion rates. By using Excel
features like data cleaning, formulas, Pivot Tables, Charts, and Slicers, the data is
transformed into a professional and interactive dashboard that supports better decision-
making.
This project demonstrates how Microsoft Excel can be used as a powerful data analysis and
reporting tool in Human Resource (HR) and business environments
.
OBJECTIVES OF THE PROJECT
The main objectives of this project are:
1. Analyze employee performance using Microsoft Excel.
2. Clean and organize the employee dataset for accurate analysis.
3. Calculate Key Performance Indicators (KPIs) using Excel formulas.
4. Create Pivot Tables for data summarization and analysis.
5. Design Pivot Charts for better data visualization.
6. Generate business insights from employee data.
TOOLS USED
The following tools and features were used to complete this
project:
Microsoft Excel
Power Query
Pivot Table
Pivot Charts
Excel Formulas
RAW DATA SHEET
Description :
This sheet contains the original employee dataset used for the analysis. It includes employee
ID, department, role, gender, experience, attendance percentage, task completion percentage,
KPI score, training hours, performance rating, and employee status.
ClEAN DATA SHEET
Description:
The dataset was cleaned before analysis. Duplicate records were checked, missing values
were handled, data types were corrected, and percentage and numeric columns were properly
formatted to ensure accurate analysis.
DATASET DESCRIPTION
Overview
This project uses an employee performance dataset containing information related to
employee details, attendance, KPI scores, task completion, departments, roles, and
performance ratings. The dataset was analyzed in Microsoft Excel to evaluate employee
performance and create an interactive dashboard.
The dataset contains 265 employee records and includes multiple fields that help in
measuring individual and department-wise performance.
Dataset Fields
Field Description
Employee ID Unique ID of each employee
Employee Name Name of the employee
Department Department of the employee (IT, HR, Finance, Marketing, Sales)
Role Job role of the employee
Status Active or Inactive employee
Attendance (%) Employee attendance percentage
KPI Score Employee KPI score
Task Completion (%) Percentage of completed tasks
Performance Rating Excellent, Good, Average, or Needs Improvement
KPI Avg Average KPI
Bonus Task Completion wise Bonus
Purpose of the Dataset
The dataset is used to:
Analyze employee performance.
Compare performance across departments.
Identify top and low performers.
Calculate KPIs using Excel formulas.
Create Pivot Tables, Pivot Charts, and an interactive dashboard
KPI CALCULATIONS USING EXCEL FORMULAS
KPI Purpose
Total Employees Total employee count
Active Employees Currently active employees
Average KPI Score Overall performance level
Average Attendance Attendance performance
Highest Scoring Employee Top performer identification
Lowest Scoring Employee Employee needing improvement
Description:
This sheet contains KPI calculations performed using Excel formulas such as COUNTIF,
AVERAGE, AVERAGEIF, MAX, MIN, and INDEX-MATCH. These formulas were used to
generate employee counts, attendance averages, KPI averages, and identify the highest and
lowest scoring employees.
PIVOT TABLE ANALYSIS
Description :
Pivot Tables were used to summarize large amounts of employee data and generate
meaningful insights. They helped compare departments, roles, gender distribution, employee
status, and performance categories efficiently.
PIVOT CHARTS
METHODOLOGY
The Employee Performance Analysis project was completed using a systematic approach in
Microsoft Excel. The following steps were followed to analyze the employee dataset and
create an interactive dashboard.
1. Data Collection
The employee dataset was collected in Microsoft Excel. It contains information such as
Employee ID, Employee Name, Department, Role, Attendance, KPI Score, Task Completion,
Performance Rating, and Status.
2. Data Cleaning
The dataset was cleaned by removing duplicate records, correcting data inconsistencies,
checking missing values, and ensuring proper formatting for accurate analysis.
3. KPI Calculation
Excel formulas were used to calculate important Key Performance Indicators (KPIs) such as
Average Attendance, Average KPI Score, Average Task Completion, Total Employees, Active
Employees, Inactive Employees, and Excellent Performance Count.
4. Pivot Table Creation
Multiple Pivot Tables were created to summarize employee performance by Department,
Role, Status, and Performance Rating.
5. Pivot Chart Creation
Different Pivot Charts such as Column Chart, Doughnut Chart, Pie Chart, and Bar Chart were
created to visualize employee performance effectively.
6. Dashboard Development
A professional interactive dashboard was designed using KPI Cards, Pivot Charts, Icons,
Shapes, and Slicers. The dashboard allows users to filter data dynamically and analyze
employee performance efficiently.
DASHBOARD
Dashboard Overview
The Employee Performance Dashboard was developed in Microsoft Excel using Pivot Tables,
Pivot Charts, KPI Cards, Shapes, Icons, and Slicers. The dashboard provides a clear and
interactive view of employee performance, allowing users to analyze key metrics and make
informed decisions.
Dashboard Features
Displays Total Employees, Active Employees, and Inactive Employees.
Shows Average Attendance, Average KPI Score, and Average Task Completion.
Compares employee performance across different departments.
Displays Performance Rating distribution.
Includes interactive Slicers for filtering data by Department, Role, and Status.
CONCLUSION
During this internship, I gained practical experience in Microsoft Excel, data cleaning, Pivot
Tables, Power Query, formulas, and dashboard creation. This project improved my analytical
and problem-solving skills and gave me confidence in creating professional Excel
dashboards.
The analysis helps identify top-performing employees, compare department performance, and
monitor important KPIs. The use of slicers and interactive charts makes the dashboard easy to
understand and user-friendly.
Overall, this project demonstrates how Microsoft Excel can be used as an effective tool for
data analysis, visualization, and reporting. It also enhanced practical skills in data
management, analytical thinking, and dashboard development.