Paper / Subject Code: 23120 / Computer systems & Applications Paper - I
TYBCOM EXCEL QUESTION BANK
(As per the Mumbai university syllabus sem-v)
Q1. The following data has been entered in a worksheet.
A B C D E F
1 NAME BASIC HRA DA PF NET PAY
2 ADIL 40000
3 SABA 25000
4 NAFISA 60000
5 SACHIN 75000
6 ROHAN 34000
Write the steps to obtain
i. HRA as 45% of the Basic or 18,000 whichever is less in column C
ii. DA as 130% of the Basic or 20,000 whichever is more in column D
iii. PF as 12% of Basic in column E
iv. NET PAY as Basic + DA + HRA – PF in column F
Q2. The following data has been entered in a worksheet.
A B C D E F G H
1 RNO NAME ACC ECO MHRM COMP TOTAL AVERAGE
2 152 AVINASH 88 80 92 99
3 176 AMIT 60 64 80 97
4 243 SUMIT 45 38 65 80
5 362 VISHAL 69 76 72 90
6 89 ARIF 98 90 98 100
7 127 AKSHAY 87 85 90 98
8
9 MAX
AVG
MARKS
Write the steps to obtain
i. Total Marks in column G
ii. Average of best three subjects in column H
iii. Maximum average marks in cell H9.
Q3. The following data has been entered in a worksheet.
A B C D E F G
NAME BASIC HRA DA GROSS TAX NET
1 RAM 25000
2 RAHIM 10000
3 ARJUN 18000
4 ANTHONY 12000
5 VARUN 30000
Write the steps to obtain
i. HRA as 60% of the Basic or 15,000 whichever is less in column C
ii. DA as 110% of the Basic rounded to the nearest integer in column D.
iii. GROSS as BASIC+DA+HRA in column E.
iv. TAX as 33.3% of GROSS in column F.
v. NET=GROSS-TAX in column G.
Q4. The following data has been entered in a worksheet.
A B C D E F
1 PRODUCT PRICE UNITS TOTAL DISCOUNT NET
NAME PER SOLID AMOUNT AMOUNT
UNIT
2 A 16000 10
3 B 28000 2
4 C 4000 15
5 D 850 14
6 E 1250 8
7
8 RATE OF 25%
DISCOUNT
Write steps to obtain
i. TOTAL AMOUN = UNITS SOLD X PRICE PER UNIT
ii. DISCOUNT = TOTAL AMOUNT X 25%
iii. NET AMOUNT = TOTAL AMOUNT – DISCOUNT
iv. Find sum of NET AMOUN in cell F7
Q5. For the following spreadsheet write steps to obtain the Subtotals of the fees paid class wise.
A B C D
1 ROLL NO NAME CLASS FEES PAID
2 34 AMAN T.Y.B.Com 4500
3 78 VISHAL F.Y.B.Com 3500
4 45 KUMAR S.Y.B.Com 4000
5 120 JOHN T.Y.B.Com 4500
6 153 NEHA S.Y.B.Com 4000
7 248 KARISHMA F.Y.B.Com 3500
8 891 AKBAR T.Y.B.Com 4500
Q6. For the following spreadsheet write the steps to obtain the subtotal of sales city wise.
A B C
1 NAME CITY SALSE
2 NIRAJ MUMBAI 78000
3 JAY PUNE 60000
4 ANUP MUMBAI 10000
5 RAM NAGPUR 68000
6 MOHAN NAGPUR 77000
7 ARBAZ PUNE 75000
8 SOHAIL MUMBAI 62000
Q7. For the following spreadsheet obtain the Subtotals of the Sales Year wise.
A B C D E
1 NAME YEAR AGE MOBILE NO. SALES
2 SALIM 2010 30 9821023012 45000
3 RAHUL 2011 27 9845673212 55000
4 ASHA 2011 22 8976765645 25000
5 RAJNI 2010 32 8286370744 20000
6 ANKIT 2010 26 9322587761 20000
7 SOHAIL 2011 20 8898021339 18000
Q8. For the following spreadsheet write the steps to obtain the Pivot table showing total salary
and lowest salary department wise in column G.
A B C D
1 NAME AGE DEPT SALARY
2 PETER 46 A/C 48000
3 NAVIN 28 PUR 32000
4 JAY 25 ADMIN 25000
5 KARTHIK 44 PUR 5000
6 MUSKAN 29 A/C 30000
7 PADMA 25 ADMIN 46000
8 VIVEK 23 ADMIN 50000
Q9. For the following spreadsheet obtain the Pivot table showing total profit and average profit
city wise in column F1.
A B C D
1 NAM CITY GENDER PROFIT
2 SHAAN NASIK MALE 45000
3 ADITYA MUMBAI MALE 75000
4 SARITA PUNE FEMALE 50000
5 NIKKI NASIK FEMALE 40000
6 RIDHI PUNE FEMALE 55000
7 SUMIT MUMBAI MALE 70000
