| Employee ID | Employee Name  | Department | Salary | Hire Date  |

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

| 4           | Emily Johnson  | HR         | 55000  | 2017-11-20 |

| 5           | Michael Lee    | Sales      | 65000  | 2019-08-05 |

| 6           | Sarah Wang     | Finance    | 72000  | 2018-02-15 |

| 7           | Daniel Kim     | Sales      | 63000  | 2020-01-03 |

| 8           | Jessica Liu    | HR         | 58000  | 2016-09-10 |

| 9           | Ethan Chen     | Finance    | 68000  | 2017-06-25 |

| 10          | Sophia Brown   | Sales      | 70000  | 2019-04-12 |

Task :

Apply conditional formatting to highlight employees with a salary above $60000.
Create a drop-down list for selecting departments using data validation.
Use VLOOKUP function to retrieve the Department of Employee ID 2.
Calculate the number of years each employee has been with the company using DATEDIF function.
Create a PivotTable to summarize total salary by department.

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

1. Apply Conditional Formatting:

Select the range of salaries (D2).
Go to Home tab > Conditional Formatting > Highlight Cells Rules > Greater Than....
Enter 60000 in the dialog box and choose a formatting style (e.g., light green fill).

2. Create Drop-down List for Departments:

Select the cells where you want the drop-down list to appear (for example, E2).
Go to Data tab > Data Validation.
Choose List under Allow, and in the Source box, enter HR,Sales,Finance to create a dropdown list with these departments.

3. Use VLOOKUP Function to Retrieve Department of Employee ID 2:

In a new cell (for example, F2), enter the formula =VLOOKUP(2, A2:E4, 3, FALSE).
This formula looks up Employee ID 2 in the range A2 and retrieves the corresponding Department.

4. Calculate Years of Service Using DATEDIF Function:

In cell G2 (next to "Hire Date" column header), enter the formula =DATEDIF(E2, TODAY(), "Y").
Copy the formula down to calculate for all employees.
This calculates the number of complete years between the hire date (E2) and the current date.

5. Create a PivotTable for Total Salary by Department:

Select the range of data including headers (A1).
Go to Insert tab > Tables group > PivotTable.
In the PivotTable Fields pane, drag "Department" to Rows and "Salary" to Values.
Ensure "Salary" is summarized as Sum.

Assignment -5 | Employee Database Management and Analysis in Excel

Excepted timing : 5 minutes