USEFUL MICROSOFT EXCEL FORMULAS
Microsoft Excel offers hundreds of built-in formulas that help you perform calculations, analyze data, clean information, manage dates, and automate everyday tasks.
Here are 100 useful Excel formulas everyone should know.
1. Basic Mathematical Formulas
1. SUM() – Adds numbers.
Example: =SUM(A1:A10)
2. AVERAGE() – Calculates the average.
Example: =AVERAGE(A1:A10)
3. MIN() – Returns the smallest value.
Example: =MIN(A1:A10)
4. MAX() – Returns the largest value.
Example: =MAX(A1:A10)
5. COUNT() – Counts cells containing numbers.
Example: =COUNT(A1:A10)
6. COUNTA() – Counts non-empty cells.
Example: =COUNTA(A1:A10)
7. COUNTBLANK() – Counts empty cells.
Example: =COUNTBLANK(A1:A10)
8. PRODUCT() – Multiplies numbers.
Example: =PRODUCT(A1:A5)
9. ABS() – Returns the absolute value.
Example: =ABS(-25)
10. ROUND() – Rounds a number to specified decimal places.
Example: =ROUND(12.567,2)
2. Logical Formulas
11. IF() – Checks a condition and returns different results.
Example: =IF(A2>=40,"Pass","Fail")
12. IFS() – Checks multiple conditions.
Example: =IFS(A2>=90,"A",A2>=75,"B",TRUE,"C")
13. AND() – Returns TRUE if all conditions are true.
Example: =AND(A2>50,B2<100)
14. OR() – Returns TRUE if at least one condition is true.
Example: =OR(A2>50,B2<100)
15. NOT() – Reverses a logical result.
Example: =NOT(A2>50)
16. IFERROR() – Returns a custom result if a formula produces an error.
Example: =IFERROR(A2/B2,0)
17. IFNA() – Handles #N/A errors.
Example: =IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"Not Found")
18. SWITCH() – Compares a value against multiple options.
Example: =SWITCH(A2,1,"One",2,"Two","Other")
19. XOR() – Returns TRUE when an odd number of conditions are true.
Example: =XOR(A2>10,B2>20)
20. TRUE() – Returns the logical value TRUE.
Example: =TRUE()
3. Text Formulas
21. LEFT() – Extracts characters from the left side of text.
Example: =LEFT(A2,5)
22. RIGHT() – Extracts characters from the right side of text.
Example: =RIGHT(A2,5)
23. MID() – Extracts characters from the middle of text.
Example: =MID(A2,2,4)
24. LEN() – Counts the number of characters.
Example: =LEN(A2)
25. TRIM() – Removes extra spaces from text.
Example: =TRIM(A2)
26. UPPER() – Converts text into uppercase.
Example: =UPPER(A2)
27. LOWER() – Converts text into lowercase.
Example: =LOWER(A2)
28. PROPER() – Capitalizes the first letter of each word.
Example: =PROPER(A2)
29. CONCAT() – Combines text from multiple cells.
Example: =CONCAT(A2,B2)
30. TEXTJOIN() – Combines text using a specified delimiter.
Example: =TEXTJOIN(", ",TRUE,A2:A10)
4. Lookup and Reference Formulas
31. VLOOKUP() – Searches vertically in a table.
Example: =VLOOKUP(E2,A2:C10,2,FALSE)
32. HLOOKUP() – Searches horizontally in a table.
Example: =HLOOKUP(B1,A1:F3,2,FALSE)
33. XLOOKUP() – Searches for a value and returns a matching result.
Example: =XLOOKUP(E2,A2:A10,B2:B10,"Not Found")
34. INDEX() – Returns a value from a specified position.
Example: =INDEX(A2:C10,2,3)
35. MATCH() – Finds the position of a value in a range.
Example: =MATCH(E2,A2:A10,0)
36. XMATCH() – Finds the relative position of a value.
Example: =XMATCH(E2,A2:A10)
37. CHOOSE() – Returns a value based on its position.
Example: =CHOOSE(2,"Red","Blue","Green")
38. OFFSET() – Returns a reference shifted from a starting cell.
Example: =OFFSET(A1,2,1)
39. INDIRECT() – Converts text into a cell reference.
Example: =INDIRECT("A1")
40. TRANSPOSE() – Converts rows into columns and vice versa.
Example: =TRANSPOSE(A1:C3)
Double Tap ❤️ For More