Ms. Excel Practical Projects with Solution

Practice, Prepare & Train Yourself

Ms. Excel Pratical Projects with Answer

Project-1
>> Calculate Total Marks, Percentage, Fail/ Pass, Grade and Division by using excel formula(function)

Student Marksheet

Roll No. Name Hindi English Maths Science SS.T Computer Total Marks Percentage P/F Grade Division
101 Divya 75 67 60 78 76 87 443 73.83% Pass A Second
102 Kavya 78 68 87 56 79 57 425 70.83% Pass A Second
103 Mariya 87 86 78 67 75 73 466 77.67% Pass A First
104 Nancy 45 56 74 73 76 56 380 63.33% Pass B Third
105 Afsana 56 56 76 45 76 78 387 64.50% Pass B Third
Solution ( Method-1):-
Total Marks =sum(select cell range from hindi to computer)
Total Marks =total/number of subject eg. total/6
Fail/Pass =if(percentage>40,"Pass","Fail")
Grade =if(%>80,"A",if(%>60,"B",if(%>40,"C","D")))
-here percentage is donted by %
Division =if(%>60,"I",if(%>50,"II",if(%>40,"III","Fail")))
-here percentage is donted by %
Solution ( Method-2):-
Fail/Pass =if(and(hindi>40,english>40, maths>40, science>40, sst>40,computer>40),"Pass","Fail")
Project-2
>> Calculate Total Present & Total Absent by using excel formula(function)

Student Attendance Report

S.NO NAME COURSE 1 2 3 4 5 6 7 8 9 10 TOTAL ABSENT TOTAL PRESENT
1 DIVYA CCC P A P P P P A P P P 2 8
2 KAVYA O LEVEL A P A P P A P P A P 4 6
3 ANUSHKA DCA P A P A P A P A P P 4 6
4 KAJAL ADCA A P A P A P P P A P 4 6
5 NANCY PYTHAN P A P A P A P P P A 4 6
6 RICHA C++ A P A P P A P A P A 5 5
7 MAHIMA TALLY A P A P A P P P A P 4 6
8 MARIYA DCA A P P A P P A P P A 4 6
9 PAYAL ADCA P A P P A P P A P P 3 7
10 AFSANA HTML A P A P P A P P A P 4 6
Solution :-
Total Abssent =countif(select cell range from 1 to 10,"A")
Total Present =countif(select cell range from 1 to 10,"P")
Project-3
>> Calculate Fine, Bonus, PF, RA, DA, TA, Overtime Salary and New Salary by using excel formula(function)

Student Attendance Report

S.NO NAME COURSE 1 2 3 4 5 6 7 8 9 10 TOTAL ABSENT TOTAL PRESENT
1 DIVYA CCC P A P P P P A P P P 2 8
2 KAVYA O LEVEL A P A P P A P P A P 4 6
3 ANUSHKA DCA P A P A P A P A P P 4 6
4 KAJAL ADCA A P A P A P P P A P 4 6
5 NANCY PYTHAN P A P A P A P P P A 4 6
6 RICHA C++ A P A P P A P A P A 5 5
7 MAHIMA TALLY A P A P A P P P A P 4 6
8 MARIYA DCA A P P A P P A P P A 4 6
9 PAYAL ADCA P A P P A P P A P P 3 7
10 AFSANA HTML A P A P P A P P A P 4 6
Solution :-
Total Abssent =countif(select cell range from 1 to 10,"A")
Total Present =countif(select cell range from 1 to 10,"P")
Project-4
>> Calculate Fine, Bonus, PF, RA, DA, TA, Overtime Salary and New Salary on the basic of total present and total present by using excel formula(function)

Staff Salary Record

TOTAL ABSENT TOTAL PRESENT SALARY FINE BONUS PF RA DA TA OVT HOUR OVT SALARY NET SALARY
2 8 4000 266.6667 500 200 2000 500 400 7 700 7233.3333
4 6 7000 933.3333 400 420 4000 600 300 5 500 11146.667
4 6 2000 266.6667 300 40 3000 200 300 9 900 6093.3333
4 6 5000 666.6667 400 200 5000 400 300 8 800 10733.333
4 6 3000 400 600 100 1000 500 200 4 400 5000
5 5 6999 1166.5 300 200 7000 100 500 5 500 5100
4 6 4777 636.9333 200 300 6000 200 400 6 600 10840.067
4 6 7000 933.3333 600 400 3000 300 100 9 900 10466.667
3 7 3000 300 700 500 2000 400 200 7 700 6000
4 6 2000 266.6667 900 600 1000 50 600 5 500 3583.3333
Solution :-
Fine =(salary/30)*Total Absent
Bonus =randan any value
PF =5%*salary
RA =randan any value
TA =randan any value
Overtime Salary =per hour salary*overtime hour (eg. =100*overtime hour)
Net Salary =salary-fine+bonus-pf+ra+ta+overtime salary
Project-5
>> Calculate Sub Total, C.Gst, S.Gst and Grand Total by using excel formula(function)

Bill

Integrated Solution
14 C-Block
Near Taxi Stand
Rajaji Puram - 226017
India
Invoice # INV-000001
Invoice Date 05 Aug 2024
Term Due on Receipt
Due Date 05 Aug 2024
S.NO ITEMS QTY RATE AMOUNT
1 CAMERA 1 899 899
2 FITNESS 12 999 11,988
3 LAPTOP 13 799 10,387
SUB TOTAL 23,274
C.GST 2,094.66
S.GST 2,094.66
Grand Total 27,463.32
Solution :-
Sub Total =sum(select range of all amounts)
C.GST =sub total*9%
S.GST =sub total*9%
Grand Total =sub total+C.GST+S.GST
Project-6
>> Calculate Sub Total, Discount Value, Total Discount and Grand Total by using excel formula(function)

Invoice

Zylker Electronics Hub
14 B, Northern Street
Greater South Avenue
New York, New York 10001
U.S.A. 		Invoice # : INV-000001
Invoice Date : 05 Aug 2024
Terms : Due on Receipt
Due Date : 05 Aug 2024
S.No. Product Rate Qty Total Discount % Discount Value Sub Total
1 Mobile Phone 10,999 5 54,995 5% 2,749.75 52,245.25
2 Speaker 2,001 6 12,006 9% 1,080.54 10,925.46
3 Charger 500 9 4,500 8% 360.00 4,140.00
4 USB Cable 300 8 2,400 7% 168.00 2,232.00
5 Headphones 5,500 7 38,500 3% 1,155.00 37,345.00
6 TV 15,000 3 45,000 11% 4,950.00 40,050.00
7 Tablet 16,000 5 80,000 16% 12,800.00 67,200.00
All Total 237,401.00
Total Discount 23,263.29
Grand Total 214,137.71
Solution :-
Total =rate*quantity
Discount =Total*Discount%
Discount Value =Total-Discount Value
All Total =sum(selelect all sub total range)
Total Discount =sum(slelect all discount value)
Grand Total =All Total-Total Discount
Project-7
>> Calculate Sub Total, Discount Value, Total Discount and Grand Total by using excel formula(function)

Bill with Coupon

Zylker Electronics Hub
14 B, Northern Street
Greater South Avenue
New York, New York 10001
U.S.A. 		Invoice #: INV-000001
Invoice Date: 05 Aug 2024
Terms: Due on Receipt
Due Date: 05 Aug 2024

S.NO Items Qty Rate Amount
1 Camera 11 899 9,889
2 Fitness Tracker 12 999 11,988
3 Laptop 13 799 10,387
Sub Total 32,264.00
C.GST 2,903.76
S.GST 2,903.76
Coupon Code CS500
Discount -500.00
Grand Total 37,571.52
Solution :-
Amount =rate*qty
Sub Total =sum(select range of all amount)
C.GST =9%*Sub Total
S.GST =9%*Sub Total
Discount ==IF(J15="CS500","500",IF(J15="CS100","100",IF(J15="CS700","700","0")))
>> here : J15 is the address of Coupon Code
Grand Total =Sub Total+C.GST+S.GST-Discount
Project-8
>> Calculate Total Selling Amount and Total Quantity of a specific Brand or Product by using sumif

Product Sales Report (by sumif)

S.NO. PRODUCT BRAND AREA RATE QTY TOTAL
1TVCROMARAJAJIPURAM35000270000
2LAPTOPHPALAMBAGH15000575000
3MOBILEREALMEKESARBAGH330004132000
4HEADPHONEMIAMINABAD2000612000
5USBVIVOCHARBAGH1900815200
6COMPUTERLGRAJAJIPURAM20000240000
7ACHAIERALAMBAGH25000125000
8WASHING MACHINLGKESARBAGH15000345000
9REFRIGERATORSUMSUNGAMINABAD40000140000
10CHARGERCROMACHARBAGH1800916200
11PRINTERHPRAJAJIPURAM350005175000
12TVREALMEALAMBAGH150008120000
13LAPTOPMIKESARBAGH350009315000
14MOBILEVIVOAMINABAD200008160000
15HEADPHONELGCHARBAGH190059500
16USBHAIERRAJAJIPURAM2500717500
17COMPUTERLGALAMBAGH250004100000
18ACSUMSUNGKESARBAGH150009135000
19I PADREALMEAMINABAD400007280000
20REFRIGERATORMICHARBAGH180006108000
SUMIF SUMIF
PRODUCT COMPUTER BRAND LG
TOTAL QTY 6 TOTAL QTY 14
TOTAL SALE 140000 TOTAL SALES 194500
Solution :-
Total Qty of Computer =sumif(select all vertial product range,"computer",select all vertical quantity range)
Total Sale of Computer =sumif(select all vertial product range,"computer",select all vertical total range)
---
Total Qty of LG =sumif(select all vertical brand range,"lg",select all vertical quantity range)
Total Sales of LG =sumif(select all vertial product range,"lg",select all vertical total range)
Project-9
>> Calculate Total Selling Amount and Total Quantity of a specific Brand & Product by suing sumifs

Product Sales Report (by sumifs)

S.NO. PRODUCT BRAND AREA RATE QTY TOTAL
1TVCROMARAJAJIPURAM35000270000
2LAPTOPHPALAMBAGH15000575000
3MOBILEREALMEKESARBAGH330004132000
4HEADPHONEMIAMINABAD2000612000
5USBVIVOCHARBAGH1900815200
6COMPUTERLGRAJAJIPURAM20000240000
7ACHAIERALAMBAGH25000125000
8WASHING MACHINLGKESARBAGH15000345000
9REFRIGERATORSUMSUNGAMINABAD40000140000
10CHARGERCROMACHARBAGH1800916200
11PRINTERHPRAJAJIPURAM350005175000
12TVREALMEALAMBAGH150008120000
13LAPTOPMIKESARBAGH350009315000
14MOBILEVIVOAMINABAD200008160000
15HEADPHONELGCHARBAGH190059500
16USBHAIERRAJAJIPURAM2500717500
17COMPUTERLGALAMBAGH250004100000
18ACSUMSUNGKESARBAGH150009135000
19I PADREALMEAMINABAD400007280000
20REFRIGERATORMICHARBAGH180006108000
SUMIFS
PRODUCT TV
BRAND CROMA
TOTAL QTY 2
TOTAL SALE 70000
Solution :-
Total Qty of TV =sumif(select all vertial quantity range,select all vertical product range,"tv",select all vertical brand range,"broma")
Total Sale of Computer =sumif(select all vertial total range,select all vertical tv range,"tv",select all vertical brand range,"broma")
SUMIFS
PRODUCT USB
BRAND VIVO
AREA CHARBAGH
TOTAL QTY 8
TOTAL SALE 15,200
Solution :-
Total Qty of TV =sumif(select all vertial quantity range,select all vertical product range,"usb",select all vertical brand range,"vivo",select all vertical area range,"charbagh")
Total Sale =sumif(select all vertical total range,select all vertical product range,"usb",select all vertical brand range,"vivo",select all vertical area range,"charbagh")
Project-10
>>Find the student details by entering their roll number by using vlookup
Student Record

Student Record (vlookup)

ROLL NO NAME ADDRESS PHONE NO
101 MARIYA RAJAJIPURAM 123456789
102 DIVYA ALAMBAGH 987654321
103 PRABHAT KESARBAGH 9786754534
104 RACHIT AMINABAD 123987675
105 ROHIT CHARBAGH 897654532
106 MARIYAM RAJAJIPURAM 362514789
107 ADITYA ALAMBAGH 698574235
108 KAVYA KESARBAGH 3254799661
109 ANUSHKA AMINABAD 125635456
110 DISHAN CHARBAGH 214579633
111 AYAN RAJAJIPURAM 789546132
112 JANNAT ALAMBAGH 789456123
113 KAJAL KESARBAGH 12354789
114 MAHIMA AMINABAD 258963147
115 NANCY CHARBAGH 123654789
ROLL NO NAME ADDRESS PHONE NO
107 ADITYA ALAMBAGH 698574235
Solution :-
Name = vlookup(click on 107,select whole table range,2)
>> here 2 is the column number of name
Address = vlookup(click on 107,select whole table range,3)
>> here 3 is the column number of name
Phone No = vlookup(click on 107,select whole table range,4)
>> here 4 is the column number of name
Project-11
>>Find the student details by entering their roll number by using hlookup

Student Record (hlookup)

S.NO 1 2 3 4 5
ROLL NO 101 102 103 104 105
NAME MARIYA DIVYA PRABHAT RACHIT ROHIT
ADDRESS RAJAJIPURAM ALAMBAGH KESARBAGH AMINABAD CHARBAGH
PHONE NO 123456789 987654321 9786754534 123987675 897654532
ROLL NO 101
NAME MARIYA
ADDRESS RAJAJIPURAM
PHONE NO 123456789
Solution :-
Name = hlookup(click on 101,select whole table range,2,0)
>> here 2 is the row number of name
Address = hlookup(click on 101,select whole table range,3,0)
>> here 3 is the row number of name
Phone No = vlookup(click on 101,select whole table range,4,0)
>> here 4 is the row number of name
Project-11
>>Find the year, month and day of candidate from their dob to till date.

DOB Calculation

S.NO NAME D.O.B YEARS MONTH DAY
1 MARIYA 23-08-07 19 0 28
2 DIVYA 29-11-07 18 9 22
3 DISHAN 04-04-04 22 5 16
4 BEBO 09-12-11 14 9 11
5 AYAN 09-12-11 14 9 11
Solution :-
Years = datedif(dob,today(),"y")
Month = datedif(dob,today(),"ym")
Day = datedif(dob,today(),"md")
Project-12
>>Find the year, month and day of candidate from their dob to till date.

Driving License Status Record

NAME ISSUE DATE EXPIRY DATE STATUS AFTER YEAR
Aman 23-12-02 24-12-17 EXPIRY 15
Rahul 12-05-06 15-05-20 EXPIRY 14
Tara 19-09-03 25-09-18 EXPIRY 15
Mariya 12-12-24 16-12-39 ACTIVE 15
Divya 17-08-12 20-08-27 ACTIVE 15
Bebo 12-06-14 18-06-29 ACTIVE 15
Solution :-
Years = datedif(dob,today(),"y")
Month = datedif(dob,today(),"ym")
Day = datedif(dob,today(),"md")
Visit Other Topics Pratical Question
Facebook Instagram Youtube Whatsapp Envelope
Facebook Instagram Youtube Whatsapp