Top 100 Excel Formulas with Explanation

Here�s a comprehensive list of top 100 Excel formulas categorized by their functionality, along with explanations and examples for each.

Here’s a comprehensive list of top 100 Excel formulas categorized by their functionality, along with explanations and examples for each. This list covers the most commonly used formulas in Excel for data analysis, calculations, and automation.

1. Basic Math Formulas

1. SUM : Adds up a range of numbers.

=SUM(A1:A10)

2. AVERAGE : Calculates the average of a range of numbers.

=AVERAGE(A1:A10)

3. MIN : Finds the smallest number in a range.

=MIN(A1:A10)

4. MAX : Finds the largest number in a range.

=MAX(A1:A10)?

5. COUNT : Counts the number of cells with numbers in a range.

=COUNT(A1:A10)

6. COUNTA : Counts the number of non-empty cells in a range.

=COUNTA(A1:A10)

7. COUNTBLANK : Counts the number of empty cells in a range.

=COUNTBLANK(A1:A10)

8. PRODUCT : Multiplies numbers in a range.

=PRODUCT(A1:A10)

9. SUBTOTAL : Performs calculations like SUM, AVERAGE, etc., while ignoring hidden rows.

=SUBTOTAL(9, A1:A10)  // 9 = SUM

10. ABS : Returns the absolute value of a number.

=ABS(-10)  // Returns 10

2. Logical Formulas

11. IF : Performs a logical test and returns one value if true, another if false.

=IF(A1>10, "Yes", "No")

12. AND : Returns TRUE if all conditions are true.

=AND(A1>10, B1<20)

13. OR : Returns TRUE if any condition is true.

=OR(A1>10, B1<20)

14. NOT : Reverses the logic of a condition.

=NOT(A1>10)

15. IFS : Checks multiple conditions and returns the first true value (Excel 2019+).

=IFS(A1>10, "High", A1>5, "Medium", TRUE, "Low")

16. SWITCH : Evaluates an expression and returns a value based on matching cases (Excel 2019+).

=SWITCH(A1, 1, "One", 2, "Two", "Unknown")

3. Text Formulas

17. CONCATENATE / CONCAT : Combines text from multiple cells.

=CONCATENATE(A1, " ", B1)

18. TEXTJOIN : Combines text with a delimiter (Excel 2019+).

=TEXTJOIN(", ", TRUE, A1:A10)

19. LEFT : Extracts a specified number of characters from the left.

=LEFT(A1, 5)

20. RIGHT : Extracts a specified number of characters from the right.

=RIGHT(A1, 5)

21. MID : Extracts a substring from the middle of a text string.

=MID(A1, 2, 5)

22. LEN : Returns the length of a text string.

=LEN(A1)

23. TRIM : Removes extra spaces from text.

=TRIM(A1)

24. UPPER : Converts text to uppercase.

=UPPER(A1)

25. LOWER : Converts text to lowercase.

=LOWER(A1)

26. PROPER : Converts text to proper case (capitalizes each word).

=PROPER(A1)?

27. FIND : Finds the position of a substring within a text string (case-sensitive).

=FIND("apple", A1)

28. SEARCH : Finds the position of a substring within a text string (not case-sensitive).

=SEARCH("apple", A1)

29. REPLACE : Replaces part of a text string with another string.

=REPLACE(A1, 1, 5, "Hello")

30. SUBSTITUTE : Replaces specific text in a string.

=SUBSTITUTE(A1, "old", "new")

4. Lookup and Reference Formulas

31. VLOOKUP : Looks up a value in the first column of a table and returns a value in the same row from a specified column.

=VLOOKUP(A1, B1:C10, 2, FALSE

32. HLOOKUP : Similar to VLOOKUP but searches horizontally.

=HLOOKUP(A1, B1:C10, 2, FALSE)

33. INDEX : Returns a value from a specific position in a range.

=INDEX(A1:C10, 2, 3)

34. MATCH : Finds the position of a value in a range.

=MATCH(A1, B1:B10, 0)

35. XLOOKUP : A more flexible replacement for VLOOKUP and HLOOKUP (Excel 365+).

=XLOOKUP(A1, B1:B10, C1:C10)

7. Statistical Formulas

61. STDEV : Calculates the standard deviation of a sample.

=STDEV(A1:A10)

62. STDEVP : Calculates the standard deviation of an entire population.

=STDEVP(A1:A10)

63. VAR : Calculates the variance of a sample.

=VAR(A1:A10)

64. VARP : Calculates the variance of an entire population.

=VARP(A1:A10)

65. CORREL : Calculates the correlation coefficient between two data sets.

=CORREL(A1:A10, B1:B10)

66. RANK : Returns the rank of a number in a list.

=RANK(A1, A1:A10)
10. Advanced Formulas 81. SUMPRODUCT : Multiplies corresponding elements in arrays and returns the sum. =SUMPRODUCT(A1:A10, B1:B10) 82. CHOOSE : Returns a value from a list based on a position. =CHOOSE(2, "Red", "Green", "Blue") 83. OFFSET : Returns a reference to a range that is offset from a starting point. =OFFSET(A1, 2, 3) 84. TRANSPOSE : Transposes rows and columns in a range. =TRANSPOSE(A1:C10) 85. UNIQUE : Returns unique values from a range (Excel 365+). =UNIQUE(A1:A10) 86. FILTER : Filters a range based on cr

Related Articles Nebius Taps $20B Microsoft, $3B Meta AI Infrastructure Deals to Turbocharge Global GPU Cloud Expansion (20-11-2025): Today Tech News: Latest AI, Mobile Launches, Cloud & Tech Updates (19-11-2025): Latest Updates on AI, Smartphones, Big Tech Regulations & Market Trends Navigating Tax Debt: Resources for Becoming Debt Free The Future of Medical Communications: The Benefits of Epic Fax Integration Solutions AI Breakthroughs, Smartphone Launches, and Global Digital Innovations Note : This article is only for students, for the purpose of enhancing their knowledge. This article is collected from several websites, the copyrights of this article also belong to those websites like : Newscientist, Techgig, simplilearn, scitechdaily, TechCrunch, TheVerge etc,.

Quick Links Interview Questions S/W Technology Civil, Mech ECE, EEE More Technologies MCQ (or) Quiz S/W Technology Civil, Mech ECE, EEE Aeronautical Example Programs C Language, C++, Java, PHP, Python Articles Marketing Management Tech Updates Tech Articles Tools Color Picker Interest Calculator EMI Calculator Vehicle EMI Calculator Compailers HTML C & CPP PHP Python