Assignment -38 | Employee Data Analysis and Reporting

Excepted timing : 10 minutes

Sample Data

| Employee ID | Department | Salary | Hire Date  |

|-------------|------------|--------|------------|

| 1           | HR         | 50000  | 2020-01-01 |

| 2           | Sales      | 60000  | 2020-02-05 |

| 3           | Finance    | 55000  | 2020-03-08 |

 


Task :
  1. Calculate the number of years of service for each employee using DATEDIF function.
  2. Use SUMIFS function to calculate the total salary for employees hired in 2020.
  3. Create a PivotTable to summarize average salary by Department.
  4. Insert a PivotChart to visualize salary distribution across departments using a column chart.
  5. Use data validation to create a dropdown list for selecting different departments to view details.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

  1. Calculate Years of Service for Each Employee:
    • Use the DATEDIF function to calculate the number of years of service based on the Hire Date. For example, if the Hire Date is in cell D2:
      • =DATEDIF(D2, TODAY(), "y") This formula calculates the number of complete years between the Hire Date and the current date.
  2. Use SUMIFS Function to Calculate Total Salary for Employees Hired in 2020:
    • Assuming Hire Date is in column D and Salary is in column C, use:
      • =SUMIFS(C2:C4, D2:D4, ">="&DATE(2020,1,1), D2:D4, "<="&DATE(2020,12,31)) This formula sums up salaries for employees hired in the year 2020.
  3. Create a PivotTable to Summarize Average Salary by Department:
    • Select the data (Employee ID, Department, Salary) and go to Insert > PivotTable.
    • Drag Department to Rows and Salary to Values (summarized as Average).
  4. Insert a PivotChart to Visualize Salary Distribution Across Departments:
    • With the PivotTable selected, go to Insert > PivotChart.
    • Choose a Column chart type to visualize the average salary by department.
  5. Use Data Validation to Create a Dropdown List for Selecting Departments:
    • Go to the cell where you want the dropdown list.
    • Go to Data > Data Validation > Data Validation.
    • Choose "List" under Allow, and in the Source field, enter the departments (e.g., HR, Sales, Finance).