Excel Dashboard on HR Data- Data Analysis Project
Project Overview
This time, I’ve worked on the HR data of a company to build a dashboard to have a look at the overall HR situation.
A portion of the raw data is given below-
I’ve included the things below in the dashboard.
· Total number of employees
· Avg monthly salary
· Number of new hires
· Gender ratio
· Travel ratio
· Department ratio
· Age ratio
· Job satisfaction based on dept.
· Performance by dept.
· Avg salary by Job roles
· Work-life balance by dept.
Analysis and Visualization Steps
Data Cleaning
Get Afnan Hillol’s stories in your inbox
Join Medium for free to get updates from this writer.
To clean data I’ve performed the following steps-
1. First, I’ve added a serial number using the sequence function to keep track of the serial, just in case needed.
2. Then, I converted it into a table.
3. Checked and fixed data format, typing errors,
4. Checked for duplicate rows and nulls. Not found.
5. Deleted Employee count, and employee number column because they are not of any use.
Then, I turned some data into groups for better analysis.
1. To calculate the number of new hires, I’ve categorized the employees in two parts in the ‘Status’ column- new hire (0 years in the organization) and old employee (1 or more years in the company) using the if function.
2. Job satisfaction points are turned into JSLevels- Unsatisfied (1), Slightly Unsatisfied (2), Neutral (3), Slightly Satisfied (4), Satisfied (5) using ifs.
3. Performance rating points are turned into PerformanceLevel- Outstanding (5), Exceeds Expectations (4), Meets Expectations (3), Poor (2), and Unacceptable (1) using vlookup.
4. Work-life balance rating points are converted into WLBLevel- Excellent (4), Good (3), Fair (2), Poor (1) using xlookup.
5. Divided employees into Generation Groups based on current age using Xlookup. Generations are- Gen Z (less than 26 y old), Millennials (27–42 y old), Gen X (43–58 y old), and Boomers II (59 and more).
Data Processing
In a sheet called Pivot Tables, I’ve created several pivot tables for everything I wanted to show in the dashboard.
Pivot tables were enough for the dashboard. So, I didn’t have to perform other types of calculations.
Data Visualization
From the pivot tables, I’ve created several pie charts and column charts and placed them in the dashboard. Also, I’ve added an ‘attrition’ slicer and connected it to all the charts and information in the dashboard.
The final dashboard is attached below.
