Case Study 01

Advanced Excel Formulas to analyze Employee Data

Problem

We have a database that has many missing data and the client asked to clean the data and made advanced Excel formulas that rely on the match and index function, so when you enter the Employee ID you will get any data you want for the selected client.

Solution

We start by applying filters and using delete duplicates in Excel to get rid of duplicate data. After that, we make an advanced formula that will go through all cells in the table of data and will get the info required depending on the Employee ID. We make a pivot table to make a simple analysis of the employee’s data like how many employees the company hires from a specific department. Also, we look for the relation between the date of hire and the pay rate and how it is changed. Also, we find the hire trend period which is the highest hiring period for this company.