EXAMKOSH | COMPUTER KNOWLEDGE

MS Excel Formulas List with Examples 2026 – Complete Guide

Easy Hindi Explanation • Practical Examples • MCQs

MS Excel ek spreadsheet software hai, jiska use data ko table mein rakhne, calculation karne, marksheet banane, salary calculate karne, expenses manage karne aur reports taiyar karne ke liye kiya jata hai.

Excel mein agar humein bahut saare numbers ka total nikalna ho, average calculate karna ho ya kisi condition ke according result chahiye ho, to hum formulas aur functions ka use karte hain.

Is guide mein hum important Excel formulas ko simple Hindi aur Hinglish mein samjhenge. Har formula ke saath syntax, example aur result diya gaya hai, taaki beginner bhi ise aasani se seekh sake.

Is Article Mein Kya Seekhenge?

• Excel formula kya hota hai?
• Cell, row, column aur cell range ka meaning
• SUM, AVERAGE, MIN aur MAX
• COUNT, COUNTA aur COUNTBLANK
• IF, SUMIF aur COUNTIF
• VLOOKUP aur XLOOKUP ka basic use
• LEFT, RIGHT, LEN aur CONCAT
• Date aur rounding formulas
• Common formula errors aur unke solutions
• Practice MCQs with answers

1. MS Excel Formula Kya Hota Hai?

Formula ek instruction hota hai jo Excel ko batata hai ki calculation kaise karni hai. Jaise 10 aur 20 ko add karna, numbers ka average nikalna ya check karna ki student pass hai ya fail.

Excel mein formula aam taur par = (equal sign) se shuru hota hai.

Example:

=10+20

Result: 30

Isi tarah agar A1 cell mein 10 aur A2 cell mein 20 likha hai, to formula =A1+A2 bhi 30 result dega.

2. Cell, Row, Column Aur Range Samjhein

Excel formulas seekhne se pehle in basic terms ko samajhna zaroori hai.

• Cell: Row aur column ke milne se banne wala box cell kehlata hai. Example: A1.
• Column: Upar se neeche jaane wale columns ko A, B, C jaise letters se pehchana jata hai.
• Row: Left se right jaane wali row ko 1, 2, 3 jaise numbers se pehchana jata hai.
• Cell Range: Cells ke group ko range kehte hain. Example: A1:A5 ka matlab A1 se A5 tak ke cells.

Example ke liye, agar A1 se A5 tak students ke marks likhe hain, to =SUM(A1:A5) un sabhi marks ka total nikal sakta hai.

3. Excel Formula Kaise Lagayein?

1. Excel mein apni worksheet open karein.
2. Jin cells par calculation karni hai, unmein data enter karein.
3. Jis cell mein answer chahiye, us cell ko select karein.
4. Equal sign (=) se formula type karein.
5. Enter press karein. Excel result dikha dega.

Example: A1 mein 50 aur B1 mein 30 hai. C1 select karke =A1+B1 type karein. Enter press karne par C1 mein 80 aayega.

4. Basic Mathematical Formulas

Ye formulas basic calculations ke liye use hote hain.

Operation	Formula	Example Result
Addition	=A1+B1	50 + 30 = 80
Subtraction	=A1-B1	50 − 30 = 20
Multiplication	=A1*B1	50 × 30 = 1500
Division	=A1/B1	50 ÷ 10 = 5
Power	=A1^2	5² = 25

Yaad rakhein: Excel mein multiplication ke liye * aur power ke liye ^ use hota hai. Formula mein cell references use karne se data change hone par result bhi update ho sakta hai.

5. SUM Formula – Total Nikalna

SUM ka use numbers ko add karke total nikalne ke liye hota hai. Jaise students ke marks, monthly expenses ya sales ka total.

Syntax:

=SUM(number1, [number2], ...)

Example data:

Cell	Marks
A1	70
A2	80
A3	90

Formula: =SUM(A1:A3)

Result: 240

Easy explanation: Excel A1, A2 aur A3 ke numbers ko add karega: 70 + 80 + 90 = 240.

6. AVERAGE Formula – Average Nikalna

AVERAGE numbers ka arithmetic average nikalta hai. Iska use marks, monthly sales ya average expense calculate karne mein hota hai.

Syntax: =AVERAGE(A1:A3)

Example: Marks 70, 80 aur 90 hain.

Calculation: (70 + 80 + 90) ÷ 3 = 80

Result: 80

AVERAGE range ke numeric values ka average nikalta hai. Blank cells ko count nahi karta, lekin range mein maujood numeric zero average mein include hota hai.

7. MIN Aur MAX Formula

MIN range ka sabse chhota number batata hai, jabki MAX sabse bada number batata hai.

Example numbers: 25, 60, 15, 90, 45

• Formula: =MIN(A1:A5) — Result: 15
• Formula: =MAX(A1:A5) — Result: 90

Easy trick: MIN = Minimum ya sabse chhota; MAX = Maximum ya sabse bada.

8. COUNT, COUNTA Aur COUNTBLANK

In teeno functions ka use cells ko count karne ke liye hota hai, lekin har ek ka kaam alag hai.

Function	Kaam	Example
COUNT	Numeric values wale cells count karta hai.	=COUNT(A1:A10)
COUNTA	Non-empty cells count karta hai.	=COUNTA(A1:A10)
COUNTBLANK	Blank cells count karta hai.	=COUNTBLANK(A1:A10)

Example: A1 mein 100, A2 mein Rahul, A3 blank aur A4 mein 200 hai.

• =COUNT(A1:A4) ka result 2 hoga.
• =COUNTA(A1:A4) ka result 3 hoga.
• =COUNTBLANK(A1:A4) ka result 1 hoga.

9. IF Formula – Condition Check Karna

IF formula kisi condition ko check karta hai. Condition true hone par ek result aur false hone par doosra result deta hai.

Syntax:

=IF(condition, value_if_true, value_if_false)

Example: Maan lijiye B2 mein student ke marks hain. Agar passing marks 33 hain, to pass ya fail dikhane ke liye:

=IF(B2>=33,"Pass","Fail")

• Agar B2 mein 60 hai, result Pass hoga.
• Agar B2 mein 25 hai, result Fail hoga.

Simple language: Excel se keh rahe hain: agar marks 33 ya usse zyada hain to Pass likho, nahi to Fail likho.

10. COUNTIF – Condition Ke According Counting

COUNTIF un cells ko count karta hai jo di gayi condition ko satisfy karte hain.

Syntax: =COUNTIF(range, criteria)

Example: A2 se A10 tak students ke results likhe hain. Agar Pass kitne students hain, ye count karna ho:

=COUNTIF(A2:A10,"Pass")

Agar range mein 7 baar Pass likha hai, to result 7 hoga.

Marks example: B2:B20 mein marks hain aur 50 se zyada marks wale students count karne hain:

=COUNTIF(B2:B20,">50")

Yahan condition quotes mein likhi gayi hai. Is formula mein 50 se bade numbers count honge; exactly 50 include nahi hoga.

11. SUMIF – Condition Ke According Total

SUMIF un numbers ka total nikalta hai jo kisi condition se match karte hain.

Syntax: =SUMIF(range, criteria, [sum_range])

Example: A column mein product names aur B column mein sales amounts hain. Sirf Pen ki sales ka total chahiye.

A: Product	B: Sales
Pen	100
Book	200
Pen	150

Formula: =SUMIF(A2:A4,"Pen",B2:B4)

Result: 250

Explanation: Excel Pen ki dono entries ka sales total karega: 100 + 150 = 250.

12. VLOOKUP – Table Mein Data Khojna

VLOOKUP table ke pehle column mein kisi value ko search karke usi row ke doosre column se information la sakta hai.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example table:

A: ID	B: Name
101	Aman
102	Neha
103	Ravi

Agar ID 102 ka naam nikalna hai aur table A2:B4 mein hai:

=VLOOKUP(102,A2:B4,2,FALSE)

Result: Neha

Formula ko samjhein:

• 102: jis ID ko search karna hai.
• A2:B4: poori table jisme search karna hai.
• 2: doosre column se answer chahiye.
• FALSE: exact match chahiye.

Important: VLOOKUP mein lookup value table ke first column mein honi chahiye. FALSE exact match ke liye use karein.

13. XLOOKUP – Modern Lookup Formula

XLOOKUP ek modern lookup function hai jo lookup range mein value search karke corresponding result return karta hai. Ye available Microsoft 365 aur supported newer Excel versions mein use kiya ja sakta hai.

Basic syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Example: A2:A4 mein IDs aur B2:B4 mein names hain. ID 102 ka naam nikalne ke liye:

=XLOOKUP(102,A2:A4,B2:B4,"Not Found")

Result: Neha

Is formula mein agar ID nahi milti, to Not Found dikhaya ja sakta hai. Purane Excel versions mein XLOOKUP available na ho sakta hai; aise mein VLOOKUP ya doosre supported methods use karein.

14. LEFT, RIGHT Aur MID – Text Se Characters Nikalna

In functions ka use kisi text ke shuru, end ya beech se characters nikalne ke liye hota hai.

Function	Example	Result
LEFT	=LEFT("EXAMKOSH",4)	EXAM
RIGHT	=RIGHT("EXAMKOSH",4)	KOSH
MID	=MID("EXAMKOSH",3,3)	AMK

MID formula: =MID(text, start_num, num_chars)

Ismein start_num batata hai ki kis position se text lena shuru karna hai aur num_chars batata hai ki kitne characters chahiye.

15. LEN Aur TRIM – Text Ki Length Aur Extra Spaces

LEN text mein characters ki sankhya batata hai. Spaces bhi characters ke roop mein count hote hain.

=LEN("EXCEL") ka result 5 hai.

TRIM text ke shuru aur end ke extra spaces hata sakta hai aur words ke beech multiple spaces ko ek space mein badalta hai.

=TRIM(" Hello World ")

Result: Hello World

TRIM data ko clean karne mein useful hai, khaaskar jab names ya text doosri files se copy kiya gaya ho.

16. CONCAT – Text Ko Jodna

CONCAT do ya zyada text values ko jod sakta hai.

Example: A1 mein Rahul aur B1 mein Sharma hai.

=CONCAT(A1," ",B1)

Result: Rahul Sharma

Formula ke beech mein " " ek space add karta hai. Space na dene par text RahulSharma ki tarah jud sakta hai.

17. ROUND, ROUNDUP Aur ROUNDDOWN

Ye functions numbers ko specified decimal places tak round karne ke liye use hote hain.

Formula	Result	Meaning
=ROUND(12.56,1)	12.6	Nearest one decimal place
=ROUNDUP(12.51,1)	12.6	Round away from zero
=ROUNDDOWN(12.59,1)	12.5	Drop extra decimal digits

18. TODAY Aur NOW – Date Aur Time

• TODAY: Current date return karta hai. Formula: =TODAY()
• NOW: Current date aur time return karta hai. Formula: =NOW()

In formulas ke results worksheet recalculate hone par update ho sakte hain. Cell ka number format tay karta hai ki date aur time kaise display honge.

19. Common Excel Errors Aur Solutions

Error	Meaning	Kya Check Karein?
#DIV/0!	Zero ya blank divisor se divide karna.	Denominator check karein.
#NAME?	Excel function ya name ko pehchan nahi raha.	Spelling aur quotation marks check karein.
#VALUE!	Formula mein galat type ka data ho sakta hai.	Text aur numbers check karein.
#REF!	Formula ka cell reference invalid ho gaya.	Deleted ya incorrect cells check karein.
#N/A	Required value available nahi mili.	Lookup value aur exact match check karein.

20. Important Excel Formulas – Quick Revision

Formula	Use
SUM	Numbers ka total
AVERAGE	Average nikalna
MIN / MAX	Smallest / largest value
COUNT	Numeric cells count karna
COUNTA	Non-empty cells count karna
IF	Condition ke according result
COUNTIF	Condition match karne wale cells count karna
SUMIF	Condition match karne wali values ka total
VLOOKUP / XLOOKUP	Table se related information khojna
LEFT / RIGHT / MID	Text se characters nikalna
LEN / TRIM	Text length aur extra spaces manage karna
CONCAT	Text ko jodna
ROUND	Decimal values round karna
TODAY / NOW	Current date / date and time

21. MS Excel Important MCQs with Answers

Ab apni knowledge test karein. Har question ka answer dene ki koshish karein, phir answer key check karein.

Q1. Excel formula aam taur par kis symbol se shuru hota hai?

A) +
B) =
C) #
D) @

Q2. Numbers ka total nikalne ke liye kaunsa function use hota hai?

A) COUNT
B) MAX
C) SUM
D) LEN

Q3. =AVERAGE(10,20,30) ka result kya hoga?

A) 10
B) 20
C) 30
D) 60

Q4. Sabse bada number nikalne ke liye kaunsa function use hota hai?

A) MIN
B) MAX
C) SUM
D) IF

Q5. COUNT function kya count karta hai?

A) Sirf blank cells
B) Numeric values wale cells
C) Sirf text cells
D) Sirf formulas

Q6. IF formula ka main use kya hai?

A) Image insert karna
B) Condition check karna
C) File save karna
D) Font change karna

Q7. =LEFT("EXCEL",2) ka result kya hoga?

A) EL
B) EX
C) CE
D) XE

Q8. Blank cells count karne ke liye kaunsa function use hota hai?

A) COUNTA
B) COUNT
C) COUNTBLANK
D) SUMIF

Q9. =MAX(5,12,8) ka result kya hoga?

A) 5
B) 8
C) 12
D) 25

Q10. Table se exact matching value khojne ke liye VLOOKUP mein kaunsa option use hota hai?

A) TRUE
B) FALSE
C) SUM
D) MAX

MCQ Answer Key with Short Explanations

Q.	Answer	Explanation
1	B	Formula = se shuru hota hai.
2	C	SUM total nikalta hai.
3	B	(10 + 20 + 30) ÷ 3 = 20.
4	B	MAX largest value deta hai.
5	B	COUNT numeric cells count karta hai.
6	B	IF condition ke according result deta hai.
7	B	LEFT shuru ke characters deta hai: EX.
8	C	COUNTBLANK blank cells count karta hai.
9	C	12 diye gaye numbers mein sabse bada hai.
10	B	FALSE exact match maangta hai.

Conclusion

MS Excel formulas data ko jaldi calculate karne aur kaam ko easy banane mein madad karte hain. Beginners ko sabse pehle SUM, AVERAGE, MIN, MAX, COUNT aur IF jaise formulas practice karne chahiye. Uske baad COUNTIF, SUMIF aur lookup functions seekhna useful rahega.

Sirf formulas yaad karna kaafi nahi hai. Excel mein khud sample data banakar har formula lagayein aur result check karein. Regular practice se aap in functions ko office work aur computer-based examinations dono mein behtar tareeke se use kar sakte hain.

ExamKosh

Study • Practice • Succeed