Microsoft Excel PDF

Microsoft Excel is a Spreadsheet Program, developed by Microsoft Corporation. It contains many features, which
help you to maintain data, create reports, help to analyze data, calculation, etc.

Formula of Addition

12 15 18 45

Formula =sum(1st cell:3rd cell)

Formula of Subtraction
15 12 3

Formula =(1st cell – 2nd cell)  Enter.

 Formula of Divided

60

30

Formula  =(1st cell / 2nd cell)  Enter

Formula of Multiplication

20

60

1200

Formula  =(1st cell * 2nd cell)  Enter

 Formula of Average

60

80

90

76.67

Formula  =Average(First cell: Last cell)  Enter

Formula of Minimum

60

80

90

60

Formula  =Min(first cell: last cell)  Enter

 Formula of Maximum

60

80

90

90

Formula  =Max( first cell: last cell)  Enter

Count the Number more then 60

52

63

85

Formula  =countif(first cell: last cell,”>60”)  Enter

 Add Above 50 Numbers

20

80

60

30

140

Formula  =sumif(first cell: last cell,”>50”)  Enter

Formula of Mode

Formula: =Mod(first cell, last cell)  Enter

 Age Calculate

Name Date of birth Today Year Month Day

Rahul 23-09-02 07-08-22 19 10 15

Priya 22-08-03 07-08-22 18 11 16

Today=Today()

Year=DATEDIF(B2,C2,"Y")

Month=DATEDIF(B2,C2,"YM")

Day=DATEDIF(B2,C2,"MD”)

Multiple Marks Sheet

Phy.
Name Roll Ben English Math L.Science Science History Geography Total Percent Result Grade
gali
RajSen
15 55 66 45 67 88 52 65 438 62.57 Pass A
RaniRoy
20 66 85 45 65 82 74 67 484 69.14 Pass A
TaniGuha
6 85 46 75 38 84 67 45 440 62.86 Pass A
 Total =SUM(C2:I2)

Percent=J2/700*100

Result=IF(OR(C2<25,D2<25,E2<25,F2<25,G2<25,H2<25,I2<25),"Fail","Pass")

Grade=IF(OR(C2<25,D2<25,E2<25,F2<25,G2<25,H2<25,I2<25),"d",IF(K2>90,"AA",IF(K2>80,"A+",IF(K2>60,"A",
IF(K2>45,"B",IF(K2>35,"B",IF(K2>25,"C","D")))))))

Budget Sheet

Name of Item Quantity Rate GST% Amount GST

Mouse 150 250 1 37500 375
Keyboard 150 350 1 52500 525
Monitor 50 3800 12.5 190000 23750
UPS 45 2050 4 92250 3690
Processor 60 9000 12.5 540000 67500
Mother Board 35 4500 10.1 157500 15907.5
SMPS 80 1500 4 120000 4800
RAM DDR3 110 1200 10.1 132000 13332
RAM SDR2 200 500 10.1 100000 10100
Total 1421750 139979.5

Grand Total 1561729.5

Amount =B2*C2

GST=E2/100*D2

Total=SUM(E2:E10)

Grand Total=SUM(E11:F11)
 Salary Sheet

Net
Name Of the Post Basic Salary D.A T.A H.R.A G.P P.F G.P.F L.I.C
Salary
Employee
Samir Bhujel B.M 10000 350 450 550 11350 340.5 283.75 227 10498.75
Pinki Paswan P.A 7000 245 315 385 7945 238.35 198.625 158.9 7349.125
Rajesh Khan Worker 6000 210 270 330 6810 204.3 170.25 136.2 6299.25
Rajen K.r
Kundal Clark 5000 175 225 275 5675 170.25 141.875 113.5 5249.375

D.A=C2/100*3.5

T.A=C2/100*4.5

H.R.A=C2/100*5.5

G.P=SUM(C2:F2)

P.F=G2/100*3

G.P.F=G2/100*2.5

L.I.C=G2/100*2

Net Salary=G2-H2-I2-J2
 Daily Income- Expense Chart

SL.NO Date Income Expense

1 10-03-16 2000 100
2 11-03-16 3000 300

3 12-03-16 1000 400

4 13-03-16 200 9000 12000

5 14-03-16 5000 240 10000

6 15-03-16 6067 285
8000
7 16-03- 8067 641
16
6000
8 17-03-16 10067 500
Income
9 18-03-16 7000 300 4000
Expense
10 19-03-16 5000 700
2000

Process: select tableinsertcolumnselect first chart

 Goal Seek Sheet

Name AC Net
Name of Bank Ac No Deposit Interest Amount Withdraw
Holder Amount

Rahul Roy CBI 74586985874 24000 180 24180 10000 14180

Priya Sen SBI 45896587458 20000 150 20150 7000 13150

Raju Singh HDFC 95864582654 30000 225 30225 13000 17225

Interest =D2/100*0.75

Amount =D2+E2

Net Amount =F2-G2

Logical Function

Name Roll Total Grade

Rahul 1 600 star
Rohan 2 480 1st
Piti 3 360 2nd
Rubi 4 200 fail
Rajiv 5 300 pass
Suman 6 500 1st
Priya 7 400 2nd
 Grade=IF(C2>=600,"star",IF(C2>=480,"1st",IF(C2>=360,"2nd",IF(C2>=272,"pass","fail"))))

Attendance Sheet
N 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 3 3 Tot Ho Lea
a R 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 al Pres Abs li ve
m o s n r t t t t t t t t t t t t t t t t t s n r t t t t t t t s Da ent ent Da Da
e ll t d d h h h h h h h h h h h h h h h h h t d d h h h h h h h t y Day Day y y

Rah
ul 1 p p p p h p p l p p a h p l p p a p p a h p L p p a p p l p p 31 20 4 3 4

Priti
2 p p p a h p l p a p p h p p p p p l p p h p P p p p l p p p p 31 23 2 3 3

Raj 5 p p p p h p a p l p l h p a p p p p p l h p A p p p p p a p p 31 20 4 3 3

Ra
m 6 p p p l h p p a p p p h p p l p a p p p h p P l p a p p p l p 31 21 3 3 4

Total Day=COUNTA (C2:AG2)

Present Day=COUNTIF(C2:AG2,"p")

Absent Day=COUNTIF(C2:AG2,"a")

Holi Day=COUNTIF(C2:AG2,"h")

Leave Day=COUNTIF(C2:AG2,"l")
 Salary Sheet2
Name Basic Salary H.R.A Bonus P.F Net Salary
Rahul 6000 1500 1200 600 8100
Raj 7000 1500 1400 700 9200
Riya 4500 800 450 225 5525
Priya 3500 800 350 175 4475
Puja 4000 800 400 200 5000
Payel 8000 1500 1600 800 10300
Pinki 1000 800 100 50 1850
Pallabi 2000 800 200 100 2900

H.R.A=IF(B2>=6000,1500,800)

Bonus=IF(B2>=6000,B2*20%,B2*10%)

P.F=IF(B2>=6000,B2*10%,B2*5%)

N.F=B2+C2+D2-E2

Discount Sheet
Name Amount Discount Net Amount
Keyboard 500 25 475
Mouse 2000 300 1700
Motherboard 5000 1250 3750
Printer 8000 2000 6000
Monitor 1000 100 900
Pendrive 500 25 475
 Discount=IF(B2<1000,B2*5%,IF(B2<2000,B2*10%,IF(B2<3000,B2*15%,IF(B2<5000,B2*20%,IF(B2<10000,B2*25%,IF(B2<20000,B2*30%,
B2*40%)))
)))

Net Amount=B2-C2

F.V
Rate 10%

Nper 3

PMT 1000

P.V 0

Type 1

F.V INR (42,130.00)

Formula=FV(B1/12,B2*12,B3,B4,B5)
 PMT
Rate 10%

Nper 2

PV 40000
FV 0

Type 1

PMT INR (1,830.54)

Formula=PMT(B1/12,B2*12,B3,B4,B5)

Advance Filter

Name Roll English Math Science Total

Ram 2 85 62 96 243

Sham 3 49 84 41 174

Madhu 5 74 58 47 179