when want highest sale from particular state
sort, filter,take combination how and where to use
top 5
# Excel Formulas - Hinglish Manual (शुरुआत के लिए)
**Beginner ke liye Complete Guide with Examples**
---
## 📚 Vishay-Suchi (Table of Contents)
1. Excel Formulas ke Basics
2. Basic (Samanya) Formulas
3. Arithmetic (Ganit) Formulas
4. Logical (Tarkik) Formulas
5. Text (Shabd) Formulas
6. Date aur Time Formulas
7. Lookup aur Reference Formulas
8. Advanced Formulas
9. Learning Tips aur Practice
10. Common Mistakes
---
## 1️⃣ Excel Formulas ke Basics (Bunyadi Jaankari)
1️⃣
### Formula kya hota hai?
**Formula** ek instruction hota hai jo Excel ko batata hai ki calculation kaise
karni hai.
**Formula likne ke rules:**
- Har formula `=` se shuru hota hai
- Formula likne ke baad **Enter** dabate ho
- Cell reference likho jaise A1, B2, C3
- Range likho jaise A1:A10 (A1 se A10 tak)
### Formula ke Parts:
```
=SUM(B2:B5)
│ │ │
│ │ └─ Range (jahan data hai)
│ └────── Function Name
└────────── Equal sign (zaroori!)
```
**Example:**
```
=A1+B1 → A1 aur B1 ka sum
=A2*3 → A2 ko 3 se multiply karo
=C5/D5 → C5 ko D5 se divide karo
```
---
## 2️⃣ Basic (Samanya) Formulas - Sabse Important
2️⃣
### 1. SUM Formula - Jodna (जोड़ना)
**Kya karte ho:** Kai numbers ko jodna (Add karna)
**Syntax:**
```
=SUM(number1, [number2], ...)
=SUM(Range)
```
**Examples:**
| Description | Formula | Result |
|---|---|---|
| Do numbers jodna | =SUM(5,10) | 15 |
| A1 se A5 tak jodna | =SUM(A1:A5) | Total |
| Specific cells jodna | =SUM(A1,A3,A5) | Total |
**Practice Example - Marks ka Total:**
```
Student ka naam: Ram
Marks:
- Hindi: 85
- Maths: 92
- Science: 88
Formula: =SUM(B2:B4)
Result: 265
```
---
### 2. AVERAGE Formula - Ausat Nikalna (औसत निकालना)
**Kya karte ho:** Average yani median nikalna
**Syntax:**
```
=AVERAGE(Range)
=AVERAGE(number1, [number2], ...)
```
**Examples:**
| Description | Formula | Result |
|---|---|---|
| A1 se A5 ka average | =AVERAGE(A1:A5) | Average value |
| Specific values | =AVERAGE(80,90,85) | 85 |
| Marks ka average | =AVERAGE(B2:B10) | Marks ka median |
**Practice Example:**
```
Sales for 5 months:
10000, 12000, 15000, 11000, 13000
Formula: =AVERAGE(B2:B6)
Result: 12200
```
---
### 3. COUNT Formula - Ginti Karna (गिनती करना)
**Kya karte ho:** Kitne numbers hain, yeh count karna
**Syntax:**
```
=COUNT(Range) → Sirf numbers count karte ho
=COUNTA(Range) → Numbers aur text dono count karte ho
=COUNTBLANK(Range) → Empty cells count karte ho
```
**Examples:**
| Formula | Kya hota hai |
|---|---|
| =COUNT(A1:A10) | Kitne numbers hain |
| =COUNTA(A1:A10) | Kitne cell filled hain |
| =COUNTBLANK(A1:A10) | Kitne empty cells hain |
**Practice Example:**
```
List: 10, 20, "Text", 30, , 40
=COUNT(A1:A5) → 4 (sirf numbers)
=COUNTA(A1:A5) → 5 (numbers + text)
=COUNTBLANK(A1:A5) → 1 (empty cell)
```
---
### 4. MIN aur MAX Formula - Chota Bada Nikalna
**MIN:** Sabse chota (छोटा) value
**MAX:** Sabse bada (बड़ा) value
**Syntax:**
```
=MIN(Range) → Chota value
=MAX(Range) → Bada value
```
**Examples:**
| Formula | Result |
|---|---|
| =MIN(5,10,3,8) | 3 |
| =MAX(5,10,3,8) | 10 |
| =MIN(A1:A10) | Column ka chota |
| =MAX(A1:A10) | Column ka bada |
**Practice Example - Student Marks:**
```
Marks: 85, 92, 78, 95, 88
=MIN(B2:B6) → 78 (Sabse kam marks)
=MAX(B2:B6) → 95 (Sabse zyada marks)
```
---
## 3️⃣ Arithmetic (Ganit) Formulas - Ganit ke Operations
3️⃣
### Basic Operations:
| Operation | Symbol | Example | Result |
|---|---|---|---|
| Jodna (Add) | + | =10+5 | 15 |
| Nikalna (Subtract) | - | =10-5 | 5 |
| Multiply karna | * | =10*5 | 50 |
| Divide karna | / | =10/5 | 2 |
| Baqi nikalna (Modulo) | MOD | =MOD(10,3) | 1 |
| Power/Exponent | ^ | =2^3 | 8 |
| Percentage | % | =50%*100 | 50 |
---
### PRODUCT Formula - Multiply Karna (गुणा करना)
**Kya karte ho:** Saari numbers ko multiply karna
**Syntax:**
```
=PRODUCT(number1, [number2], ...)
=PRODUCT(Range)
```
**Examples:**
```
=PRODUCT(2,3,4) → 24 (2*3*4)
=PRODUCT(A1:A3) → A1*A2*A3
```
---
### SQRT Formula - Square Root (वर्गमूल)
**Kya karte ho:** Square root nikalna
**Syntax:**
```
=SQRT(number)
```
**Examples:**
```
=SQRT(16) → 4
=SQRT(25) → 5
=SQRT(A1) → A1 ka square root
```
---
### POWER Formula - Power/Exponent
**Kya karte ho:** Number ko power dena
**Syntax:**
```
=POWER(base, power)
```
**Examples:**
```
=POWER(2,3) → 8 (2 ki power 3)
=POWER(5,2) → 25 (5 ka square)
=POWER(10,2) → 100
```
---
## 4️⃣ Logical (Tarkik) Formulas - Haan ya Na (हाँ या ना)
4️⃣
### IF Formula - Agar... To (अगर...तो)
**Kya karte ho:** Condition check karna aur fir do different kaam karna
**Syntax:**
```
=IF(condition, value_if_true, value_if_false)
```
**Examples:**
**Example 1 - Pass/Fail:**
```
Marks: 75
Passing marks: 40
Formula: =IF(B2>=40, "Pass", "Fail")
Result: "Pass"
```
**Example 2 - Discount:**
```
Price: 1000
Discount sirf 500+ price par
Formula: =IF(B2>=500, B2*10%, B2*0%)
Result: 100 (agar price 1000 hai)
```
**Example 3 - Grade:**
```
Marks: 85
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","D")))
Result: "B"
```
---
### AND Formula - Sab Sach (सब सच)
**Kya karte ho:** Agar sab conditions true ho tab TRUE, nahi to FALSE
**Syntax:**
```
=AND(condition1, condition2, ...)
```
**Examples:**
```
=AND(5>3, 10>5) → TRUE (dono true hain)
=AND(5>3, 10<5) → FALSE (ek false hai)
=AND(A1>10, B1<20) → Condition check karo
```
---
### OR Formula - Koi Ek Sach (कोई एक सच)
**Kya karte ho:** Agar koi bhi condition true ho to TRUE
**Syntax:**
```
=OR(condition1, condition2, ...)
```
**Examples:**
```
=OR(5>10, 10>5) → TRUE (ek true hai)
=OR(5>10, 10<5) → FALSE (dono false hain)
=OR(A1>10, B1<20) → Check karega
```
---
### NOT Formula - Ulta Nikalna (उल्टा)
**Kya karte ho:** TRUE ko FALSE aur FALSE ko TRUE karna
**Syntax:**
```
=NOT(condition)
```
**Examples:**
```
=NOT(5>10) → TRUE (kyunki 5>10 false hai)
=NOT(5<10) → FALSE (kyunki 5<10 true hai)
```
---
## 5️⃣ Text (Shabd) Formulas - Likhna-Padna
5️⃣
### CONCATENATE Formula - Shabd Jodna (शब्द जोड़ना)
**Kya karte ho:** Do ya zyada text ko jodna (combine karna)
**Syntax:**
```
=CONCATENATE(text1, text2, ...)
=CONCAT(text1, text2, ...)
Or simple method: =A1&B1&C1
```
**Examples:**
**Example 1 - Name Jodna:**
```
First Name: Raj
Last Name: Kumar
Formula: =A1&" "&B1
Or: =CONCATENATE(A1," ",B1)
Result: "Raj Kumar"
```
**Example 2 - Address Banana:**
```
City: Delhi
Country: India
Formula: =C1&", "&D1
Result: "Delhi, India"
```
---
### UPPER aur LOWER Formula - Bade Chhote Letters (बड़े छोटे अक्षर)
| Formula | Kya karte ho | Example |
|---|---|---|
| =UPPER(text) | Sab letters bade karo | =UPPER("hello") → "HELLO" |
| =LOWER(text) | Sab letters chhote karo | =LOWER("HELLO") → "hello" |
| =PROPER(text) | Har word ka pehla letter bada | =PROPER("hello world") → "Hello
World" |
---
### LEN Formula - Lambayi Nikalna (लंबाई निकालना)
**Kya karte ho:** Text ki length nikalna (kitne characters hain)
**Syntax:**
```
=LEN(text)
```
**Examples:**
```
=LEN("Hello") → 5
=LEN("Excel Formulas") → 15
=LEN(A1) → A1 ke text ki length
```
---
### FIND aur SEARCH Formula - Khoj Karna (खोज करना)
**Kya karte ho:** Kisi text ko dusre text mein dhundna
**Syntax:**
```
=FIND(find_text, within_text)
=SEARCH(find_text, within_text)
```
**Examples:**
```
=FIND("World", "Hello World") → 7 (7th position pe hai)
=FIND("a", "Excel") → 3 (3rd position)
=SEARCH("WORLD", "Hello World") → 7 (case-insensitive)
```
---
### REPLACE Formula - Badalna (बदलना)
**Kya karte ho:** Text ka koi hissa badalna
**Syntax:**
```
=REPLACE(old_text, start_num, num_chars, new_text)
```
**Examples:**
```
=REPLACE("Hello", 1, 1, "J") → "Jello"
=REPLACE("Excel", 2, 2, "XX") → "EXXel"
```
---
### TRIM Formula - Khaali Jagah Hatana (खाली जगह हटाना)
**Kya karte ho:** Text ke aage-peeche khaali jagah hata dena
**Syntax:**
```
=TRIM(text)
```
**Examples:**
```
=TRIM(" Hello ") → "Hello"
=TRIM(A1) → A1 ke text se spaces hatao
```
---
## 6️⃣ Date aur Time Formulas - Tarikh aur Samay
6️⃣
### TODAY Formula - Aaj ki Tarikh (आज की तारीख)
**Kya karte ho:** Aaj ki tarikh nikalna
**Syntax:**
```
=TODAY()
```
**Examples:**
```
=TODAY() → 29-11-2025 (aaj ki tarikh)
```
---
### NOW Formula - Abhi Ka Samay (अभी का समय)
**Kya karte ho:** Aaj ka date aur time dono
**Syntax:**
```
=NOW()
```
**Examples:**
```
=NOW() → 29-11-2025 13:05:30
```
---
### YEAR, MONTH, DAY Formulas - Saal, Mahina, Din
**Kya karte ho:** Date se saal, mahina ya din nikalna
| Formula | Example | Result |
|---|---|---|
| =YEAR(date) | =YEAR(A1) | 2025 |
| =MONTH(date) | =MONTH(A1) | 11 |
| =DAY(date) | =DAY(A1) | 29 |
**Examples:**
```
Date: 15-01-2025
=YEAR(A1) → 2025
=MONTH(A1) → 1
=DAY(A1) → 15
```
---
### DATEDIF Formula - Din/Mahina/Saal ka Antar (दिन का अंतर)
**Kya karte ho:** Do dates ke beech ka farak nikalna
**Syntax:**
```
=DATEDIF(start_date, end_date, "D")
```
**"D"** = Days (din)
**"M"** = Months (mahine)
**"Y"** = Years (saal)
**Examples:**
```
Start Date: 01-01-2025
End Date: 29-11-2025
=DATEDIF(A1, B1, "D") → 332 (din ka antar)
=DATEDIF(A1, B1, "M") → 10 (mahine)
=DATEDIF(A1, B1, "Y") → 0 (saal)
```
---
## 7️⃣ Lookup aur Reference Formulas - Dhoondna
7️⃣
### VLOOKUP Formula - Vertical Search (सीधी खोज)
**Kya karte ho:** Kisi table mein ek value dhundna aur related value return karna
**Syntax:**
```
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
```
**Practice Example - Student aur Roll Number:**
```
Table:
Roll No | Name
1001 | Raj
1002 | Priya
1003 | Aman
Dhundhna: Roll No 1002 ka naam kya hai?
Formula: =VLOOKUP(1002, A1:B4, 2, FALSE)
Result: "Priya"
```
---
### INDEX aur MATCH Formula - Aur Bhi Powerful
**Kya karte ho:** Specifically kisi cell ki value lana
**Syntax:**
```
=INDEX(array, row_num, [col_num])
=MATCH(lookup_value, lookup_array, [match_type])
```
**Combined:**
```
=INDEX(B1:B10, MATCH("Raj", A1:A10, 0))
```
---
## 8️⃣ Advanced Formulas - Aage ka Padhai
8️⃣
### SUMIF Formula - Condition ke sath Sum (शर्त के साथ जोड़)
**Kya karte ho:** Kisi condition ko match karte hue numbers jodna
**Syntax:**
```
=SUMIF(range, criteria, [sum_range])
```
**Example - City wise Sales:**
```
City | Sales
Delhi | 10000
Mumbai | 15000
Delhi | 12000
Mumbai | 13000
Delhi ka total sales nikalna:
Formula: =SUMIF(A1:A4, "Delhi", B1:B4)
Result: 22000
```
---
### COUNTIF Formula - Condition ke sath Count (शर्त के साथ गिनती)
**Kya karte ho:** Kisi condition ko match karte hue count karna
**Syntax:**
```
=COUNTIF(range, criteria)
```
**Example - Passing Students:**
```
Marks: 45, 90, 75, 35, 85
Kitne students pass hue? (passing marks 40+)
Formula: =COUNTIF(B1:B5, ">=40")
Result: 4
```
---
### AVERAGEIF Formula - Condition ke sath Average
**Kya karte ho:** Condition match karte hue average nikalna
**Syntax:**
```
=AVERAGEIF(range, criteria, [average_range])
```
**Example:**
```
Department | Salary
IT | 50000
HR | 40000
IT | 55000
HR | 42000
IT department ka average salary:
Formula: =AVERAGEIF(A1:A4, "IT", B1:B4)
Result: 52500
```
---
### IFERROR Formula - Galti Sambhalna (गलती संभालना)
**Kya karte ho:** Agar koi error aaye to kuch aur dikhana
**Syntax:**
```
=IFERROR(value, value_if_error)
```
**Example:**
```
=IFERROR(A1/B1, "Cannot divide")
If B1 is 0, error aa jayega, lekin formula "Cannot divide" dikhaega
```
---
## 9️⃣ Learning Tips aur Practice (Seekhne ke Tarike)
9️⃣
### 📝 Step-by-Step Learning Path:
**Level 1 - Pehla Hafta (Basic):**
- [ ] SUM, AVERAGE, COUNT practice karo
- [ ] MIN, MAX use karo
- [ ] Daily marksheet banao aur sum nikalo
**Level 2 - Doosra Hafta (Intermediate):**
- [ ] IF formula sikho
- [ ] Text formulas (CONCATENATE, UPPER, LOWER) practice karo
- [ ] Sales data ke saath kaam karo
**Level 3 - Teesra Hafta (Advanced):**
- [ ] VLOOKUP aur INDEX-MATCH sikho
- [ ] SUMIF, COUNTIF practice karo
- [ ] Real project banao
**Level 4 - Char Hafta (Expert):**
- [ ] Complex formulas combine karo
- [ ] IFERROR, NESTED formulas use karo
- [ ] Complete project bana lo
---
### 💡 Practice Ideas (Practice Karne ke Liye):
**1. Student Marksheet (Chhaatra Ank-Patra):**
```
Create a table with:
- Student names
- Marks in 5 subjects
- Use SUM for total
- Use AVERAGE for average
- Use IF for pass/fail
- Use COUNTIF for passing students
```
**2. Monthly Expenses (Mahine ka Kharch):**
```
- Category names
- Amount spent
- Use SUM for total
- Use IF for warning if > budget
- Use COUNTIF for entries
```
**3. Sales Report (Bikri Ka Report):**
```
- Product name
- Salesman name
- Sales amount
- Use VLOOKUP for product details
- Use SUMIF for total by salesman
- Use MAX for highest sale
```
**4. Student Database (Chhatra Database):**
```
- Roll number
- Name
- Department
- Use INDEX-MATCH for lookup
- Use COUNTIF for department-wise count
```
---
### 📚 Aur Padhne ke Tarike:
**1. Daily Practice (Roz 10 Minutes):**
- Har din kisi ek formula ko practice karo
- Excel khol kar real examples banao
- Small datasets ke saath kaam shuru karo
**2. Real Projects Banao:**
- Apne personal expenses track karo
- Family ke budget ko organize karo
- School ke marks ko compile karo
**3. Online Resources Use Karo:**
- YouTube par tutorials dekho
- Microsoft Excel help check karo
- Kaggle datasets download karke practice karo
**4. Formula ke Mistakes Se Seekho:**
- Error messages padho aur samjho
- Syntax galat kyo tha, samjho
- Dobara sahi tarike se likho
---
## 🔟 Common Mistakes - Aksar Hone Wali Galtiyan (अक्सर होने वाली गलतियाँ)
### ❌ Mistake 1: Equal Sign Bhulna
```
❌ GALAT: SUM(A1:A5) → Formula nahi banega!
✅ SAHI: =SUM(A1:A5) → Sahi!
```
---
### ❌ Mistake 2: Wrong Range Format
```
❌ GALAT: =SUM(A1-A5) → Dash instead of colon
✅ SAHI: =SUM(A1:A5) → Colon use karo
❌ GALAT: =SUM(A1;A5) → Semicolon
✅ SAHI: =SUM(A1:A5) → Colon
```
---
### ❌ Mistake 3: Nested Brackets Galat
```
❌ GALAT: =IF(A1>10, SUM(B1:B5), 0 → Bracket nahi band!
✅ SAHI: =IF(A1>10, SUM(B1:B5), 0) → Sab close karo
```
---
### ❌ Mistake 4: Wrong Criteria Format
```
❌ GALAT: =SUMIF(A1:A5, >10, B1:B5) → Quotes nahi hain
✅ SAHI: =SUMIF(A1:A5, ">10", B1:B5) → Quotes lagao
```
---
### ❌ Mistake 5: Circular Reference
```
❌ GALAT: Cell A1 mein =A1+10 → Khud ko reference karna
✅ SAHI: Cell B1 mein =A1+10 → Doosre cell mein likho
```
---
### ❌ Mistake 6: Division by Zero
```
❌ GALAT: =A1/B1 → Agar B1=0 to error!
✅ SAHI: =IFERROR(A1/B1, 0) → Error handle karo
```
---
### ❌ Mistake 7: Text aur Number Mix-up
```
❌ GALAT: =SUM("10", "20") → Text hai number nahi
✅ SAHI: =SUM(10, 20) → Direct numbers use karo
Or: =SUM(VALUE("10"), VALUE("20")) → Text ko number banao
```
---
### ❌ Mistake 8: Wrong Cell Reference
```
❌ GALAT: =SUM(A1:C5) → Galat range select
✅ SAHI: =SUM(A1:A5) → Sahi range select
```
---
### ❌ Mistake 9: Case Sensitivity (Text mein)
```
❌ GALAT: =IF(A1="delhi", "Found", "Not")
(data mein "Delhi" hai with capital D)
✅ SAHI: =IF(LOWER(A1)="delhi", "Found", "Not")
(LOWER use karke case-insensitive banao)
```
---
### ❌ Mistake 10: Spaces Bhulna
```
❌ GALAT: =IF(A1=" Delhi ", A1=) → Space bhul sakta hai match!
✅ SAHI: =IF(TRIM(A1)="Delhi", A1) → TRIM use karo
```
---
## 🎯 Quick Reference Sheet (Ek Nazar mein Sab)
### Most Used Formulas:
| Formula | Matlab | Example |
|---|---|---|
| =SUM() | Jodna | =SUM(A1:A5) |
| =AVERAGE() | Ausat | =AVERAGE(B1:B5) |
| =COUNT() | Ginti | =COUNT(C1:C5) |
| =IF() | Agar-To | =IF(A1>50,"Pass","Fail") |
| =CONCATENATE() | Jodna (Text) | =CONCAT(A1,B1) |
| =VLOOKUP() | Dhundna | =VLOOKUP(Value, Table, 2, 0) |
| =SUMIF() | Condition Sum | =SUMIF(A1:A5,">10",B1:B5) |
| =COUNTIF() | Condition Count | =COUNTIF(A1:A5,"Delhi") |
| =TODAY() | Aaj ka date | =TODAY() |
| =MAX() | Sabse bada | =MAX(A1:A5) |
| =MIN() | Sabse chota | =MIN(A1:A5) |
---
## 📖 Conclusion - Samapt Karte Hain
**Excel formulas seekhne ke liye:**
1. ✅ Basics samjho (SUM, AVERAGE, COUNT)
2. ✅ Daily 10 minute practice karo
3. ✅ Real projects banao
4. ✅ YouTube par tutorials dekho
5. ✅ Mistakes se seekho
6. ✅ Regular practice jari rakho
**Ek mahine mein:**
- ✅ Beginner level ke formulas clear ho jayenge
- ✅ 2-3 projects bana doge
- ✅ Job interviews mein excel skills dikhaa soge!
**Do mahine mein:**
- ✅ Advanced formulas sikha loge
- ✅ Professional level ke sheets bana soge
- ✅ Data analysis ke projects ready ho jayenge!
---
**Happy Learning! 🎉**
**Shubhkamnaye! शुभकामनाएँ ! 🚀**
---
*Last Updated: 29 November 2025*
*Created for beginners learning Excel formulas in Hinglish*