MS-Excel:
Microsoft Excel is a spread sheet developed by Microsoft for Windows, macOS, Android and iOS. It features
calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for
Applications.
Open This Application:
Atfirst Press (Window Key + R) Then Open Run Dialog Box Write Commend “Excel”Display Following
Format.
LESSON-1
1. Wrap Text :-
NET
NET AMOUNT
AMOUNT
2. Merge & Center :-
3. Type the following records:-
1. Compute the item wise TOTAL.
2. Compute the Discount Rate as 12.5% of item wise TOTAL.
3. Compute the NET AMT.
4. Insert a new column called SL-No before ITEM.
5. Fill the column SL-No with start value 5 and increase with step value 5.
6. “GOAL SEEK” on the above table of TOTAL as 400 by changing the Quantity of AIRTELL.
7. Find the HIGHEST sale of item BSNL in between 5 Months at A15 Cell.
8. Find the LOWEST sale of item BSNL in between 5 Months at A16 Cell.
9. Find the AVERAGE sale of item BSNL in between 5 Months at A17 Cell.
LESSON-2
1. Type this following Table Structure :-( FILTER)
NAME COURSE EXCEL WORD DTP
PUJA DOAP 87 82 72
CHAMPA DCA 86 88 90
MOHIT ADCA 87 73 80
DIPU DOAP 75 90 77
a. Display the records that who are in DOAP Course.
b. Display the records that whose marks in between 70 to 80 in DTP.
c. Display the record that who have got 87 in EXCEL but reading in ADCA.
d. Display the record that Sort Name wise A to Z
e. Display Top 3 student On Word …
2) Type this following worksheet is “sheet-1”:-(ADVANCED FILTER)
NAME DEPT DESIG SALARY
RONI DA SALES MGR 6500
AMIT PAL MKT PEON 3500
ASHA SEN ACCT MGR 4900
DIPA DAS SALES PEON 3000
ASHIF ACCT MGR 6800
a. Extract the records who is working is SALES department.
b. Extract the records whose department is ACCT but salary is more than Rs.5000.
c. Extract the records whose designation is MGR is but not working in SALES.
d. Extract the records whose SALARY in between Rs.6000 to Rs.7000.
e. Extract the records whose Surname is starting with “D”.
f. Extract the records whose First name e is starting with “A”.
g. Save this document in your folder by your name.
1. Following this calculation:-(MARKSHEET) LESSON-3
2. Following this calculation:-(VOTER LIST)
3. Create the Following Worksheet and Use CHART.
LESSON-4
1. Data validation:- (Put only Alphabet)(list) (Date)(Numeric)(10 Digit Mobile)
2. Calculatethe followingsheetand ( SUBTOTAL)
3. Do the Following (CONDITIONALFORMATING)
LESSON-5
1. Calculate Sum if & Count if Function :-
2. Set Freeze Panes :-
3. Trace Precedents & Trace Dependents :-
LESSON-6
1. Calculate Electricity Bill
Calculate: Consumed Units, Units Charge, TAX, Late Fine and Payable Fields
2. Calculate Attendance Sheet:
Calculate: Working Daye’s, Absent, Leave, Half Day, Present Fields
1. Calculate EMI Calculation:- LESSON-7
2. CalculateEMI Calculation:-
3. Due& AdvanceCalculation:-
1. CalculateFollowingMarksheet:- LESSON-8
2. Calculate GST:-
1. V-LookupCalculation:- LESSON-9
2. Calculate Commission:-
3. Date of BirthCalculation
H-LOOKUP CALCULATION LESSON-10
TABLE-
1
ROLL NO 101 102 103 104 105
MATH 78 55 92 60 70
ENGLISH 65 70 88 45 60
PHYSICS 72 60 95 58 75
CHEMISTRY 88 75 85 62 80
HISTORY 90 80 84 70 58
TOTAL ? ? ? ? ?
AVERAGE
%
GRADE ? ? ? ? ?
LOOKUP VALUE HLOOKUP
(ROLL NO) (GRADE)
103
TABLE-
2
SALES MAN ID E101 E102 E103 E104 E105
NAME AYAN RIYA SOHAM MEERA ARJUN
REGION EAST WEST NORTH SOUTH EAST
MONTH JAN JAN JAN JAN JAN
TARGET 100000 100000 120000 80000 150000
SALES AMOUNT 120000 85000 145000 60000 20000
% OF
15% 14% 16% 13% 17%
COMMISSION
COMMISSION
? ? ? ? ?
AMOUNT
BONUS ? ? ? ? ?
BASIC SALARY 25000 25000 25000 25000 25000
FINAL SALARY ? ? ? ? ?
GRADE ? ? ? ? ?
BONUS = COMMISSION AMOUNT MORE THAN RS.20000 THEN 2000
BONUS,COMMISSION AMOUNT RS. 15000 THEN 1500,COMMISSION AMOUNT
RS.10000 THEN 1000,OTHERWISE 0
GRADE = FINAL SALARY ABOVE 50000 THEN "A+",FINAL SALARY ABOVE 40000
THEN "A",FINAL SALARY 30000 THEN "B",FINAL SALARY 20000 THEN "C",FINAL
SALARY LESS THEN 10000 THEN "D"
LOOKUP VALUE HLOOKUP
1. SALARY SHEET:- LESSON-11
1. House Rent (HR) 50% of Basic Pay
2. Medical Allowance (MA) 10% of Basic Pay.
3. Provident Fund (PF) 10% of Basic Pay.
4. Income Tax (IT) 0 if Basic Pay is under 2000 Income Tax 5% if Basic Pay is
2000 to 5000 Income Tax 10% if Basic Pay is more than 5000
5. PF and Income Tax are not payable with net salary
6. Finding Net Salary.
-