99 Formulas In Excel for Ultimate Productivity

Introduction

In the world of spreadsheets and data analysis, Microsoft Excel stands as an unrivaled powerhouse, empowering users to organize, analyze, and visualize data with unparalleled efficiency. At the heart of Excel’s prowess are its formulas, the secret sauce that transforms a simple spreadsheet into a dynamic tool for complex calculations and data manipulation. In this comprehensive guide, we unveil 99 essential Excel formulas, serving as your passport to unlock the full potential of this versatile software. Whether you’re a seasoned Excel user or just starting your journey, these formulas will elevate your skills, streamline your workflows, and propel your productivity to new heights. Join us on this exploration of Excel wizardry as we dive into the essential formulas that can revolutionize the way you work with data.

99 Formulas

1. SUM formula:

Example: =SUM(A1:A5)

This formula adds up the values in cells A1 to A5.

2. AVERAGE formula:

Example: =AVERAGE(B1:B10)

This formula calculates the average of the values in cells B1 to B10.

3. COUNT formula:

Example: =COUNT(C1:C8)

This formula counts the number of cells that contain numerical values in the range C1 to C8.

4. MAX formula:

Example: =MAX(D1:D6)

This formula finds the maximum value in the range D1 to D6.

5. MIN formula:

Example: =MIN(E1:E4)

This formula finds the minimum value in the range E1 to E4.

6. IF formula:

Example: =IF(F1>10, “Pass”, “Fail”)

This formula checks if the value in cell F1 is greater than 10. If it is, it returns “Pass”; otherwise, it returns “Fail”.

7. VLOOKUP formula:

Example: =VLOOKUP(G1, A1:B10, 2, FALSE)

This formula looks for the value in cell G1 in the first column of the range A1 to B10 and returns the corresponding value from the second column.

8. CONCATENATE formula:

Example: =CONCATENATE(“Hello “, “World”)

This formula combines the text “Hello” and “World” to create the string “Hello World”.

9. TODAY formula:

Example: =TODAY()

This formula returns the current date.

10. LEN formula:

Example: =LEN(H1)

This formula calculates the number of characters in the text contained in cell H1.

11. TRIM formula:

Example: =TRIM(I1)

This formula removes excess spaces from the text in cell I1.

12. LEFT formula:

Example: =LEFT(J1, 5)

This formula extracts the leftmost 5 characters from the text in cell J1.

13. RIGHT formula:

Example: =RIGHT(K1, 3)

This formula extracts the rightmost 3 characters from the text in cell K1.

14. MID formula:

Example: =MID(L1, 3, 5)

This formula extracts 5 characters starting from the 3rd character of the text in cell L1.

15. UPPER formula:

Example: =UPPER(M1)

This formula converts the text in cell M1 to uppercase.

16. LOWER formula:

Example: =LOWER(N1)

This formula converts the text in cell N1 to lowercase.

17. PROPER formula:

Example: =PROPER(O1)

This formula capitalizes the first letter of each word in the text in cell O1.

18. ROUND formula:

Example: =ROUND(P1, 2)

This formula rounds the value in cell P1 to 2 decimal places.

19. COUNTIF formula:

Example: =COUNTIF(Q1:Q10, “>50”)

This formula counts the number of cells in the range Q1 to Q10 that are greater than 50.

20. SUMIF formula:

Example: =SUMIF(R1:R10, “Apples”, S1:S10)

This formula sums the values in the range S1 to S10 where the corresponding cell in the range R1 to R10 is “Apples”.

21. AVERAGEIF formula:

Example: =AVERAGEIF(T1:T10, “Red”, U

1:U10)

This formula calculates the average of the values in the range U1 to U10 where the corresponding cell in the range T1 to T10 is “Red”.

22. IFERROR formula:

Example: =IFERROR(V1/W1, “Error”)

This formula divides the value in cell V1 by the value in cell W1 and returns “Error” if an error occurs.

23. INDEX formula:

Example: =INDEX(X1:Y10, 3, 2)

This formula returns the value in the 3rd row and 2nd column of the range X1 to Y10.

24. MATCH formula:

Example: =MATCH(Z1, AA1:AA10, 0)

This formula searches for the value in cell Z1 in the range AA1 to AA10 and returns its position.

25. INDIRECT formula:

Example: =INDIRECT(“A”&AB1)

This formula creates a reference to a cell based on the value in cell AB1.

26. COUNTIFS formula:

Example: =COUNTIFS(AC1:AC10, “>50”, AD1:AD10, “<100”)

This formula counts the number of cells that meet multiple criteria in the ranges AC1 to AC10 and AD1 to AD10.

27. SUMIFS formula:

Example: =SUMIFS(AE1:AE10, AF1:AF10, “Apples”, AG1:AG10, “>50”)

This formula sums the values in the range AE1 to AE10 where the corresponding cells in the ranges AF1 to AF10 and AG1 to AG10 meet specific criteria.

28. AVERAGEIFS formula:

Example: =AVERAGEIFS(AH1:AH10, AI1:AI10, “Red”, AJ1:AJ10, “<>0”)

This formula calculates the average of the values in the range AH1 to AH10 where the corresponding cells in the ranges AI1 to AI10 and AJ1 to AJ10 meet specific criteria.

29. CONCATENATE formula (with cell references):

Example: =CONCATENATE(AK1, ” “, AK2)

This formula combines the text in cell AK1, a space, and the text in cell AK2.

30. TEXT formula:

Example: =TEXT(AL1, “dd-mmm-yyyy”)

This formula converts the date in cell AL1 to a specific text format.

31. NETWORKDAYS formula:

Example: =NETWORKDAYS(AM1, AM10)

This formula calculates the number of working days between the dates in cells AM1 and AM10, excluding weekends.

32. LEFT formula (extracting variable number of characters):

Example: =LEFT(AN1, SEARCH(” “, AN1)-1)

This formula extracts the characters from the beginning of the text in cell AN1 until the first space.

33. RIGHT formula (extracting variable number of characters):

Example: =RIGHT(AO1, LEN(AO1)-SEARCH(” “, AO1))

This formula extracts the characters from the first space to the end of the text in cell AO1.

34. MID formula (extracting variable number of characters):

Example: =MID(AP1, SEARCH(” “, AP1)+1, LEN(AP1)-SEARCH(” “, AP1))

This formula extracts the characters after the first space until the end of the text in cell AP1.

35. RAND formula:

Example: =RAND()

This formula generates a random decimal number between 0 and 1.

36. RANDBETWEEN formula:

Example: =RANDBETWEEN(1, 100)

This formula generates a random whole number between 1 and 100.

37. SUBTOTAL formula:

Example: =SUBTOTAL(1, AQ1:AQ10)

This formula calculates the sum of the visible cells in the range AQ1 to AQ10, ignoring any hidden rows.

38. TRANSPOSE formula:

Example: =TRANSPOSE(AR1:AT1)

This formula transposes the values in the range AR1 to AT1, changing them from a row to a column or vice versa.

39. TODAY formula (with custom formatting):

Example: =TEXT(TODAY(), “dd-mmm-yyyy”)

This formula returns the current date in a specific text format.

40. NOW formula:

Example: =NOW()

This formula returns the current date and time.

41. DATE formula:

Example: =DATE(2023, 12, 31)

This formula creates a date based on the specified year (2023), month (12), and day (31).

42. TIME formula:

Example: =TIME(9, 30, 0)

This formula creates a time based on the specified hour (9), minute (30), and second (0).

43. WEEKDAY formula:

Example: =WEEKDAY(AS1)

This formula returns the day of the week corresponding to the date in cell AS1.

44. EOMONTH formula:

Example: =EOMONTH(AT1, 3)

This formula returns the last day of the month, 3 months after the date in cell AT1.

45. DATEDIF formula:

Example: =DATEDIF(AU1, AU10, “y”)

This formula calculates the number of complete years between the dates in cells AU1 and AU10.

46. PMT formula:

Example: =PMT(0.05/12, 12*5, -20000)

This formula calculates the monthly payment for a loan with an annual interest rate of 5%, 12 monthly payments per year, and a principal amount of $20,000.

47. FV formula:

Example: =FV(0.1, 10, -100, -500)

This formula calculates the future value of an investment with an annual interest rate of 10%, 10 periods, a regular payment of $100, and an initial principal of $500.

48. PV formula:

Example: =PV(0.08, 5, -100, 0, 1)

This formula calculates the present value of an investment with an annual discount rate of 8%, 5 periods, a regular payment of $100, a future value of $0, and payments made at the end of each period.

49. NPV formula:

Example: =NPV(0.1, A1:A5) + A1

This formula calculates the net present value of cash flows in cells A1 to A5, with a discount rate of 10%, and adds the initial investment in cell A1.

50. IRR formula:

Example: =IRR(A1:A5)

This formula calculates the internal rate of return for a series of cash flows in cells A1 to A5.

51. CUMIPMT formula:

Example: =CUMIPMT(0.05/12, 12*5, -20000, 1, 12*3)

This formula calculates the cumulative interest paid on a loan with an annual interest rate of 5%, 12 monthly payments per year, a principal amount of $20,000, and payments made from the first to the 36th month.

52. C

UMPRINC formula:

Example: =CUMPRINC(0.05/12, 12*5, -20000, 1, 12*3)

This formula calculates the cumulative principal paid on a loan with an annual interest rate of 5%, 12 monthly payments per year, a principal amount of $20,000, and payments made from the first to the 36th month.

53. PMT formula (for annuity due):

Example: =PMT(0.05/12, 12*5, -20000, 0, 1)

This formula calculates the monthly payment for an annuity due, where payments are made at the beginning of each period.

54. VDB formula:

Example: =VDB(1000, 100, 5, 1, 3)

This formula calculates the depreciation of an asset for the first 3 periods using the declining balance method.

55. HLOOKUP formula:

Example: =HLOOKUP(“Apples”, AV1:AX10, 2, FALSE)

This formula looks for the value “Apples” in the first row of the range AV1 to AX10 and returns the corresponding value from the second row.

56. INDEX-MATCH formula:

Example: =INDEX(AV1:AX10, MATCH(“Oranges”, AW1:AW10, 0), 2)

This formula combines the INDEX and MATCH functions to find the row that contains “Oranges” in column AW and returns the value from the second column in the range AV1 to AX10.

57. OFFSET formula:

Example: =OFFSET(AY1, 3, 2, 2, 1)

This formula returns a range of cells starting from cell AY1, moving 3 rows down and 2 columns to the right, with a height of 2 rows and a width of 1 column.

58. SUMPRODUCT formula:

Example: =SUMPRODUCT(AZ1:AZ5, BA1:BA5)

This formula multiplies the values in the range AZ1 to AZ5 by the values in the range BA1 to BA5 and returns the sum of the products.

59. RANK formula:

Example: =RANK(BB1, BB1:BB10)

This formula calculates the rank of the value in cell BB1 compared to the values in the range BB1 to BB10.

60. STDEV formula:

Example: =STDEV(BC1:BC10)

This formula calculates the standard deviation of the values in the range BC1 to BC10, representing a sample.

61. STDEVP formula:

Example: =STDEVP(BD1:BD10)

This formula calculates the standard deviation of the values in the range BD1 to BD10, representing the entire population.

62. MEDIAN formula:

Example: =MEDIAN(BE1:BE10)

This formula calculates the median (middle value) of the values in the range BE1 to BE10.

63. MODE formula:

Example: =MODE(BF1:BF10)

This formula calculates the mode (most frequently occurring value) of the values in the range BF1 to BF10.

64. QUARTILE formula:

Example: =QUARTILE(BG1:BG10, 3)

This formula calculates the third quartile (75th percentile) of the values in the range BG1 to BG10.

65. PERCENTILE formula:

Example: =PERCENTILE(BH1:BH10, 90%)

This formula calculates the 90th percentile of the values in the

range BH1 to BH10.

66. VARIANCE formula:

Example: =VAR(BI1:BI10)

This formula calculates the variance of the values in the range BI1 to BI10, representing a sample.

67. VARP formula:

Example: =VARP(BJ1:BJ10)

This formula calculates the variance of the values in the range BJ1 to BJ10, representing the entire population.

68. UPPER formula (applied to a range):

Example: =UPPER(BK1:BK10)

This formula converts the text in cells BK1 to BK10 to uppercase.

69. LOWER formula (applied to a range):

Example: =LOWER(BL1:BL10)

This formula converts the text in cells BL1 to BL10 to lowercase.

70. PROPER formula (applied to a range):

Example: =PROPER(BM1:BM10)

This formula capitalizes the first letter of each word in the text in cells BM1 to BM10.

71. SUBSTITUTE formula:

Example: =SUBSTITUTE(BN1, “Apple”, “Orange”)

This formula replaces the text “Apple” with “Orange” in the text contained in cell BN1.

72. FIND formula:

Example: =FIND(“e”, BO1)

This formula finds the position of the letter “e” in the text contained in cell BO1.

73. SEARCH formula:

Example: =SEARCH(“e”, BP1)

This formula searches for the letter “e” in the text contained in cell BP1 and returns its position.

74. REPLACE formula:

Example: =REPLACE(BQ1, 3, 2, “xyz”)

This formula replaces 2 characters starting from the 3rd position of the text in cell BQ1 with the text “xyz”.

75. MID formula (extracting variable number of characters within a range):

Example: =MID(BR1, SEARCH(” “, BR1)+1, LEN(BR1)-SEARCH(” “, BR1))

This formula extracts the characters after the first space until the end of the text in cell BR1, applied to a range of cells.

76. CONCATENATE formula (with cell ranges):

Example: =CONCATENATE(BS1:BS10)

This formula combines the text in cells BS1 to BS10 into a single string.

77. TEXTJOIN formula:

Example: =TEXTJOIN(“, “, TRUE, BT1:BT10)

This formula combines the text in cells BT1 to BT10, separated by a comma and space.

78. LEFT formula (extracting variable number of characters within a range):

Example: =LEFT(BU1, SEARCH(” “, BU1)-1)

This formula extracts the characters from the beginning of the text in cell BU1 until the first space, applied to a range of cells.

79. RIGHT formula (extracting variable number of characters within a range):

Example: =RIGHT(BV1, LEN(BV1)-SEARCH(” “, BV1))

This formula extracts the characters from the first space to the end of the text in cell BV1, applied to a range of cells.

80. SUMPRODUCT formula (multiple ranges):

Example: =SUMPRODUCT(BW1:BW5, BX1:BX5, BY1:BY5)

This formula multiplies the values in the ranges BW1 to BW5, BX1 to BX5, and BY1 to BY5, and returns the sum of the products.

81. INDEX-MATCH formula (multiple criteria):

Example: =INDEX(BZ1:BZ10, MATCH(1, (

CA1:CA10=”Red”)*(CB1:CB10=”Large”), 0))

This formula combines the INDEX and MATCH functions to find the first occurrence where the corresponding cells in ranges CA1 to CA10 and CB1 to CB10 meet specific criteria, and returns the value from the range BZ1 to BZ10.

82. COUNTIF formula (with wildcard):

Example: =COUNTIF(CC1:CC10, “App*”)

This formula counts the number of cells in the range CC1 to CC10 that start with the text “App”.

83. SUMIF formula (with wildcard):

Example: =SUMIF(CD1:CD10, “Fruit*”, CE1:CE10)

This formula sums the values in the range CE1 to CE10 where the corresponding cells in the range CD1 to CD10 start with the text “Fruit”.

84. AVERAGEIF formula (with wildcard):

Example: =AVERAGEIF(CF1:CF10, “Red*”, CG1:CG10)

This formula calculates the average of the values in the range CG1 to CG10 where the corresponding cells in the range CF1 to CF10 start with the text “Red”.

85. MAX formula (with criteria):

Example: =MAX(IF(CH1:CH10=”Apples”, CI1:CI10))

This formula finds the maximum value in the range CI1 to CI10, considering only the values where the corresponding cells in the range CH1 to CH10 are “Apples”. It is an array formula, so remember to press Ctrl+Shift+Enter when entering it.

86. MIN formula (with criteria):

Example: =MIN(IF(CJ1:CJ10=”Oranges”, CK1:CK10))

This formula finds the minimum value in the range CK1 to CK10, considering only the values where the corresponding cells in the range CJ1 to CJ10 are “Oranges”. It is an array formula, so remember to press Ctrl+Shift+Enter when entering it.

87. SUMIFS formula (with wildcard):

Example: =SUMIFS(CL1:CL10, CM1:CM10, “Fruit*”, CN1:CN10, “>50”)

This formula sums the values in the range CL1 to CL10 where the corresponding cells in the range CM1 to CM10 start with the text “Fruit” and the corresponding cells in the range CN1 to CN10 are greater than 50.

88. AVERAGEIFS formula (with wildcard):

Example: =AVERAGEIFS(CO1:CO10, CP1:CP10, “Red*”, CQ1:CQ10, “>0”)

This formula calculates the average of the values in the range CO1 to CO10 where the corresponding cells in the range CP1 to CP10 start with the text “Red” and the corresponding cells in the range CQ1 to CQ10 are greater than 0.

89. COUNTBLANK formula:

Example: =COUNTBLANK(CR1:CR10)

This formula counts the number of blank cells in the range CR1 to CR10.

90. CONCAT formula (with cell ranges):

Example: =CONCAT(CS1:CT10)

This formula combines the text in cells CS1 to CT10 into a single string.

91. SUBTOTAL formula (dynamic):

Example: =SUBTOTAL(9, CU1:CU10)

This formula calculates the sum of the visible cells in the range CU1 to CU10, excluding any hidden rows, and updates automatically when the filtering of data changes.

92. XNPV formula:

Example: =XNPV(0

.1, CV1:CV5, CW1:CW5)

This formula calculates the net present value of cash flows in the range CW1 to CW5 with specific dates in the range CV1 to CV5 and a discount rate of 10%.

93. XIRR formula:

Example: =XIRR(CV1:CV5, CW1:CW5)

This formula calculates the internal rate of return for a series of cash flows in the range CW1 to CW5 with specific dates in the range CV1 to CV5.

94. POWER formula:

Example: =POWER(CX1, CY1)

This formula raises the number in cell CX1 to the power of the number in cell CY1.

95. SQRT formula:

Example: =SQRT(CZ1)

This formula calculates the square root of the number in cell CZ1.

96. LOG formula:

Example: =LOG(DA1)

This formula calculates the natural logarithm of the number in cell DA1.

97. ROUND formula:

Example: =ROUND(DB1, 2)

This formula rounds the number in cell DB1 to 2 decimal places.

98. INT formula:

Example: =INT(DC1)

This formula rounds down the number in cell DC1 to the nearest integer.

99. MOD formula:

Example: =MOD(DD1, 7)

This formula returns the remainder when the number in cell DD1 is divided by 7.

Conclusion

In this journey through the vast landscape of Excel formulas, we’ve uncovered the essential tools that can turn ordinary spreadsheets into dynamic, data-driven powerhouses. Excel, with its array of functions, enables users to perform complex calculations, manipulate data effortlessly, and derive meaningful insights.

As you embark on applying these 99 formulas in your Excel endeavors, remember that mastery comes with practice. Experiment with different scenarios, explore the capabilities of each formula, and discover the unique ways they can transform your data.

Whether you’re a data analyst, financial professional, student, or anyone navigating the world of spreadsheets, these formulas are your companions in achieving efficiency, accuracy, and productivity. From basic arithmetic to advanced statistical analyses, Excel has the formula for every data challenge you may encounter.

As the Excel journey continues, stay curious, embrace continuous learning, and leverage these formulas to their full potential. Armed with this newfound knowledge, you’re equipped to tackle the complexities of data management and analysis, making Excel an invaluable ally in your professional and academic pursuits.

May your spreadsheets be error-free, your data insights profound, and your Excel skills ever-evolving.