💡 Excel Formulas & Functions — Full Guide
Made by Suresh — Learn Every Formula Step by Step
1️⃣ Basic Formulas
| Formula | Use |
|---|---|
| =SUM(A1:A10) | Total nikalne ke liye |
| =AVERAGE(A1:A10) | Average nikalne ke liye |
| =MIN(A1:A10) | Sabse chhoti value |
| =MAX(A1:A10) | Sabse badi value |
| =COUNT(A1:A10) | Kitne numbers hai |
| =COUNTA(A1:A10) | Kitne cells me data hai |
2️⃣ Logical Formulas
| Formula | Explanation |
|---|---|
| =IF(A1>50,"Pass","Fail") | Condition check karta hai |
| =AND(A1>10, B1<20) | Dono condition true hai kya |
| =OR(A1>50, B1>50) | Koi ek true hai kya |
| =NOT(A1>10) | Condition ulta karega |
3️⃣ Text Formulas
| Formula | Explanation |
|---|---|
| =CONCAT(A1, " ", B1) | Text jodta hai |
| =TEXTJOIN(",", TRUE, A1:A3) | Multiple text join karta hai |
| =LEFT(A1, 3) | Text ke left se 3 letter |
| =RIGHT(A1, 4) | Text ke right se 4 letter |
| =MID(A1, 2, 3) | Text ke beech ke letter |
| =LEN(A1) | Text ki length |
| =UPPER(A1) | Sab capital letters |
| =LOWER(A1) | Sab small letters |
| =PROPER(A1) | First letter capital |
| =TRIM(A1) | Extra spaces hataata hai |
4️⃣ Lookup Formulas
| Formula | Explanation |
|---|---|
| =VLOOKUP(lookup_value, table_array, col_index, FALSE) | Vertical lookup |
| =HLOOKUP(lookup_value, table_array, row_index, FALSE) | Horizontal lookup |
| =INDEX(A1:C10, 2, 3) | Row-column ke base par value |
| =MATCH("Apple", A1:A10, 0) | Value ka position number |
| =XLOOKUP("Apple", A1:A10, B1:B10) | New advanced lookup |
5️⃣ Date & Time Formulas
| Formula | Explanation |
|---|---|
| =TODAY() | Aaj ki date |
| =NOW() | Date + Time |
| =DAY(A1) | Date se din nikalta hai |
| =MONTH(A1) | Date se mahina |
| =YEAR(A1) | Date se saal |
| =DATEDIF(A1,B1,"d") | Do date ke beech difference (days) |
| =EDATE(A1, 3) | 3 months add karta hai |
6️⃣ Math & Statistical Formulas
| Formula | Explanation |
|---|---|
| =ROUND(A1,2) | Value round off karta hai |
| =SQRT(A1) | Square root |
| =POWER(A1,3) | Power (A1³) |
| =RAND() | Random number (0–1) |
| =RANDBETWEEN(1,100) | Random number 1–100 |
| =AVERAGEIF(range, criteria, average_range) | Conditional average |
| =COUNTIF(range, criteria) | Condition match count |
| =SUMIF(range, criteria, sum_range) | Condition ke hisab se sum |
7️⃣ Advanced Functions
| Formula | Explanation |
|---|---|
| =IFERROR(A1/B1, "Error") | Error ko handle karega |
| =INDIRECT("A"&B1) | Cell ko indirect reference |
| =OFFSET(A1,1,1) | Offset ke base par cell |
| =TRANSPOSE(A1:B2) | Row-column exchange |
| =UNIQUE(A1:A10) | Unique values |
| =FILTER(A1:B10, B1:B10>50) | Filter data dynamically |
| =SORT(A1:A10) | Data sort karta hai |
8️⃣ Tips & Tricks
- Ctrl + T → Table banana
- Alt + = → Auto SUM
- Ctrl + Shift + L → Filter on/off
- Ctrl + ; → Aaj ki date
- Ctrl + Shift + : → Current time
- Alt + Enter → Line break within cell
Comments
Post a Comment