MICROSOFT EXCEL PRACTICE SHEET Step 1: ' tep 1: Open Excel File (Start > All Programs > Microsoft Office > Microsoft Office Excel) Step 2: Create the followi . “Employee Cake lowing table in the new workbook. Use Auto Fill feature to ereate the Employee Table: A B c F s | 1 | Emp Code Name of Emp Dept. | Date ofJoining | Basic Salary 2} Foor Mr. JayantaGhosh | Faculty 10/04/1982 5000 3 E002 Mr. Ajay Basu | Accountant 05/06/1989 7500 4 | 8003 | Mr. Deepak Adhikari | __ Clerk 11/12/1987 4500 5} E004 Mr. Rajib Das Manager | 02/03/1985 10000 6} E005 ‘Smt. Jayita Dutta | Supervisor | _ 06/08/2000 3000 Problems: ing”. Apply best fit method on columns “Employee Name” & “Date of Joinii 2. Edit the name of “Smt. Jayita Dutta” to “Smt. Jayita Dutta Majumder”, “HOD”, Basic “8000” to “10000”. Rename your current work sheet as Insert a blank row containing field names Calculate the following for all record: © DA-15% of Basic © TA-5%of Basic * TA-8% of Basic Gross - Basic + DA + TA + HRA © PF-12% of Gross © Tax-17% of Gross ¢ Net—Gross - (PF + Tax) 6. Find the total Gross Salary of the Company. 7. Find the average Gross Salary of the Company containing field name & 1* record. 8. Delete Sheet 2 & Sheet 3 9. Insert a new work sheet. 10. Format the heading at 15 Degree Angle and Red Color. 11. Format the work sheet as like calendar form. 12. Change the background color of the cell as you like. 13. Change the line color with single or double margin. 14, Change the heading as Merge & Centre. "Dept. “Supervisor” to “Employee Data” and the row containing the first recorded. wae y hide the row inserted between the rowrosoft Office E: . ‘ met on > Microsoft Office > Mic Step 1: Open Excel File (Start > All Programs Step 2: Create the following table in the new workbook. a avs D (aise 1 | SINo. Name of Emp tr aa Mr. Jayanta Ghosh | + a 2 ‘Mr. Ajay Basu oo 4[ 3 Mr. Deepak Adhikari 4 | Mr. Rajib Das Problem: 7 ‘i ay any 15. Calculate the Tax @5% of Basic Salary [Condition: If Basic <5000 they will not pay any Tax (“O")]. o : 16. Calculate the Bonus [Condition: If Basic >4500 then Bonus will be 3 % of Basic otherwise 2%). = (Basic Salary + Bonus)]. 17. Calculate Total Salary (Condition: 18. Calculate Net Salary [Condition: = (Total Salary - Tax)]. 19. Count the Total Number of Salary Holder [by using Count Function]. 20. Find out the Total Basic Salary 21. Find out the Average Basic Salary. 22. Keep separate record of the employee who is not got any salary [using Auto Filter Function}. Sort the name of the employee with their salary [Ascending Order]. 23. 24. Find out the person whose name start with the letter“! 25. Show the massage “Absence this month” of each employee who is not get Basic when we click his/her name. 26. Find out the employee's name & details who are not paying any Tax.MICROSOFT EXCEL PRACTICE SHEET soft Office Excel) Step 1: Open Excel File (Start > All Programs > Microsoft Office > Micro: | Step 2: Create the following table in the new workbook. A B ] c Dies fete Url 1] SINo.| Name of Emp hou) Discount | Rank | Bonvs | idan aa Shyam 250 | os ti~”Y - 2 Bidhan 200 ais Saroj i} 500 aac Jayanta “600 i ei Pradip 700 = Problem: using Auto filter). 1. Find out the name of EMP, who purchased same amount f . Find out minimum & Maximum purchased amount. . Find out Today"s Date in any cell. |. Find out current Date & Time in any cell. Calculate the amount of Discount based on the following: - Amount (Rs.) Purchase >=200 and <500 =500 and <1000 wReL Discount (%) 5% of Purchase Purchase > 10% of Purchase Purchase >=1000 15% of Purchase 6. Calculate the rank of Emp. on the basis of their purchase: - Purchase Amount (R: Purchase <250 Purchase >=250 and <500 Above 500