ALL IMPORTANT EXCEL FUNCTIONS WITH PRACTICAL EXAMPLES & DESCRIPTIONS
1. SUM()
Adds a range of numbers.
Example: =SUM(A1:A5)
Practical Use: Total marks, total sales, total expenses.
2. AVERAGE()
Calculates the average.
Example: =AVERAGE(B1:B10)
Practical Use: Average salary, average attendance.
3. COUNT()
Counts numeric cells.
Example: =COUNT(A1:A10)
Practical Use: Count how many students have marks.
4. COUNTA()
Counts non-empty cells.
Example: =COUNTA(A1:A10)
Practical Use: Count entries in a column.
5. COUNTIF()
Counts cells meeting a condition.
Example: =COUNTIF(A1:A20,"Male")
Practical Use: Count male employees.
6. COUNTIFS()
Counts with multiple conditions.
Example: =COUNTIFS(A:A,"Male",B:B,"IT")
Practical Use: Male employees in IT.
7. MAX()
Finds highest value.
Example: =MAX(C1:C20)
Practical Use: Highest salary.
8. MIN()
Finds lowest value.
Example: =MIN(C1:C20)
Practical Use: Lowest marks.
9. IF()
Checks condition.
Example: =IF(A1>50,"Pass","Fail")
Practical Use: Result sheet.
10. AND()
Checks if all conditions are TRUE.
Example: =AND(A1>50,B1="Present")
Practical Use: Check attendance + marks.
11. OR()
Checks if any condition TRUE.
Example: =OR(A1="HR",A1="IT")
Practical Use: Check department.
12. IFERROR()
Handles errors.
Example: =IFERROR(A1/B1,"Invalid")
Practical Use: Avoid #DIV/0 errors.
13. VLOOKUP()
Searches vertically.
Example: =VLOOKUP(A2,Sheet2!A:D,3,FALSE)
Practical Use: Retrieve employee details.
14. HLOOKUP()
Search horizontally.
Example: =HLOOKUP(A1,A1:F2,2,FALSE)
Practical Use: Read header-based data.
15. XLOOKUP()
Modern lookup.
Example: =XLOOKUP(A2,A5:A50,B5:B50)
Practical Use: Fetch product price.
16. INDEX + MATCH
Flexible lookup.
Example: =INDEX(B1:B20, MATCH(A1,A1:A20,0))
Practical Use: Advanced table lookup.
17. LEFT()
Extract left characters.
Example: =LEFT("ODISHA",3)
Practical Use: Extract state code.
18. RIGHT()
Extract right characters.
Example: =RIGHT("12345",2)
Practical Use: Last digits of ID.
19. MID()
Extract from middle.
Example: =MID("ABC123",4,3)
Practical Use: Extract numeric part.
20. LEN()
Count characters.
Example: =LEN(A1)
Practical Use: Check mobile number length.
21. TRIM()
Remove extra spaces.
Example: =TRIM(A1)
Practical Use: Clean imported data.
22. UPPER()
Convert to uppercase.
Example: =UPPER("odisha")
Practical Use: Standard formatting.
23. LOWER()
Convert to lowercase.
Example: =LOWER("ODISHA")
24. PROPER()
Capitalizes first letter.
Example: =PROPER("john doe")
25. CONCAT()
Combine text.
Example: =CONCAT(A1," ",B1)
Practical Use: Full name.
26. TEXTJOIN()
Join text with delimiter.
Example: =TEXTJOIN("-",TRUE,A1:C1)
Practical Use: Generate codes.
27. SUBSTITUTE()
Replace text.
Example: =SUBSTITUTE("A-B-C","-","/")
Practical Use: Fix formatting.
28. FIND()
Find position (case-sensitive).
Example: =FIND("A","ODIA")
Practical Use: Find character location.
29. SEARCH()
Find position (not case-sensitive).
Example: =SEARCH("a","Odisha")
30. TODAY()
Current date.
Example: =TODAY()
Practical Use: Attendance sheet.
31. NOW()
Current date+time.
Example: =NOW()
32. DATE()
Create a date.
Example: =DATE(2025,12,31)
33. DAY()
Extract day.
Example: =DAY("12/05/2024")
34. MONTH()
Extract month.
Example: =MONTH(A1)
35. YEAR()
Extract year.
Example: =YEAR(A1)
36. DATEDIF()
Date difference.
Example: =DATEDIF(A1,B1,"D")
Practical Use: Count days of training.
37. NETWORKDAYS()
Working days between dates.
Example: =NETWORKDAYS(A1,B1)
38. TIME()
Create time.
Example: =TIME(10,30,00)
39. PMT()
Loan EMI.
Example: =PMT(10%/12,60,500000)
40. ISBLANK()
Check empty cell.
Example: =ISBLANK(A1)
41. ISNUMBER()
Check number.
Example: =ISNUMBER(A1)
42. ISTEXT()
Check text.
Example: =ISTEXT(A1)
43. CONVERT()
Convert units.
Example: =CONVERT(100,"km","m")