HR Data analysis dashboard by MS Excel
This dataset contains 1,000 rows of data that HR professionals deal with.
Below is a list of all the fields of data:
- Employee ID
- Full Name
- Job Title
- Gender
- Ethnicity
- Age
- Hire Date
- Annual Salary (USD)
- Bonus %
- Department
- Business Unit
- Country
- City
- Exit Date
Creating new columns as listed below:
- Age Group using IF formula
- Hire Year Group 5 years each starting in 1992 to 2021 using IF formula
- Bouns Group 6 categories using IFs formula
- Total Salary
(Annual Salary * Bouns) + Annual Salary
- Retired using IF formula
- Total number of employees in each country using COUNTIF formula
- The percentage of each group age using COUNTIF formula
In order to create the dashboard I created 5 pivot tables to show the following:
- The employees' retirement over the years based on hiring year
- The range of bonuses given to the employees in each business unit with the average annual salary
- The percentage of ethnicities in the company
- The number of hiring employees based on gender in year periods
- The distribution of the employees in each department based on age group
In addition, adding filters:
- Country
- City
- Department
- Year