Excel Formulas in Hindi: 25 Formulas You Will Use Daily

Short answer

Excel formula हमेशा = से शुरू होता है। Office work में सबसे ज़्यादा काम आने वाले formulas हैं: SUM, AVERAGE, COUNT/COUNTA, IF, SUMIF/SUMIFS, COUNTIF/COUNTIFS, VLOOKUP या XLOOKUP, IFERROR, TEXT, TRIM, CONCAT/TEXTJOIN, LEFT/RIGHT/MID, TODAY और ROUND. Agar आप ये 15-20 formulas ठीक से सीख लेते हैं, तो MIS reports, attendance sheets, sales registers और billing data का ज़्यादातर काम संभाल सकते हैं।

Office kaam ke 10 sabse zaroori formulas, ek line mein:

1. SUM: total jodta hai
2. AVERAGE: average nikalta hai
3. COUNT / COUNTA: cells ginta hai
4. IF: condition ke hisaab se result deta hai
5. SUMIF / SUMIFS: condition wala total
6. COUNTIF / COUNTIFS: condition wali ginti
7. VLOOKUP / XLOOKUP: table se value dhoondhta hai
8. IFERROR: error ki jagah saaf message
9. TRIM / PROPER: messy text saaf karta hai
10. TODAY / DATEDIF: date aur age nikalta hai

I'll be honest: when we built YUNOVA's Excel skill, I kept cutting this list down. You don't need 400 functions. You need these, used confidently on messy real data.

This guide explains each formula in Hinglish with one small office example, so you can try it on your own sheet right now.

Formula likhne ke 4 basic rules

1. Formula = se shuru hota hai: =A2+B2.
2. Cell reference (A2) likhiye, number नहीं. Tab data badalne par result apne aap update hota hai.
3. $ lagane se reference lock hota hai: $B$1. Formula ko neeche drag karte waqt fixed rate ya tax cell ke liye zaroori hai.
4. Text ko double quotes mein likhiye: "Paid".

Group 1: Ginti aur total (math formulas)

Formula	Kya karta hai	Example
SUM	Total jodta hai	=SUM(C2:C50)
AVERAGE	Average nikalta hai	=AVERAGE(D2:D50)
MIN / MAX	Sabse chhota / bada	=MAX(E2:E50)
COUNT	Sirf numbers wali cells ginta hai	=COUNT(C2:C50)
COUNTA	Khaali ke alawa sab cells ginta hai	=COUNTA(A2:A50)
ROUND	Decimal theek karta hai	=ROUND(F2,2)

Tip: COUNT aur COUNTA mein confusion common है। Agar column mein naam hain, COUNT zero dega; wahan COUNTA lagaiye.

Group 2: Condition wale formulas

IF

=IF(C2>=40,"Pass","Fail")

Matlab: agar C2 ki value 40 ya zyada hai to "Pass", warna "Fail". Nested IF se teen result bhi de sakte hain:

=IF(C2>=75,"A",IF(C2>=50,"B","C"))

Newer Excel (Microsoft 365, Excel 2019 aur baad ke) mein IFS bhi hai, jo lambi nesting se aasaan padhta hai.

SUMIF aur SUMIFS

Ek condition: =SUMIF(B2:B100,"Delhi",D2:D100) — sirf Delhi ki sales ka total.

Kai conditions: =SUMIFS(D2:D100,B2:B100,"Delhi",C2:C100,"October") — Delhi + October.

Dhyaan dijiye: SUMIFS mein pehle sum range aati hai, SUMIF mein aakhir mein. Yahi sabse common galti hai.

	SUMIF	SUMIFS
Conditions	Sirf 1	1 ya zyada
Sum range kahan	Aakhir mein	Sabse pehle
Example	=SUMIF(B:B,"Delhi",D:D)	=SUMIFS(D:D,B:B,"Delhi",C:C,"October")

COUNTIF aur COUNTIFS

=COUNTIF(E2:E100,"Present") — kitne din present. =COUNTIFS(B2:B100,"Sales",E2:E100,"Present") — Sales team ke present din.

AND / OR

=IF(AND(C2>=40,D2>=40),"Pass","Fail") — dono subject mein pass hona zaroori.

Group 3: Lookup formulas (sabse zyada interview mein pooche jaate hain)

VLOOKUP

VLOOKUP formula ek table ke pehle column mein value dhoondhta hai aur usi row ke kisi doosre column se jawab laata hai. Syntax: =VLOOKUP(lookup_value, table, column_number, FALSE). FALSE ka matlab exact match hai. Example: product code se uski price nikalna.

=VLOOKUP(A2,Products!A:C,3,FALSE)

Product code A2 ko Products sheet ke pehle column mein dhoondho, aur teesre column ki value (price) lao. FALSE ka matlab exact match. Ise bhoolne par galat price aa sakta hai.

Limitation: VLOOKUP sirf right side dekh sakta hai. Lookup column hamesha table ka pehla column hona chahiye.

XLOOKUP

=XLOOKUP(A2,Products!A:A,Products!C:C,"Not found")

XLOOKUP left-right dono taraf kaam karta hai, default exact match deta hai, aur "not found" message andar hi likh sakte hain. Microsoft ke anusaar ye Microsoft 365 aur Excel 2021 ya baad ke versions mein milta hai. Purane Excel par office mein kaam karna ho to VLOOKUP bhi aana chahiye. Detail comparison ke liye padhiye: VLOOKUP vs XLOOKUP.

INDEX + MATCH

=INDEX(C2:C100,MATCH(A2,A2:A100,0)) — purane versions mein left lookup ke liye.

IFERROR

=IFERROR(VLOOKUP(A2,Products!A:C,3,FALSE),"Check code") — #N/A ki jagah saaf message.

Group 4: Text formulas (data cleaning)

Formula	Use	Example
TRIM	Extra spaces hatata hai	=TRIM(A2)
UPPER / LOWER / PROPER	Case badalta hai	=PROPER(A2) → "Rahul Sharma"
LEFT / RIGHT / MID	Text ka hissa nikalta hai	=LEFT(A2,2) → state code
LEN	Characters ginta hai	=LEN(A2) → mobile number 10 digit hai ya nahi
CONCAT / TEXTJOIN	Text jodta hai	=TEXTJOIN(" ",TRUE,A2,B2)
TEXT	Number/date ko format karta hai	=TEXT(B2,"dd-mmm-yyyy")
SUBSTITUTE	Shabd badalta hai	=SUBSTITUTE(A2,"-","")

Real example: customer list mein naam " rahul sharma " jaise aate hain. =PROPER(TRIM(A2)) ek hi baar mein theek kar deta hai.

Group 5: Date formulas

• =TODAY() — aaj ki date
• =DATEDIF(B2,TODAY(),"y") — age ya service years
• =EOMONTH(B2,0) — mahine ki aakhri date (due dates ke liye)
• =NETWORKDAYS(B2,C2) — do dates ke beech working days

Common errors aur unka matlab

Error	Kyun aata hai	Kya karein
#N/A	Lookup value mili nahi	Spelling, extra space (TRIM), number-as-text check karein
#VALUE!	Galat type ka data	Text ko number mein badlein
#REF!	Reference delete ho gaya	Formula dobara banayein
#DIV/0!	Zero se divide	=IFERROR(A2/B2,0)
#NAME?	Formula ka naam galat	Spelling ya quotes check karein

7 din ka practice plan

1. Day 1: SUM, AVERAGE, COUNT, COUNTA ek marks sheet par.
2. Day 2: IF aur nested IF se grade column.
3. Day 3: SUMIF/SUMIFS se city-wise sales.
4. Day 4: COUNTIFS se attendance summary.
5. Day 5: VLOOKUP aur XLOOKUP se price list jodna.
6. Day 6: TRIM, PROPER, TEXT se messy data saaf karna.
7. Day 7: Sab mila kar ek chhota monthly report.

Practice ke baad ek pivot table banana seekhiye; formulas aur pivot milkar MIS ka core hai.

FAQ

Excel formulas Hindi mein free kahan seekh sakte hain?

YUNOVA par Excel skill free hai: Hindi aur English video lessons, har lesson ke key points, quiz, aur end mein monitored final test ke baad verifiable certificate.

Sabse pehle kaunsa formula seekhna chahiye?

SUM aur IF. Iske baad SUMIFS, COUNTIFS aur ek lookup formula (VLOOKUP ya XLOOKUP).

VLOOKUP aur XLOOKUP mein kaunsa better hai?

XLOOKUP zyada flexible hai, lekin sirf naye versions mein hai. Office ke purane Excel ke liye VLOOKUP bhi aana zaroori hai.

Formula sahi hai phir bhi #N/A kyun aa raha hai?

Aksar data mein extra space hota hai ya number text format mein save hota hai. TRIM lagaiye aur format check kijiye.

Interview mein Excel ke kaunse formulas poochte hain?

VLOOKUP/XLOOKUP, IF, SUMIFS, COUNTIFS aur pivot table. Poori list ke liye padhiye Excel interview questions.

Learn it free on YUNOVA

आप ये सभी formulas step-by-step videos के साथ free में सीख सकते हैं। YUNOVA Excel skill mein basics se lekar lookups, pivot tables aur dashboards tak ka path hai, quizzes ke saath.

Start the free Excel skill on YUNOVA

Next read: Power BI vs Excel | How to become a data analyst in India