Excel Formula List Cheat Sheet

Excel is a powerful tool for handling a vast array of data tasks, from basic arithmetic to complex data analysis. Mastering a wide range of formulas can dramatically improve your efficiency and the depth of insights you can derive from your data. Whether you’re looking to crunch numbers, analyze trends, or manage your workflow, these 100 essential Excel formulas will equip you with the skills you need to get the job done.

Here’s a comprehensive list of Excel formulas, complete with descriptions and examples to help you harness the full potential of this versatile tool:

99 Formula Excel Cheat Sheet for Data Analysis and Management

1. SUM

• Description: Adds all the numbers in a range of cells.
• Example: =SUM(A1:A5) Adds the values in cells A1 through A5.

2. AVERAGE

• Description: Calculates the average (arithmetic mean) of a group of numbers.
• Example: =AVERAGE(B1:B5) Returns the average of the values in cells B1 through B5.

3. COUNT

• Description: Counts the number of cells that contain numbers.
• Example: =COUNT(C1:C10) Counts the number of cells with numerical values in the range C1 to C10.

4. COUNTA

• Description: Counts the number of non-empty cells.
• Example: =COUNTA(D1:D10) Counts the number of non-empty cells in the range D1 to D10.

5. IF

• Description: Checks whether a condition is met, returns one value if TRUE, and another value if FALSE.
• Example: =IF(E1>50, "Pass", "Fail") If the value in E1 is greater than 50, it returns "Pass"; otherwise, it returns "Fail".

6. VLOOKUP

• Description: Searches for a value in the first column of a table and returns a value in the same row from another column.
• Example: =VLOOKUP(F1, A1:C10, 3, FALSE) Looks for the value in F1 in the first column of the range A1and returns the value in the third column of that range.

7. HLOOKUP

• Description: Searches for a value in the top row of a table and returns a value in the same column from a row you specify.
• Example: =HLOOKUP(G1, A1:F5, 3, FALSE) Looks for the value in G1 in the top row of the range A1and returns the value from the third row of that range.

8. CONCATENATE (or CONCAT)

• Description: Joins two or more text strings into one string.
• Example: =CONCATENATE(H1, " ", I1) Combines the values in H1 and I1 with a space in between.

9. LEFT

• Description: Returns the first character or characters in a text string, based on the number of characters you specify.
• Example: =LEFT(J1, 3) Returns the first 3 characters of the text in J1.

10. RIGHT

• Description: Returns the last character or characters in a text string, based on the number of characters you specify.
• Example: =RIGHT(K1, 2) Returns the last 2 characters of the text in K1.

11. MID

• Description: Returns a specific number of characters from a text string, starting at the position you specify.
• Example: =MID(L1, 2, 4) Returns 4 characters from the text in L1, starting at the second character.

12. LEN

• Description: Returns the number of characters in a text string.
• Example: =LEN(M1) Counts the number of characters in the text in M1.

13. TRIM

• Description: Removes all spaces from text except for single spaces between words.
• Example: =TRIM(N1) Removes extra spaces from the text in N1.

14. TODAY

• Description: Returns the current date.
• Example: =TODAY() Returns the current date in the cell.

15. NOW

• Description: Returns the current date and time.
• Example: =NOW() Returns the current date and time in the cell.

16. DATE

• Description: Returns the serial number of a particular date.
• Example: =DATE(2024, 8, 11) Returns the serial number of August 11, 2024.

17. DATEDIF

• Description: Calculates the difference between two dates.
• Example: =DATEDIF(O1, P1, "D") Returns the number of days between the dates in cells O1 and P1.

18. EDATE

• Description: Returns the serial number of the date that is the indicated number of months before or after a specified date.
• Example: =EDATE(Q1, 3) Returns the date 3 months after the date in Q1.

19. FIND

• Description: Finds one text string within another and returns the number of the starting position of the found text.
• Example: =FIND("apple", R1) Finds "apple" in the text in R1 and returns the starting position.

20. SUBSTITUTE

• Description: Replaces existing text with new text in a text string.
• Example: =SUBSTITUTE(S1, "old", "new") Replaces occurrences of "old" with "new" in the text in S1.

21. ROUND

• Description: Rounds a number to a specified number of digits.
• Example: =ROUND(T1, 2) Rounds the number in T1 to 2 decimal places.

22. ROUNDUP

• Description: Rounds a number up, away from zero.
• Example: =ROUNDUP(U1, 0) Rounds the number in U1 up to the nearest whole number.

23. ROUNDDOWN

• Description: Rounds a number down, towards zero.
• Example: =ROUNDDOWN(V1, 0) Rounds the number in V1 down to the nearest whole number.

24. ABS

• Description: Returns the absolute value of a number.
• Example: =ABS(W1) Returns the absolute value of the number in W1.

25. POWER

• Description: Returns the result of a number raised to a power.
• Example: =POWER(X1, 2) Returns the square of the number in X1.

26. SQRT

• Description: Returns the square root of a number.
• Example: =SQRT(Y1) Returns the square root of the number in Y1.

27. MAXIFS

• Description: Returns the maximum value among cells specified by a given set of conditions.
• Example: =MAXIFS(Z1:Z10, A1:A10, ">10") Returns the maximum value from Z1 to Z10 where the corresponding cell in A1 to A10 is greater than 10.

28. MINIFS

• Description: Returns the minimum value among cells specified by a given set of conditions.
• Example: =MINIFS(AA1:AA10, A1:A10, "<20") Returns the minimum value from AA1 to AA10 where the corresponding cell in A1 to A10 is less than 20.

29. IFS

• Description: Checks whether one or more conditions are met and returns a value corresponding to the first TRUE condition.
• Example: =IFS(AB1>90, "Excellent", AB1>80, "Good", AB1>70, "Average", TRUE, "Poor") Returns a corresponding grade based on the value in AB1.

30. SWITCH

• Description: Evaluates an expression against a list of values and returns the result corresponding to the first matching value.
• Example: =SWITCH(AC1, "A", 90, "B", 80, "C", 70, "D", 60, "F", 50) Returns a score based on the letter grade in AC1.

31. UNIQUE

• Description: Returns a list of unique values in a range.
• Example: =UNIQUE(AD1:AD10) Returns unique values from the range AD1 to AD10.

32. SORT

• Description: Sorts the contents of a range or array.
• Example: =SORT(AE1:AE10, 1, TRUE) Sorts the range AE1 to AE10 in ascending order.

33. FILTER

• Description: Filters a range of data based on criteria.
• Example: =FILTER(AF1:AF10, AG1:AG10="Yes") Returns rows from AF1 to AF10 where the corresponding cell in AG1 to AG10 equals "Yes".

34. XLOOKUP

• Description: Searches a range or array and returns an item corresponding to the first match it finds.
• Example: =XLOOKUP(AH1, A1:A10, B1:B10) Searches for the value in AH1 within A1 and returns the corresponding value from B1.

35. TEXTJOIN

• Description: Joins text from multiple ranges and/or strings, with a specified delimiter.
• Example: =TEXTJOIN(", ", TRUE, AI1:AI5) Joins the text in AI1 through AI5 with a comma and space between each item.

36. FORMULATEXT

• Description: Returns the formula as text from a cell.
• Example: =FORMULATEXT(AJ1) Returns the formula in cell AJ1 as a text string.

37. TRANSPOSE

• Description: Converts a vertical range of cells to a horizontal range, or vice versa.
• Example: =TRANSPOSE(AK1:AL3) Converts the vertical range AK1to a horizontal range.

38. ISNUMBER

• Description: Checks whether a value is a number.
• Example: =ISNUMBER(AM1) Returns TRUE if the value in AM1 is a number; otherwise, FALSE.

39. ISBLANK

• Description: Checks whether a cell is empty.
• Example: =ISBLANK(AN1) Returns TRUE if cell AN1 is empty; otherwise, FALSE.

40. INDIRECT

• Description: Returns the reference specified by a text string.
• Example: =INDIRECT("A" & AO1) Returns the value from the cell specified by the text string "A" followed by the value in AO1.

41. CELL

• Description: Returns information about the formatting, location, or contents of a cell.
• Example: =CELL("address", AP1) Returns the address of the cell AP1.

42. ADDRESS

• Description: Returns the address of a cell based on a specified row and column number.
• Example: =ADDRESS(1, 1) Returns the address of the cell in the first row and first column (i.e., A1).

43. ROW

• Description: Returns the row number of a cell reference.
• Example: =ROW(AQ1) Returns the row number of cell AQ1.

44. COLUMN

• Description: Returns the column number of a cell reference.
• Example: =COLUMN(AR1) Returns the column number of cell AR1.

45. EOMONTH

• Description: Returns the serial number of the last day of the month, before or after a specified number of months.
• Example: =EOMONTH(AS1, 1) Returns the last day of the month one month after the date in AS1.

46. NETWORKDAYS

• Description: Returns the number of whole working days between two dates.
• Example: =NETWORKDAYS(AT1, AU1) Returns the number of working days between the dates in AT1 and AU1.

47. WORKDAY

• Description: Returns the serial number of the date before or after a specified number of workdays.
• Example: =WORKDAY(AV1, 10) Returns the date that is 10 working days after the date in AV1.

48. TEXT

• Description: Formats a number and converts it to text.
• Example: =TEXT(AW1, "dd/mm/yyyy") Formats the date in AW1 as "day/month/year".

49. HYPERLINK

• Description: Creates a shortcut or link to another cell, file, or URL.
• Example: =HYPERLINK("http://www.example.com", "Go to Example") Creates a link with the text "Go to Example" that directs to "http://www.example.com".

50. PERCENTILE

• Description: Returns the k-th percentile of values in a range.
• Example: =PERCENTILE(AX1:AX10, 0.9) Returns the 90th percentile of the values in the range AX1 to AX10.

51. PERCENTRANK

• Description: Returns the rank of a value in a data set as a percentage.
• Example: =PERCENTRANK(AY1:AY10, AZ1) Returns the percentage rank of the value in AZ1 among the values in AY1 to AY10.

52. MEDIAN

• Description: Returns the median (middle value) of a group of numbers.
• Example: =MEDIAN(BA1:BA10) Returns the median of the values in the range BA1 to BA10.

53. MODE

• Description: Returns the most frequently occurring value in a range.
• Example: =MODE(BB1:BB10) Returns the mode of the values in the range BB1 to BB10.

54. SUMIF

• Description: Adds the cells specified by a given condition or criteria.
• Example: =SUMIF(BC1:BC10, ">50") Adds the values in BC1 to BC10 where the cell value is greater than 50.

55. SUMIFS

• Description: Adds the cells in a range that meet multiple criteria.
• Example: =SUMIFS(BD1:BD10, BE1:BE10, ">10", BF1:BF10, "<100") Adds the values in BD1 to BD10 where the corresponding cells in BE1 to BE10 are greater than 10 and in BF1 to BF10 are less than 100.

56. COUNTIF

• Description: Counts the number of cells that meet a specific condition.
• Example: =COUNTIF(BG1:BG10, "Yes") Counts the number of cells in BG1 to BG10 that contain "Yes".

57. COUNTIFS

• Description: Counts the number of cells that meet multiple criteria.
• Example: =COUNTIFS(BH1:BH10, ">10", BI1:BI10, "<100") Counts the number of cells in BH1 to BH10 where the value is greater than 10 and the corresponding cells in BI1 to BI10 are less than 100.

58. ISERROR

• Description: Checks whether a cell contains an error.
• Example: =ISERROR(BJ1) Returns TRUE if cell BJ1 contains an error; otherwise, FALSE.

59. ISERR

• Description: Checks whether a cell contains any error except #N/A.
• Example: =ISERR(BK1) Returns TRUE if cell BK1 contains an error other than #N/A; otherwise, FALSE.

60. ISNA

• Description: Checks whether a cell contains the #N/A error.
• Example: =ISNA(BL1) Returns TRUE if cell BL1 contains the #N/A error; otherwise, FALSE.

61. NA

• Description: Returns the #N/A error value.
• Example: =NA() Returns the #N/A error.

62. NOT

• Description: Reverses the logical value of its argument.
• Example: =NOT(BM1>50) Returns TRUE if BM1 is not greater than 50; otherwise, FALSE.

63. TRUE

• Description: Returns the logical value TRUE.
• Example: =TRUE() Returns the logical value TRUE.

64. FALSE

• Description: Returns the logical value FALSE.
• Example: =FALSE() Returns the logical value FALSE.

65. TEXTSPLIT

• Description: Splits text into separate columns or rows based on a delimiter.
• Example: =TEXTSPLIT(BN1, ",") Splits the text in BN1 into separate columns using a comma as the delimiter.

66. DOLLAR

• Description: Converts a number to text in currency format.
• Example: =DOLLAR(BO1, 2) Converts the number in BO1 to text in currency format with 2 decimal places.

67. RANDBETWEEN

• Description: Returns a random integer between the numbers you specify.
• Example: =RANDBETWEEN(1, 100) Returns a random integer between 1 and 100.

68. RAND

• Description: Returns a random number between 0 and 1.
• Example: =RAND() Returns a random decimal number between 0 and 1.

69. SEQUENCE

• Description: Generates a list of sequential numbers in an array.
• Example: =SEQUENCE(10, 1, 1, 1) Generates a vertical array of numbers from 1 to 10.

70. UNIQUE

• Description: Returns a list of unique values from a range.
• Example: =UNIQUE(BP1:BP10) Returns unique values from the range BP1 to BP10.

71. FILTERXML

• Description: Parses XML data and returns specific data based on the XPath query.
• Example: =FILTERXML(BQ1, "//book/title") Parses the XML data in BQ1 to return the title of the book.

72. FORMULATEXT

• Description: Returns the formula as a text from a cell.
• Example: =FORMULATEXT(BR1) Returns the formula in cell BR1 as a text string.

73. OFFSET

• Description: Returns a reference to a range that is offset from a starting cell or range.
• Example: =OFFSET(BS1, 1, 2, 3, 1) Returns a reference to a range starting 1 row down and 2 columns to the right of BS1, with a height of 3 rows and a width of 1 column.

74. CHOOSE

• Description: Chooses a value from a list of values based on an index number.
• Example: =CHOOSE(BT1, "Red", "Green", "Blue") Returns "Red" if BT1 is 1, "Green" if BT1 is 2, or "Blue" if BT1 is 3.

75. HYPERLINK

• Description: Creates a clickable link to another cell, file, or URL.
• Example: =HYPERLINK("https://www.example.com", "Visit Example") Creates a hyperlink with the text "Visit Example" that links to "https://www.example.com".

76. IFERROR

• Description: Returns a value you specify if a formula results in an error; otherwise, it returns the result of the formula.
• Example: =IFERROR(BV1, "Error") Returns "Error" if BV1 results in an error; otherwise, returns the value in BV1.

77. DCOUNT

• Description: Counts the cells that contain numbers in a database that match the specified criteria.
• Example: =DCOUNT(BW1:BW10, "Amount", BX1:BY10) Counts the cells in the "Amount" column of the database BW1where the criteria in BX1are met.

78. DB

• Description: Calculates the depreciation of an asset for a specified period using the fixed-declining balance method.
• Example: =DB(BZ1, 1000, 200, 10, 1) Calculates the depreciation for an asset with an initial cost of 1000, salvage value of 200, life of 10 years, and year 1.

79. PV

• Description: Returns the present value of an investment based on a constant interest rate.
• Example: =PV(0.05, 10, -100) Returns the present value of an investment with an interest rate of 5%, 10 periods, and payment of 100.

80. FV

• Description: Returns the future value of an investment based on a constant interest rate.
• Example: =FV(0.05, 10, -100) Returns the future value of an investment with an interest rate of 5%, 10 periods, and payment of 100.

81. PMT

• Description: Returns the payment amount for a loan based on constant payments and a constant interest rate.
• Example: =PMT(0.05, 10, 1000) Calculates the payment amount for a loan with an interest rate of 5%, 10 periods, and a loan amount of 1000.

82. NPV

• Description: Returns the net present value of an investment based on a series of periodic cash flows and a discount rate.
• Example: =NPV(0.05, 100, 200, 300) Returns the net present value of cash flows of 100, 200, and 300 with a discount rate of 5%.

83. IRR

• Description: Returns the internal rate of return for a series of cash flows.
• Example: =IRR(C1:C5) Returns the internal rate of return for the cash flows in the range C1 to C5.

84. SLN

Description: Calculates the straight-line depreciation of an asset for one period.

• Example: =SLN(1000, 200, 5) Calculates the straight-line depreciation for an asset with a cost of 1000, salvage value of 200, and life of 5 years.

85. SYD

• Description: Calculates the sum-of-years' digits depreciation of an asset for a specified period.
• Example: =SYD(1000, 200, 5, 3) Calculates the sum-of-years' digits depreciation for an asset with a cost of 1000, salvage value of 200, life of 5 years, and period 3.

86. DDB

• Description: Calculates the depreciation of an asset for a specified period using the double-declining balance method.
• Example: =DDB(1000, 200, 5, 3) Calculates the double-declining balance depreciation for an asset with a cost of 1000, salvage value of 200, life of 5 years, and period 3.

87. CUMIPMT

• Description: Returns the cumulative interest paid on a loan between two periods.
• Example: =CUMIPMT(0.05, 10, 1000, 1, 5, 0) Calculates the cumulative interest paid between periods 1 and 5 on a loan with an interest rate of 5%, 10 periods, and a loan amount of 1000.

88. CUMPRINC

• Description: Returns the cumulative principal paid on a loan between two periods.
• Example: =CUMPRINC(0.05, 10, 1000, 1, 5, 0) Calculates the cumulative principal paid between periods 1 and 5 on a loan with an interest rate of 5%, 10 periods, and a loan amount of 1000.

89. SLN

• Description: Calculates the straight-line depreciation of an asset.
• Example: =SLN(5000, 500, 10) Returns the straight-line depreciation amount of an asset with a cost of 5000, salvage value of 500, and a useful life of 10 years.

90. ACCRINT

• Description: Calculates the accrued interest for a security that pays periodic interest.
• Example: =ACCRINT(C1, D1, E1, F1, G1, "ACT/ACT") Calculates the accrued interest of a security based on the issue date in C1, settlement date in D1, first coupon date in E1, coupon rate in F1, and face value in G1.

91. PRICE

• Description: Returns the price per $100 face value of a security that pays periodic interest.
• Example: =PRICE(C1, D1, E1, F1, G1, H1) Returns the price of a security with a face value of $100, based on its issue date in C1, maturity date in D1, coupon rate in E1, yield in F1, and frequency of coupon payments in G1.

92. YIELD

• Description: Returns the yield on a security that pays periodic interest.
• Example: =YIELD(C1, D1, E1, F1, G1, H1) Returns the yield of a security based on its settlement date in C1, maturity date in D1, coupon rate in E1, price in F1, face value in G1, and frequency of coupon payments in H1.

93. DURATION

• Description: Returns the Macauley duration for an assumed security with periodic interest payments.
• Example: =DURATION(C1, D1, E1, F1, G1, H1) Returns the duration of a security based on its settlement date in C1, maturity date in D1, coupon rate in E1, yield in F1, face value in G1, and frequency of coupon payments in H1.

94. ODEPREC

• Description: Returns the depreciation of an asset for a specified period using the ODE method.
• Example: =ODEPREC(1000, 100, 5, 3) Calculates the depreciation for an asset with a cost of 1000, salvage value of 100, and a useful life of 5 years for the 3rd period.

95. FV

• Description: Calculates the future value of an investment based on periodic, constant payments and a constant interest rate.
• Example: =FV(0.03, 5, -200) Returns the future value of an investment with a 3% interest rate, 5 periods, and $200 payment per period.

96. PV

• Description: Calculates the present value of an investment based on periodic, constant payments and a constant interest rate.
• Example: =PV(0.03, 5, -200) Returns the present value of an investment with a 3% interest rate, 5 periods, and $200 payment per period.

97. PMT

• Description: Calculates the payment for a loan based on periodic, constant payments and a constant interest rate.
• Example: =PMT(0.03, 5, 1000) Returns the payment amount for a loan with a 3% interest rate, 5 periods, and a $1000 loan amount.

98. NPV

• Description: Returns the net present value of an investment based on periodic cash flows and a discount rate.
• Example: =NPV(0.03, 200, 300, 400) Returns the net present value of cash flows of 200, 300, and 400 with a discount rate of 3%.

99. IRR

• Description: Returns the internal rate of return for a series of cash flows.
• Example: =IRR(A1:A10) Returns the internal rate of return for the cash flows in the range A1.

Excel's expansive arsenal of formulas is a testament to its versatility and power in data manipulation. Whether you're analyzing financial statements, managing project timelines, or simply organizing a household budget, these formulas can significantly enhance your productivity and accuracy.

From the classic SUM and AVERAGE to the more advanced XLOOKUP and FILTER functions, each formula serves a unique purpose that can streamline your workflows and provide deeper insights. Mastering these formulas not only saves time but also equips you with the skills to handle complex data challenges with confidence.

As you explore these formulas, remember that practice is key. Start with basic functions and gradually incorporate more advanced ones into your daily tasks. Experimenting with different combinations will help you understand their nuances and applications better. Over time, you'll discover how these formulas can transform your data management practices and enable more informed decision-making.

Excel remains an indispensable tool in the world of data analysis, and its formulas are the heart of its functionality. Embrace these formulas, and you'll unlock a new level of efficiency and precision in your work. Happy spreadsheeting!

Learn Excel with me here.