MS Excel practical assignment 2025

 

Information Technology National Institute of Oriented Study

MS Excel Practical Assignment

Instructions:

1. Carefully read and complete all the questions.
2. Solve each question on a separate worksheet within the same Excel workbook. Name the workbook as "Excel_Assignment_YourName.xlsx".
3. Submit the completed assignment as a zipped file via WhatsApp to 9899821983.
4. Deadline for submission: (Enter your date).

---

Assignment Questions

1. Data Entry and Formatting:

   • Create a table with the following columns: Employee Name, Department, Monthly Salary, and Joining Date for 10 employees.
   • Format the "Monthly Salary" column in Currency format and "Joining Date" column in Date format.
   • Apply conditional formatting to highlight salaries greater than 50,000.

2. Basic Formulas and Functions:

   • Using the table from Question 1, calculate the Annual Salary (Monthly Salary × 12) in a new column.
   • Use the IF function to create a column that marks employees as "Eligible for Bonus" if their salary is above 40,000, otherwise "Not Eligible".

3. Data Sorting and Filtering:

   • Sort the data from Question 1 in ascending order by Department and then by Monthly Salary.
   • Apply a filter to display only employees from the "IT" department with salaries above 45,000.

4. Pivot Table Creation:

   • Create a Pivot Table from the data in Question 1 to summarize the total salary by Department.
   • Add a filter to the Pivot Table for Joining Year.

5. Charts and Graphs:

   • Create a bar chart to show the Monthly Salary of each employee from Question 1.
   • Add appropriate titles, labels, and legends to make the chart meaningful.

6. Advanced Formulas:

   • Use the VLOOKUP function to fetch the Monthly Salary of a specific employee based on their name (use a dropdown list for employee names).
   • Use the COUNTIF function to count the number of employees in the "Finance" department.

7. Data Validation:

   • Apply data validation to ensure that the Monthly Salary column only accepts values between 20,000 and 1,00,000.
   • Add an input message and error alert for the validation.

8. Text Functions:

   • Use the CONCATENATE (or TEXTJOIN) function to create a full description of each employee in the format:
     "Employee Name - Department - Joining Date".
   • Use the LEFT, RIGHT, and LEN functions to extract and display the first three letters of each employee's name.

9. Goal Seek:

   • Assume an employee wants to achieve an Annual Salary of 6,00,000. Use the Goal Seek tool to determine the required Monthly Salary.

10. Macros:

    • Record a macro that automates formatting for the table:
      • Bold headers, center align text, and apply borders.
      • Assign the macro to a button and test it on the table from Question 1.

---

Submission Guidelines

1. Ensure your workbook is well-organized with appropriate sheet names.
2. Zip your file and send it to 9899821983 via WhatsApp.