Microsoft Excel आज हर student, professional, और business user के लिए essential tool है। Excel में formulas और functions जानना productivity बढ़ाने का सबसे आसान तरीका है।
इस ब्लॉग में हम step-by-step tutorials के साथ top Excel formulas सीखेंगे, जो आपको data analysis और reporting में मदद करेंगे।
🔹 1. SUM Formula
Purpose: Add multiple numbers quickly
Syntax: =SUM(A1:A10)
Example:
- If A1=10, A2=20, A3=30
- Formula:
=SUM(A1:A3)→ Result: 60
Tip: SUM can also skip empty cells automatically.
🔹 2. AVERAGE Formula
Purpose: Find average of numbers
Syntax: =AVERAGE(A1:A10)
Example:
- If marks in A1:A5 are 80, 90, 85, 70, 95
- Formula:
=AVERAGE(A1:A5)→ Result: 84
🔹 3. IF Formula
Purpose: Perform conditional checks
Syntax: =IF(condition, value_if_true, value_if_false)
Example:
=IF(B1>=50, "Pass", "Fail")- If B1=60 → Result: Pass
🔹 4. VLOOKUP Formula
Purpose: Lookup value in a table
Syntax: =VLOOKUP(lookup_value, table_array, col_index, [range_lookup])
Example:
- Lookup student marks from a table by ID
- Formula:
=VLOOKUP(101, A2:C10, 3, FALSE)
Tip: Always use FALSE for exact match.
🔹 5. HLOOKUP Formula
Purpose: Horizontal lookup in a row
Syntax: =HLOOKUP(lookup_value, table_array, row_index, [range_lookup])
Example:
- Lookup product price from a row of product names
🔹 6. COUNTIF Formula
Purpose: Count cells based on criteria
Syntax: =COUNTIF(range, criteria)
Example:
=COUNTIF(A1:A10, ">50")→ Counts all numbers >50
🔹 7. CONCATENATE / CONCAT Formula
Purpose: Combine text from multiple cells
Syntax:
=CONCATENATE(A1, " ", B1)=CONCAT(A1, " ", B1)(Excel 2016+)
Example:
- First Name in A1, Last Name in B1 → Full Name in C1
🔹 8. TRIM Formula
Purpose: Remove extra spaces from text
Syntax: =TRIM(A1)
Example:
- A1 = ” Hello World ” →
=TRIM(A1)→ “Hello World”
🔹 9. TODAY & NOW Formula
Purpose: Get current date and time
Syntax:
=TODAY()→ Returns current date=NOW()→ Returns current date & time
🔹 10. PMT Formula (Finance)
Purpose: Calculate loan EMIs
Syntax: =PMT(rate, nper, pv)
Example:
- Loan of 1,00,000, 5% monthly interest, 12 months →
=PMT(5%,12,100000)
🔹 Bonus Tips for High Productivity
- Use Named Ranges for better readability
- Learn Keyboard Shortcuts for faster formula entry
- Use Array Formulas for advanced calculations
- Combine IF + AND / OR for complex conditions
- Always lock cells with $ when copying formulas
✅ Conclusion
Excel formulas are powerful tools for data management, analysis, and reporting. By mastering these top formulas like SUM, IF, VLOOKUP, COUNTIF, you can save hours of work and improve productivity.
💡 Pro Tip: Practice these formulas daily on your own data to become confident and efficient in Excel.
