99+ Excel Formulas: The Ultimate Guide

Mastering Excel Formulas: The Ultimate Guide

When it comes to Excel, formulas are the backbone of every spreadsheet. They enable you to perform everything from simple mathematical operations to complex data analysis. Below is a comprehensive list of 99 Excel formulas that will supercharge your productivity and efficiency.

1. SUM Formula

• Example: =SUM(A1:A5)
• Explanation: This formula adds up the values in cells A1 to A5.

2. AVERAGE Formula

• Example: =AVERAGE(B1:B10)
• Explanation: This calculates the average of the values in cells B1 to B10.

3. COUNT Formula

• Example: =COUNT(C1:C8)
• Explanation: This counts the number of cells with numerical values in the range C1 to C8.

4. MAX Formula

• Example: =MAX(D1:D6)
• Explanation: Finds the maximum value in the range D1 to D6.

5. MIN Formula

• Example: =MIN(E1:E4)
• Explanation: Finds the minimum value in the range E1 to E4.

6. IF Formula

• Example: =IF(F1>10, "Pass", "Fail")
• Explanation: Checks if the value in F1 is greater than 10. If true, returns "Pass"; otherwise, returns "Fail."

7. VLOOKUP Formula

• Example: =VLOOKUP(G1, A1:B10, 2, FALSE)
• Explanation: Looks for the value in G1 in the first column of A1

8. CONCATENATE Formula

• Example: =CONCATENATE("Hello", " World")
• Explanation: Combines "Hello" and "World" into "Hello World."

9. TODAY Formula

• Example: =TODAY()
• Explanation: Returns the current date.

10. LEN Formula

• Example: =LEN(H1)
• Explanation: Counts the number of characters in the text of cell H1.

11. TRIM Formula

• Example: =TRIM(I1)
• Explanation: Removes excess spaces from the text in I1.

12. LEFT Formula

• Example: =LEFT(J1, 5)
• Explanation: Extracts the leftmost 5 characters from the text in J1.

13. RIGHT Formula

• Example: =RIGHT(K1, 3)
• Explanation: Extracts the rightmost 3 characters from the text in K1.

14. MID Formula

• Example: =MID(L1, 3, 5)
• Explanation: Extracts 5 characters starting from the 3rd character of the text in L1.

15. UPPER Formula

• Example: =UPPER(M1)
• Explanation: Converts text in M1 to uppercase.

16. LOWER Formula

• Example: =LOWER(N1)
• Explanation: Converts text in N1 to lowercase.

17. PROPER Formula

• Example: =PROPER(O1)
• Explanation: Capitalizes the first letter of each word in O1.

18. ROUND Formula

• Example: =ROUND(P1, 2)
• Explanation: Rounds the value in P1 to 2 decimal places.

19. COUNTIF Formula

• Example: =COUNTIF(Q1:Q10, ">50")
• Explanation: Counts the cells greater than 50 in Q1 to Q10.

20. SUMIF Formula

• Example: =SUMIF(R1:R10, "Apples", S1:S10)
• Explanation: Sums the values in S1

21. AVERAGEIF Formula

• Example: =AVERAGEIF(T1:T10, "Red", U1:U10)
• Explanation: Averages the values in U1

22. IFERROR Formula

• Example: =IFERROR(V1/W1, "Error")
• Explanation: Returns "Error" if V1/W1 results in an error.

23. INDEX Formula

• Example: =INDEX(X1:Y10, 3, 2)
• Explanation: Returns the value in the 3rd row and 2nd column of X1

24. MATCH Formula

• Example: =MATCH(Z1, AA1:AA10, 0)
• Explanation: Returns the position of Z1 in AA1

25. INDIRECT Formula

• Example: =INDIRECT("A"&AB1)
• Explanation: Creates a reference to the cell specified in AB1.

26. COUNTIFS Formula

• Example: =COUNTIFS(AC1:AC10, ">50", AD1:AD10, "<100")
• Explanation: Counts the cells meeting criteria in multiple ranges.

27. SUMIFS Formula

• Example: =SUMIFS(AE1:AE10, AF1:AF10, "Apples", AG1:AG10, ">50")
• Explanation: Sums the values in AE1

28. AVERAGEIFS Formula

• Example: =AVERAGEIFS(AH1:AH10, AI1:AI10, "Red", AJ1:AJ10, "<>0")
• Explanation: Averages AH1

29. CONCATENATE (With Cell References)

• Example: =CONCATENATE(AK1, " ", AK2)
• Explanation: Combines the text in AK1 and AK2 with a space in between.

30. TEXT Formula

• Example: =TEXT(AL1, "dd-mmm-yyyy")
• Explanation: Converts the date in AL1 to a specific format.

31. NETWORKDAYS Formula

• Example: =NETWORKDAYS(AM1, AM10)
• Explanation: Returns the number of working days between AM1 and AM10.

32. LEFT (Variable Number of Characters)

• Example: =LEFT(AN1, SEARCH(" ", AN1)-1)
• Explanation: Extracts text from AN1 up to the first space.

33. RIGHT (Variable Number of Characters)

• Example: =RIGHT(AO1, LEN(AO1)-SEARCH(" ", AO1))
• Explanation: Extracts text from the first space to the end of AO1.

34. MID (Variable Number of Characters)

• Example: =MID(AP1, SEARCH(" ", AP1)+1, LEN(AP1)-SEARCH(" ", AP1))
• Explanation: Extracts text after the first space from AP1.

35. RAND Formula

• Example: =RAND()
• Explanation: Generates a random number between 0 and 1.

36. RANDBETWEEN Formula

• Example: =RANDBETWEEN(1, 100)
• Explanation: Generates a random number between 1 and 100.

37. SUBTOTAL Formula

• Example: =SUBTOTAL(1, AQ1:AQ10)
• Explanation: Returns a subtotal for the visible cells in AQ1

38. TRANSPOSE Formula

• Example: =TRANSPOSE(AR1:AT1)
• Explanation: Transposes a row of data into a column, or vice versa.

39. TODAY Formula (With Custom Formatting)

• Example: =TEXT(TODAY(), "dd-mmm-yyyy")
• Explanation: Returns the current date in a specific format.

40. NOW Formula

• Example: =NOW()
• Explanation: Returns the current date and time.

41. DATE Formula

• Example: =DATE(2023, 12, 31)
• Explanation: Creates a date for December 31, 2023.

42. TIME Formula

• Example: =TIME(9, 30, 0)
• Explanation: Creates a time of 9:30 AM.

43. WEEKDAY Formula

• Example: =WEEKDAY(AS1)
• Explanation: Returns the day of the week for the date in AS1.

44. DAY Formula

• Example: =DAY(AT1)
• Explanation: Extracts the day from the date in AT1.

45. MONTH Formula

• Example: =MONTH(AU1)
• Explanation: Extracts the month from the date in AU1.

46. YEAR Formula

• Example: =YEAR(AV1)
• Explanation: Extracts the year from the date in AV1.

47. HOUR Formula

• Example: =HOUR(AW1)
• Explanation: Extracts the hour from the time in AW1.

48. MINUTE Formula

• Example: =MINUTE(AX1)
• Explanation: Extracts the minute from the time in AX1.

49. SECOND Formula

• Example: =SECOND(AY1)
• Explanation: Extracts the second from the time in AY1.

50. TEXTJOIN Formula

• Example: =TEXTJOIN(", ", TRUE, AZ1:BA1)
• Explanation: Combines the text in AZ1

51. CHAR Formula

• Example: =CHAR(65)
• Explanation: Returns the character for ASCII code 65 (A).

52. CODE Formula

• Example: =CODE("A")
• Explanation: Returns the ASCII code for the character "A."

53. FIND Formula

• Example: =FIND("B", BB1)
• Explanation: Returns the position of "B" in the text of BB1.

54. SEARCH Formula

• Example: =SEARCH("B", BC1)
• Explanation: Finds the position of "B" in BC1, case-insensitive.

55. REPLACE Formula

• Example: =REPLACE(BD1, 1, 3, "New")
• Explanation: Replaces the first 3 characters in BD1 with "New."

56. SUBSTITUTE Formula

• Example: =SUBSTITUTE(BE1, "Old", "New")
• Explanation: Replaces occurrences of "Old" with "New" in BE1.

57. EXACT Formula

• Example: =EXACT(BF1, BF2)
• Explanation: Checks if BF1 and BF2 are exactly the same.

58. REPT Formula

• Example: =REPT("X", 5)
• Explanation: Repeats "X" five times.

59. LOWER (with Cell References)

• Example: =LOWER(BG1)
• Explanation: Converts the text in BG1 to lowercase.

60. UPPER (with Cell References)

• Example: =UPPER(BH1)
• Explanation: Converts the text in BH1 to uppercase.

61. PROPER (with Cell References)

• Example: =PROPER(BI1)
• Explanation: Capitalizes the first letter of each word in BI1.

62. AND Formula

• Example: =AND(BJ1>10, BK1<20)
• Explanation: Returns TRUE if both conditions are met.

63. OR Formula

• Example: =OR(BL1=10, BM1=20)
• Explanation: Returns TRUE if either condition is met.

64. NOT Formula

• Example: =NOT(BN1=10)
• Explanation: Returns TRUE if BN1 is not equal to 10.

65. XOR Formula

• Example: =XOR(BO1=10, BP1=20)
• Explanation: Returns TRUE if exactly one of the conditions is TRUE.

66. ISNUMBER Formula

• Example: =ISNUMBER(BQ1)
• Explanation: Returns TRUE if BQ1 contains a number.

67. ISBLANK Formula

• Example: =ISBLANK(BR1)
• Explanation: Returns TRUE if BR1 is blank.

68. ISERROR Formula

• Example: =ISERROR(BS1)
• Explanation: Returns TRUE if BS1 contains an error.

69. ISEVEN Formula

• Example: =ISEVEN(BT1)
• Explanation: Returns TRUE if the value in BT1 is an even number.

70. ISODD Formula

• Example: =ISODD(BU1)
• Explanation: Returns TRUE if the value in BU1 is an odd number.

71. LARGE Formula

• Example: =LARGE(BV1:BW10, 2)
• Explanation: Returns the 2nd largest value in the range BV1

72. SMALL Formula

• Example: =SMALL(BX1:BY10, 3)
• Explanation: Returns the 3rd smallest value in the range BX1

73. MEDIAN Formula

• Example: =MEDIAN(BZ1:CA10)
• Explanation: Returns the median value from BZ1

74. MODE Formula

• Example: =MODE(CB1:CC10)
• Explanation: Returns the most frequent value in the range CB1

75. STDEV.P Formula

• Example: =STDEV.P(CD1:CE10)
• Explanation: Calculates the standard deviation for an entire population.

76. STDEV.S Formula

• Example: =STDEV.S(CF1:CG10)
• Explanation: Calculates the standard deviation for a sample.

77. VAR.P Formula

• Example: =VAR.P(CH1:CI10)
• Explanation: Calculates the variance for an entire population.

78. VAR.S Formula

• Example: =VAR.S(CJ1:CK10)
• Explanation: Calculates the variance for a sample.

79. PERCENTILE Formula

• Example: =PERCENTILE(CL1:CM10, 0.75)
• Explanation: Returns the 75th percentile from the range CL1

80. QUARTILE Formula

• Example: =QUARTILE(CN1:CO10, 2)
• Explanation: Returns the median (2nd quartile) value in the range CN1

81. RANK Formula

• Example: =RANK(CP1, CQ1:CR10)
• Explanation: Returns the rank of CP1 within the range CQ1

82. CEILING Formula

• Example: =CEILING(CS1, 5)
• Explanation: Rounds CS1 up to the nearest multiple of 5.

83. FLOOR Formula

• Example: =FLOOR(CT1, 5)
• Explanation: Rounds CT1 down to the nearest multiple of 5.

84. ROUNDUP Formula

• Example: =ROUNDUP(CU1, 2)
• Explanation: Rounds the value in CU1 up to two decimal places.

85. ROUNDDOWN Formula

• Example: =ROUNDDOWN(CV1, 2)
• Explanation: Rounds the value in CV1 down to two decimal places.

86. POWER Formula

• Example: =POWER(CW1, 3)
• Explanation: Raises the value in CW1 to the power of 3.

87. SQRT Formula

• Example: =SQRT(CX1)
• Explanation: Returns the square root of CX1.

88. EXP Formula

• Example: =EXP(CY1)
• Explanation: Returns e raised to the power of CY1.

89. LN Formula

• Example: =LN(CZ1)
• Explanation: Returns the natural logarithm of CZ1.

90. LOG Formula

• Example: =LOG(DA1, 10)
• Explanation: Returns the base 10 logarithm of DA1.

91. ABS Formula

• Example: =ABS(DB1)
• Explanation: Returns the absolute value of DB1.

92. PI Formula

• Example: =PI()
• Explanation: Returns the value of π.

93. DEGREES Formula

• Example: =DEGREES(DC1)
• Explanation: Converts radians to degrees.

94. RADIANS Formula

• Example: =RADIANS(DD1)
• Explanation: Converts degrees to radians.

95. SIN Formula

• Example: =SIN(DE1)
• Explanation: Returns the sine of DE1 (in radians).

96. COS Formula

• Example: =COS(DF1)
• Explanation: Returns the cosine of DF1 (in radians).

97. TAN Formula

• Example: =TAN(DG1)
• Explanation: Returns the tangent of DG1 (in radians).

98. ASIN Formula

• Example: =ASIN(DH1)
• Explanation: Returns the arcsine of DH1 (in radians).

99. ACOS Formula

• Example: =ACOS(DI1)
• Explanation: Returns the arccosine of DI1 (in radians).

These 99 Excel formulas will empower you to tackle a wide range of tasks, from simple calculations to sophisticated data analysis, ensuring you make the most of Excel’s capabilities.