Basic Excel Formula
Formula Use Example
=SUM(A1:A10) Numbers ka total A1 se A10 ka sum
=AVERAGE(A1:A10) Average nikalne ke liye Marks ka average
=MAX(A1:A10) Sabse bada value Highest marks
=MIN(A1:A10) Sabse chota value Lowest marks
=COUNT(A1:A10) Number count karta hai Kitne cells me number hain
=COUNTA(A1:A10) Text + Number dono count Names + Marks count
=IF(A1>=33,"PASS","FAIL") Condition check karta hai Pass/Fail formula
=ROUND(A1,2) Decimal control karta hai 89.567 → 89.57
=NOW() Current Date + Time Auto update hota hai
=TODAY() Current Date Aaj ki date
=UPPER(A1) Capital letters name → NAME
=LOWER(A1) Small letters NAME → name
=PROPER(A1) First letter capital sarwan → Sarwan
=LEN(A1) Characters count Word length
=LEFT(A1,3) Left se characters SARWAN → SAR
=RIGHT(A1,3) Right se characters SARWAN → WAN
=MID(A1,2,3) Beech se cut SARWAN → ARW
=CONCAT(A1," ",B1) 2 cells join First + Last name
=VLOOKUP(A2, D1:E10, 2, FALSE) Search data Roll no. se Name find
=HLOOKUP(A2, A1:J2, 2, FALSE) Horizontal search Header ke niche data
=ABS(A1) Negative ko positive -10 → 10
Advance Excel Formula
✅ Percentage + Result ke Formula
Task Formula
Percentage =Marks/Total*100
Grade =IF(B2>=80,"A",IF(B2>=60,"B",IF(B2>=33,"C","FAIL")))
✅ Date & Time Calculations
Work Formula
Age calculate =DATEDIF(DOB cell, TODAY(), "Y")
Days difference =DAYS(A2, A1)
✅ VLOOKUP – Vertical Lookup
📌 Kab use hota hai?
Jab data column me hota hai aur aap kisi ek value ke basis par uska related data chahte ho.
✅ Formula
=VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])
Argument Matlab
lookup_value Jiska data chahiye (Roll No. ya Name)
table_array Pura table jahan search karna hai
col_index_number Konsa column ka result chahiye
FALSE = Exact match, TRUE = Approx
range_lookup
(usually FALSE use karein)
✅ HLOOKUP – Horizontal Lookup
📌 Kab use hota hai?
Jab data row me hota hai aur upar headers hote hain.
✅ Formula
=HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])
Argument Matlab
lookup_value Jisko search karna
table_array Selected rows
row_index_number Konsi row ka result chahiye
range_lookup FALSE use karein (Exact match)
XLOOKUP Function
⭐ Use (Upyog Kahan?)
✔ Roll No. se Name laana
✔ Product Code se Price laana
✔ Employee ID se Salary/Department laana
✔ Left lookup possible (VLOOKUP me nahi hota)
🧠 Formula Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Part Matlab
lookup_value Jisko dhoondhna hai (Roll No.)
lookup_array Jahan search karoge (Roll Column)
return_array Jiska result chahiye (Name Column)
if_not_found Agar na mile to kya likhe
SUMIFS Function
📌 Kahan Use Hota Hai?
Multiple conditions ke saath Total / Sum nikalna
Sales report, Attendance, GST, Stock me sabse jyada use
🔹 Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]...)
COUNTIFS Function
📌 Kahan Use Hota Hai?
Kitne items conditions satisfy kar rahe hain — ye count karta hai
Attendance, HR sheets, stock audits, filtering me useful
🔹 Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)
FILTER Function
📌 Kahan Use Hota Hai?
Large data me se required rows extract karna
Multiple criteria ke sath search
HR, Sales, Inventory, School Reports me commonly use
🔹 Syntax
=FILTER(array, include, [if_empty])
Argument Meaning
array Data range jo filter karni hai
include Condition (TRUE/FALSE)
if_empty Agar match na mile to kya dikhaye
NETWORKDAYS – Working Days Calculate
📌 Kahan Use Hota Hai?
Employee attendance aur payroll calculation
Project timeline / task duration calculation
🔹 Formula
=NETWORKDAYS(start_date, end_date, [holidays])
Argument Meaning
start_date Startdateofwork/project
end_date End dateofwork/project
holidays Optional→Arrayofholidays
SUMPRODUCT – Multi Condition + Calculation King
1️⃣ Kahan use hota hai?
Multiple conditions ke saath sum ya count karna
HR, Sales, Finance, Inventory reporting me
VLOOKUP/INDEX-MATCH se bhi zyada flexible
Interview me top formula
2️⃣ Syntax (Simple Version)
=SUMPRODUCT((condition1)*(condition2)*range_to_sum)
Part Matlab
condition1 TRUE/FALSE array (A2:A100="Delhi")
condition2 TRUE/FALSE array (B2:B100>5000)
range_to_sum Jo values add karni hain (C2:C100)
3️⃣ Example Table – Sales Report
A: City B: Product C: Sales
Delhi Pen 5000
Mumbai Pen 7000
Delhi Pencil 3000
Delhi Pen 4000
4️⃣ Total Sales Delhi + Product = Pen
=SUMPRODUCT((A2:A5="Delhi")*(B2:B5="Pen")*(C2:C5))
📌 Output → 5000 + 4000 = 9000 ✅
5️⃣ Count Example – Count rows matching multiple conditions
=SUMPRODUCT((A2:A5="Delhi")*(B2:B5="Pen"))
📌 Output → 2
(Because 2 rows match Delhi + Pen)
6️⃣ Advanced Example – Multiple Products & Threshold
Total sales Delhi + Product Pen + Sales > 4000
=SUMPRODUCT((A2:A5="Delhi")*(B2:B5="Pen")*(C2:C5>4000)*(C2:C5))
📌 Output → 5000 + 4000 = 9000 ✅