M.

S Excel
Ex - 1
MARKSHHET

A B C D E F G H I J K L M N

RNo Name Eng Mil Maths [Link] [Link] Hindi T. Ma M. Ob Avg. Div. grd Rslt
01 RAHUL 65 75 84 95 65 65 600 449 74.8 1st A PASS
02 PRIYA 82 65 95 75 55 36 - - - - - -

FORMULA:
Marks Obtained : = sum (1st Subject : Last Subject ) -> enter.
Minimum Number : = Min (1st Subject : Last Subject ) -> enter.
Maximum Number : = Max ( 1st Subject : Last Subject ) -> enter.
Average : = Average ( 1st Subject : Last Subject ) -> enter.
Division : = IF (Average>=80, “Star Mark”, IF (Average>=60, “1st Div.”, IF (Average>=45,”2nd Div.”,
IF(Average>=30,”3rd Div.”, IF(Average <=30,”Fail”)))))
Grade : = IF (Average>=95, “O”, IF(Average>=90,”E”, IF(Average>=85,”A+”, IF(Average<=75, “A”,
IF(Average<=60, “B”, IF(Average<=45, “C”, IF(Average<=30, “D”, IF(Average>30, “F”))))
Result : = IF (Average>30,”Pass”, IF(Average<30,”Fail”))

Add any Two Numbers (+) : = SUM (B2:F2) → [Link]

any Two Numbers (-) : = (B2-F2) → enter. Multiply any
Two Numbers (*) : = (B2*F2) → enter.
Divide any Two Numbers (%) : = (B2*5%) -> enter. Or = (B2/K2)Average: = AVERAGE (B2:F2) → enter.

Ex - 2
INDIA MEDICAL STORES

A B C D E F G H
SL NO Item Name Quantity Unit Price Total Price Rate of Discount Total Discount Net Price
01 Monitor 50 6500 - 10% - -

FORMULA:
Total Price : = C2*D2 → Enter
Total Discount : = E2*F2 → Enter
Net Price : = E2-G2 → Enter

Ex - 3
SALES TAX

A B C D E F G H
SL Customer Name Month Product Name Quantity Per Quantity Sales Tax
01 AKRAM UDDIN MARCH HP PRINTER 15 8500 - -

FORMULA:
Sales : = E2*F2 → Enter
Tax : = IF(G2>100000,(G2*18%), IF(G2>65000,(G2*12%), IF(G2>30000,(G2*10%),”NO TAX”))) → Enter

1
 Ex - 4
SALES SHEET

A B C D E F G H
SL Item Name Quantity Unit Price Total Price Rate of Discount Total Discount Net Price
NO
01 Monitor 50 6500 - 10% - -

FORMULA:
Total Price : = C2*D2 → Enter
Total Discount : = E2*F2 → Enter
Net Price : = E2-G2 → Enter

Ex - 5 CASH MAINTAIN

A B C D E F G H
Product Name Quantity Per Rs. Value Discount Discount Price Sailing Price Total Amount
Hp Printer 15 4500 - - - - -

FORMULA:
Value : = B2*C2 → Enter ( i.e. Value = Quantity*Per Rs → Enter )
Discount : = C2*5% → Enter ( i.e. Discount = Per Rs* 5% → Enter )
Discount Price : = C2-E2 → Enter ( i.e. Discount Price = Per Rs Discount → Enter )
Sailing Price : = B2*F2 → Enter ( i.e. Sailing Price = Quantity*Discount Price → Enter )
Total Amount : = F2*G2 → Enter ( i.e. Total Amount = Discount Price*Sailing Price → Enter )

Ex - 6 EMPLOYEE SALARY SHEET

A B C D E F
SL NO EMPL-NAME BASIC SALARY HOUSE RENT MEDICAL ALLOWANCE TOTAL SALARY
01 PRODIP ROY 5000 - - -

FORMULA:
House Rent : = C2*10% → Enter
Medical Allowance : = C2*40% → Enter
Total Salary : = SUM(C2:E2) → Enter

Ex - 7 SIMPLE INTEREST

Amount (Principal) 10000

Interest rate 8%
Term (Years) 10
Interest -
FORMULA:
Interest : = Principal * rate*term.

2
Ex - 8
PAYMNET FOR A LOAN

C5 Loan Amount 15000

C6 Interest rate 4.50%
C7 Periods (Term in Months) 60
C8 Compounding Periods per year 12
C9 Monthly Payment -

FORMULA:
= PMT(C6/12, C7, -C5) ( i.e = PMT ( Rate, Periods, - Amount )

Ex - 9
COMPOUND INTEREST

C5 Present Value 10000

C6 Interest rate 5%
C7 Terms (years) 10
C8 Compounding Periods Per Year 12
C9 Future Value -
FORMULA:
= FV(C6/18, C7*C8, 0, -C5) { i.e, = FV( Rate, nper, pmt, pv) }

Ex - 10 NTEREST RATE FOR LOAN

C5 Loan Amount 150000

C6 Monthly Payment 1500
C7 Periods (term in Months) 60
C8 Compounding Periods 12
C9 Interest rate -
FORMULA:
= rate(C7,C6,-C5)*12 ( i.e = Rate ( Periods, Payments, -Amount)*12 )

Ex - 11 PAYMENT PERIODS FOR LOAN

C5 Loan Amount 150000

C6 Interest rate 4.50%
C7 Monthly Payment 1500
C8 Compounding Periods Per Year 12
C9 Periods (term in months) -

FORMULA:

= NPER(C6/12,C7, -C5) { i.e. = NPER(rate, payment, -loan) }

3
 Ex - 12
EMPLOYEE DATA SHEET

Experience
SL NO Name Position Salary Joint Date Year Months Phone No Address
01 Ajay Manager 15200 12 May 2014 - - 0123456789 ****

Position Name : Manager, Doctor, Engineer, Driver, Security, Cashier, Finance.

FORMULA:

Total Salary : = SUM( Salary First :Salary Last ) → Enter

Total Employee : = Count a( Name First : Name Last ) → Enter
Total Manager : = Count if(Position, “Manager”) → Enter. ( Doctor, Engineer, etc. aresame code)
Highest Salary : = Max(Salary First : Salary Last) → Enter
Minimum Salary : = Min(salary First : Salary Last) → Enter

Ex - 13
ATTENDANCE COUNTING

A B C D E F G H I J
SI No Staff Name 1 2 3 4 5 6 7 Total Attendance
1 Ajoy P P A P A P P
2 Rahul A P A A A P P
3 Sunil P P P P P P A
4 Priya A P A P A P A
5 Puja P P P P P P P
Formula : Attendance : =Countif(C3:I3,”P”) → Enter

Ex - 14
CONDITIONAL CHECKBOX
A B C D
1 ID Name Arrived IN/OUT
2 135 Raj
3 542 Puja
4 125 Rahul
5 1365 Kamal
Formula :
IN/OUT : =IF(C2=TRUE,”IN”,”OUT”)
Shortcut :
I 1. Automatic adjust Serial Number : =ROW() – ROW($A$3) → Enter
2. Auto fit cells : Go to Sheet1 or Sheet2 → right click → View Code → Replace with General to
worksheet → Private Sub Worksheet_Selection
[Link]
End Sub
3. Conditional Format ( Red color below 5 number and Yellow color above 5 number :
Press Ctrl1 → Custom → Clear the General → [red][<5];[green][>=5] → Enter.

4
Ex - 15
ELECTRICITY BILL
Consumer Month ofPeriod Previous CurrentUnit Meter Unit Total
SL No
Number Unit Rent Charge Amount
01 111 Jan Feb 501 985 40
02 112 Jan – Feb 216 312 40

CONDITION FORMULA
1. Total Unit 0 199, Unit Charges Rs- 4 /-. Unit Charge : = if(Total unit<200, total unit*4, if(total
2. Total Unit 200 399, Unit Charges Rs- 4.50 /-. unit<400, total unit*4.5, if(total unit<600, total unit*5,
3. Total Unit 400 599, Unit Charges Rs- 5 /-. if(total unit<800, total unit*5.5, if(total unit<1000, total
4. Total Unit 600 799, Unit Charges Rs- 5.50 /-. unit*6))))))
5. Total Unit 800 999, Unit Charges Rs- 6 /-. Total Amount : = ( Unit Charges + meter rent)

Consumer Meter Previous Current Total Rate /Unit

SL C/D Unit Bill
Name Number Unit Unit Unit
No
01 Abul Hussain 123456789 D 120 245
02 Komrul Islam 012345678 C 234 365

Total Unit : =sum( Current – Previous )

Rate / Unit : = if(C/D = “C”, 20, 8)
Unit Bill : =Sum(Total Unit * Rate / Unit )

15% 50 5%
Sl Meter Serve Due Delay Total Bill
Name Area Unit Amount Tax
No No Charge Amount Fine
01 Abdul Lala D1658 154 15
02 Rahim Appin D2365 321 15

Note

1. যে সকল বিদ্য ুৎ গ্রাহক ১ ইউবিটের সমাি িা যিশী এিং ১৯৯ ইউবিটের সমাি িা কম বিদ্য ু িুিহার কটর তাটদ্রটক প্রবত
ইউবিটের জিু ৪ োকা কটর বিদ্য ুৎ বিল পবরটশাধ করটত হয়।
2. যে সকল বিদ্য ুৎ গ্রাহক ২০০ ইউবিটের সমাি িা যিবশ এিং ৩৯৯ ইউবিটের সমাি িা কম বিদ্য ুৎ িুিহার কটর তাটদ্রটক
প্রবত ইউবিটের জিু ৪.৫০ োকা কটর বিদ্য ুৎ বিল পবরটশাধ করটত হয়।
3. যে সকল বিদ্য ুৎ গ্রাহক ৪০০ ইউবিটের সমাি িা যিবশ এিং ৫৯৯ ইউবিটের সমাি িা কম বিদ্য ুৎ িুিহার কটর তাটদ্রটক
প্রবত ইউবিটের জিু ৫ োকা কটর বিদ্য ুৎ বিল পবরটশাধ করটত হয়।

Formula

Amount : = IF(AND (E9>=1, E9<=199), E9*4, IF(AND(E9>=200,E9<=399),E9*4.50, IF(AND(E9>=400,E9<=599),

E9*5))) → Enter.
Tax : = Amount*15% → Enter.
Due Amount : = F9 (Amount) → Enter.
Delay Fine : = F9*5%. → Enter.
Total Bill : = F9+G9+H9+I9+J9 → Enter.

5
 Ex - 16

Add Data: Private Sub CommandButton1_Click() Update Data: Private Sub CommandButton2_Click()
Dim x As Long Dim x As Long
Dim y As Worksheet Dim y As Long
Set y = Sheets("Sheet1") x = Sheets("sheet1").Range("A" &
[Link]).End(xlUp).RowFor y = 2 To x
x = [Link]("B" & [Link]).End(xlUp).RowWith y
If Sheets("sheet1").Cells(y, 1).Text = [Link]
.Cells(x + 1, "B").Value = [Link]
ThenSheets("sheet1").Cells(y, 2) = [Link]
.Cells(x + 1, "C").Value = [Link]
Sheets("sheet1").Cells(y, 3) = [Link]
………… ………
.Cells(x + 1, "P").Value = [Link] End With
Sheets("sheet1").Cells(y, 16) = [Link]
'clear the data
[Link] = "" End If
[Link] = "" Next y
……….. End Sub
[Link] = ""
Private Sub CommandButton5_Click() Dim x As Long
End Sub
Dim y As Long
Reset : Private Sub x = Sheets("sheet1").Range("A" &
CommandButton3_Click() [Link]).End(xlUp).RowFor y = 2 To x
If Sheets("sheet1").Cells(y, 1).Text = [Link]
Unload Me [Link]
Then
End Sub
[Link] = Sheets("sheet1").Cells(y, 12)………….
Quite: Private Sub CommandButton4_Click() [Link] = Sheets("sheet1").Cells(y, 16)
Unload Me End Sub End If
Next y End Sub

6
 Ex - 17
Like or Dislike
Name Score Like /Dislike
Ahad 49 imoji
Abdulla 56 Imoji
Foriz 31 Imoji
Parbin 87 imoji

Formula: Select all (Score) →ctrl+1 →custom → [>50][color10]#(means windows button)+.(dot)(dislike imoji);
[<50][color3]#(means windows button)+.(dot)( like imoji);

Ex - 18
Automatic fill yellow color <500

Name January February March April

Anamul 678 305 409 555
Imrul 497 500 499 508
Ruhul 951 450 651 25o
Maruf 101 998 996 198

Formula: Alt+H L H M → cell value → greater than or equal to → 500 → format → fill → Choose yellow color → ok.

Ex - 19 Add +91 in all number

Name Number
Mehbub Hassan 9265124356
Abdulla 9678546321

Formula: Select all (Number) → Ctrl + 1 → Custom → +91 0000000000.

Ex - 20 Monthly EMI Calculator

Monthly EMI Calculator
Particular Amount
Loan Amount 100000
Months 12
Interest Rate (Monthly) 12%

EMI
Total Amount
Total Interest
Formula:
EMI =PMT(Interest rate, Months, Loan Amount)
Total Amount =EMI*Months
Total Interest =Total Amount – Loan Amount

7
Ex - 21 Date to Day Convert
Date Day
1/1/2024
1/2/2024
1/3/2024
Formula: =TEXT(B3,”dddd”), e.g. → B3 means Column and Row of first Date.

Ex - 22
Vertical to Horizontal convert
TRANSPOSE Function
ID 101 102 103 104 105
Name Rahul Sham Sumaiya Akash Priya
Address Assam Delhi Bihar Kolkata Punjab
Department Finance IT IT Field Finance
Salary 45000 65000 60000 35000 42000

ID Name Address Department Salary

Enter formula

Formula: =TRANSPOSE(B3:G6) , E.g. → B3 means ID and G6 means 42000

Ex - 23 Vertical to Horizontal all number calculate in one click

Supplier Name Feb-25 Mar-25 Apr-25 Total
Rahul, Guwahati 15000 25000 5000
Priya, Kolkata 4500 6500 4100
Abul, Delhi 24500 6500 4500
Total

Formula: ALT + =
Auto Adjust Text
Ex - 24
Applicant Name Adjust Text
rahim uddin
prity shing
abul hussain mazumder

Formula: =PROPER(B2), E.g. → B2 means rahim Uddin

8
 Ex - 25 Add +91 in all number

Name Mobile Number Add +91

Mehbub Hassan 9265124356
Abdulla 9678546321

Formula: =”+91”& select Number column

Ex - 26 Age Calculate
Name Date of Birth Age
Kalam 15-Jan-2000
Rahul 19-Mar-2007

Formula: =datedif(C4,Today(),”Y”), // C4 means 15-Jan-2000

Ex - 27 Calculate working hours

Time
Full Name Working Hours
IN OUT
Kalam Uddin Barbhuiya 9:45 AM 4:15 PM
Rahul Roy 10:00 AM 3:00 PM

Formula: =MOD(OUT – IN, 1)