TITLE:
Employee and Department Data Analysis Using MS Excel.
[Employees-data] https://docs.google.com/spreadsheets/d/1ucbXDWIPg4KaGyduYWsdeUdY53LX0_lh/edit?usp=sharing&ouid=114143905310513993055&rtpof=true&sd=true
[Departments-data] https://docs.google.com/spreadsheets/d/1Lc1gzQJpFnMNlBeyIDEep-6FFnBtt2Ue/edit?usp=sharing&ouid=114143905310513993055&rtpof=true&sd=true
PROBLEM STATEMENT:
Your company wants to perform an in-depth analysis of the employees and departments within the organization to
improve HR processes and departmental efficiency. The analysis will help in understanding the distribution of employees
across departments, identifying salary patterns, and facilitating better data management. You have been provided with two
datasets: Employees and Departments. Your task is to perform various data analysis and management activities using
Excel.
FINDINGS:
- Apply conditional formatting to the entire row in the Employees dataset to highlight salaries greater than 15,000 in green. Additionally, use conditional formatting to highlight employees hired within the last 4 years from 2000 in yellow.
- In the Employees dataset, set up data validation to ensure that the Department ID column only accepts values that are present in the Departments dataset.
- Use Power Query to combine first name and last name in a single name and extract the user name part from email column of employees table.
- Use Power Pivot to create a relationship between the Employees and Departments datasets using the Department ID.
- Create a Power Pivot table to analyse the total and average salary per department.
- Convert the Employees dataset into an Excel Table and name it "Employees Table".
- Use the Employees Table to filter and display only employees from the Sales department.
- Create a button in the Excel sheet and assign the recorded macro to add a new employee.
- Use named ranges to create a formula that calculates the average salary of all employees.
- Apply conditional formatting to compare salaries within each department, highlighting the highest salary in each department.