100+ Excel Formulas

Here’s a comprehensive list of 100+ Excel formulas with examples to cover various categories like arithmetic, text manipulation, date and time, lookup, and logical operations. Each formula is accompanied by an example for clarity.

1. SUM
Adds up values in a range.
=SUM(A1:A5)
Adds values from A1 to A5.
2. AVERAGE
Calculates the average of a range.
=AVERAGE(B1:B5)
Returns the average of values in cells B1 to B5.
3. COUNT
Counts the number of numeric values in a range.
=COUNT(C1:C10)
Counts how many numbers are in C1 to C10.
4. COUNTA
Counts the number of non-empty cells.
=COUNTA(D1:D10)
Counts non-empty cells in D1 to D10.
5. IF
Checks a condition and returns one value if TRUE and another if FALSE.
=IF(E1>100, "Pass", "Fail")
Returns "Pass" if E1 is greater than 100, otherwise "Fail".
6. IFERROR
Returns a custom value if the formula results in an error.
=IFERROR(A1/B1, "Error")
If there is an error in A1/B1, it returns "Error", otherwise, the result of A1/B1.
7. VLOOKUP
Looks for a value in the leftmost column and returns a value in the same row from a specified column.
=VLOOKUP(A2, A1:D10, 2, FALSE)
Looks for the value of A2 in the range A1:D10 and returns the value from the 2nd column of the same row.
8. HLOOKUP
Searches for a value in the top row and returns a value in the same column from a specified row.
=HLOOKUP(B1, A1:D5, 3, FALSE)
Searches for the value of B1 in the top row and returns the value from the 3rd row of the same column.
9. MATCH
Returns the relative position of an item in a range.
=MATCH("Apples", A1:A10, 0)
Finds the position of "Apples" in the range A1:A10.
10. INDEX
Returns the value of a cell at the intersection of a row and column within a range.
=INDEX(A1:C10, 3, 2)
Returns the value at the intersection of the 3rd row and 2nd column in the range A1:C10.
11. LEN
Returns the number of characters in a text string.
=LEN(A1)
Returns the length of the text in cell A1.
12. LEFT
Extracts a given number of characters from the start of a text string.
=LEFT(A1, 3)
Returns the first 3 characters from the text in A1.
13. RIGHT
Extracts a given number of characters from the end of a text string.
=RIGHT(A1, 4)
Returns the last 4 characters from the text in A1.
14. MID
Returns a specific number of characters from a text string starting at a position you specify.
=MID(A1, 2, 5)
Returns 5 characters starting from the 2nd position in the text in A1.
15. TRIM
Removes all extra spaces from text except for single spaces between words.
=TRIM(A1)
Removes excess spaces from the text in A1.
16. CONCATENATE (or CONCAT)
Joins two or more text strings into one.
=CONCATENATE(A1, " ", B1)
Combines the text in A1 and B1 with a space in between.
17. SUBSTITUTE
Replaces occurrences of old text with new text.
=SUBSTITUTE(A1, "old", "new")
Replaces all instances of "old" with "new" in the text in A1.
18. REPLACE
Replaces part of a text string based on the number of characters.
=REPLACE(A1, 2, 3, "XYZ")
Replaces 3 characters in A1 starting at the 2nd position with "XYZ".
19. SEARCH
Returns the position of a substring within a string (case-insensitive).
=SEARCH("apple", A1)
Finds the position of "apple" in the text in A1.
20. FIND
Returns the position of a substring within a string (case-sensitive).
=FIND("Apple", A1)
Finds the position of "Apple" in the text in A1.
21. UPPER
Converts text to uppercase.
=UPPER(A1)
Converts the text in A1 to uppercase.
22. LOWER
Converts text to lowercase.
=LOWER(A1)
Converts the text in A1 to lowercase.
23. PROPER
Converts the first letter of each word in a text string to uppercase.
=PROPER(A1)
Converts the text in A1 to proper case (e.g., "john doe" becomes "John Doe").
24. NOW
Returns the current date and time.
=NOW()
Returns the current date and time.
25. TODAY
Returns the current date.
=TODAY()
Returns today’s date.
26. YEAR
Returns the year of a date.
=YEAR(A1)
Returns the year from the date in A1.
27. MONTH
Returns the month of a date.
=MONTH(A1)
Returns the month from the date in A1.
28. DAY
Returns the day of a date.
=DAY(A1)
Returns the day from the date in A1.
29. HOUR
Returns the hour of a time.
=HOUR(A1)
Returns the hour from the time in A1.
30. MINUTE
Returns the minute of a time.
=MINUTE(A1)
Returns the minute from the time in A1.
31. SECOND
Returns the second of a time.
=SECOND(A1)
Returns the second from the time in A1.
32. TEXT
Formats a number or date as text in a specified format.
=TEXT(A1, "mm/dd/yyyy")
Converts the date in A1 to "mm/dd/yyyy" format.
33. DATEDIF
Calculates the difference between two dates.
=DATEDIF(A1, B1, "d")
Calculates the number of days between A1 and B1.
34. NETWORKDAYS
Returns the number of working days between two dates.
=NETWORKDAYS(A1, B1)
Returns the number of weekdays between two dates, excluding weekends.
35. WEEKDAY
Returns the day of the week for a date.
=WEEKDAY(A1)
Returns a number representing the day of the week for the date in A1 (e.g., 1 = Sunday).
36. EOMONTH
Returns the last day of the month, a specified number of months in the future or past.
=EOMONTH(A1, 2)
Returns the last day of the month, two months after the date in A1.
37. SUMIF
Adds the cells that meet a single condition.
=SUMIF(A1:A10, ">50")
Adds all values in A1:A10 that are greater than 50.
38. COUNTIF
Counts the number of cells that meet a single condition.
=COUNTIF(A1:A10, "Yes")
Counts the number of cells in A1:A10 that contain "Yes".
39. AVERAGEIF
Calculates the average of cells that meet a specific condition.
=AVERAGEIF(A1:A10, ">50")
Averages all values in A1:A10 that are greater than 50.
40. ROUND
Rounds a number to a specified number of digits.
=ROUND(A1, 2)
Rounds the value in A1 to 2 decimal places.
41. ROUNDUP
Rounds a number up to a specified number of digits.
=ROUNDUP(A1, 2)
Rounds the value in A1 up to 2 decimal places.
UNDDOWN** Rounds a number down to a specified number of digits.
=ROUNDDOWN(A1, 2)
Rounds the value in A1 down to 2 decimal places.
43. INT
Rounds a number down to the nearest integer.
=INT(A1)
Rounds the value in A1 down to the nearest integer.
44. MOD
Returns the remainder from a division.
=MOD(A1, 3)
Returns the remainder of A1 divided by 3.
45. POWER
Returns the result of a number raised to a power.
=POWER(A1, 3)
Returns the result of A1 raised to the power of 3.
46. SQRT
Returns the square root of a number.
=SQRT(A1)
Returns the square root of the value in A1.
47. RAND
Returns a random number between 0 and 1.
=RAND()
Generates a random number between 0 and 1.
48. RANDBETWEEN
Returns a random integer between two values.
=RANDBETWEEN(1, 100)
Generates a random integer between 1 and 100.
49. PI
Returns the value of π (3.14159…).
=PI()
Returns the value of pi.
50. ABS
Returns the absolute value of a number.
=ABS(A1)
Returns the absolute value of A1.

Here’s a list of Excel financial formulas with examples that are commonly used for investment analysis, loan calculations, and financial planning.

51. FV (Future Value)
Calculates the future value of an investment based on constant periodic payments and a constant interest rate.
=FV(5%, 10, -200, -5000)

In this example:
- Interest rate: 5% (annual)
- Periods: 10 years
- Payment: -200 (monthly payment)
- Present value: -5000 (initial investment)

52. PV (Present Value)
Calculates the present value of an investment or loan based on constant periodic payments and a constant interest rate.
=PV(4%, 5, -1000, 10000)

In this example:
- Interest rate: 4% (annual)
- Periods: 5 years
- Payment: -1000 (annual payment)
- Future value: 10000 (future sum you want)

53. NPV (Net Present Value)
Calculates the net present value of an investment based on a series of future cash flows and a discount rate.
=NPV(8%, A1:A5) + A1
Where A1:A5 contains projected cash flows. The formula adds the initial investment (A1) to the calculated net present value at an 8% discount rate.
54. IRR (Internal Rate of Return)
Calculates the internal rate of return for a series of cash flows (including both initial investments and net income).
=IRR(A1:A6)
Where A1:A6 represents the range of cash flows, including initial investment (which is negative) and future incomes (positive).
55. XIRR
Calculates the internal rate of return for irregular cash flows over specific dates.
=XIRR(A1:A6, B1:B6)

Where:
- A1:A6 contains the cash flows.
- B1:B6 contains the corresponding dates for each cash flow.

56. PMT (Payment)
Calculates the periodic payment for a loan based on constant payments and a constant interest rate.
=PMT(6%/12, 30*12, -100000)

In this example:
- Interest rate: 6% (annual, divided by 12 for monthly)
- Periods: 30 years (multiplied by 12 for monthly payments)
- Loan amount: -100,000

57. PPMT (Principal Payment)
Returns the principal portion of a payment for a given period.
=PPMT(6%/12, 5, 30*12, -100000)
This formula calculates the principal payment in the 5th period of a 30-year loan with a 6% annual interest rate.
58. IPMT (Interest Payment)
Returns the interest portion of a payment for a given period.
=IPMT(6%/12, 5, 30*12, -100000)
This formula calculates the interest portion of the 5th payment of a loan.
59. RATE
Calculates the interest rate per period of a loan or investment.
=RATE(30*12, -600, 100000)

In this example:
- Periods: 30 years (multiplied by 12 for monthly payments)
- Payment: -600 (monthly payment)
- Loan amount: 100,000

60. EFFECT
Calculates the effective annual interest rate, given the nominal interest rate and the number of compounding periods per year.
=EFFECT(6%, 12)

In this example:
- Nominal rate: 6%
- Compounding periods: 12 (monthly)

61. NOMINAL
Calculates the nominal interest rate given the effective rate and the number of compounding periods per year.
=NOMINAL(6.17%, 12)
This converts the 6.17% effective annual rate to a nominal rate assuming 12 compounding periods.
62. CUMIPMT (Cumulative Interest Payment)
Calculates the cumulative interest paid on a loan between two periods.
=CUMIPMT(5%/12, 30*12, 200000, 13, 24, 0)
This calculates the cumulative interest paid between the 13th and 24th months on a 30-year loan of 200,000 with a 5% annual interest rate.
63. CUMPRINC (Cumulative Principal Payment)
Calculates the cumulative principal paid on a loan between two periods.
=CUMPRINC(5%/12, 30*12, 200000, 13, 24, 0)
This calculates the cumulative principal paid between the 13th and 24th months on a 30-year loan of 200,000 with a 5% interest rate.
64. SLN (Straight-Line Depreciation)
Calculates the straight-line depreciation of an asset for one period.
=SLN(10000, 2000, 5)

Where:
- 10,000 is the initial cost of the asset.
- 2,000 is the salvage value.
- 5 is the useful life in years.

65. DB (Declining Balance Depreciation)
Calculates the declining balance depreciation of an asset for a specific period.
=DB(10000, 2000, 5, 1)

Where:
- 10,000 is the initial cost of the asset.
- 2,000 is the salvage value.
- 5 is the useful life.
- 1 is the current period.

66. DDB (Double Declining Balance Depreciation)
Calculates the depreciation of an asset for a specific period using the double-declining balance method.
=DDB(10000, 2000, 5, 1)

Where:
- 10,000 is the initial cost of the asset.
- 2,000 is the salvage value.
- 5 is the useful life.
- 1 is the current period.

67. SYD (Sum-of-Years' Digits Depreciation)
Calculates the sum-of-years' digits depreciation of an asset for a specific period.
=SYD(10000, 2000, 5, 1)

Where:
- 10,000 is the initial cost of the asset.
- 2,000 is the salvage value.
- 5 is the useful life.
- 1 is the current period.

68. MIRR (Modified Internal Rate of Return)
Calculates the modified internal rate of return for a series of periodic cash flows, considering both the cost of the investment and interest received on reinvestment of cash.
=MIRR(A1:A6, 10%, 12%)

Where:
- A1:A6 contains the cash flows.
- 10% is the cost of the investment.
- 12% is the reinvestment rate.

69. XNPV
Calculates the net present value for cash flows at irregular intervals.
=XNPV(10%, A1:A6, B1:B6)

Where:
- A1:A6 contains the cash flows.
- B1:B6 contains the dates for the cash flows.
- 10% is the discount rate.

70. AMORLINC
Calculates the depreciation for each accounting period using a straight-line depreciation method.
=AMORLINC(10000, 500, "2022-01-01", "2027-01-01", 1, 0.1)

Where:
- 10,000 is the cost of the asset.
- 500 is the salvage value.
- "2022-01-01" is the start date.
- "2027-01-01" is the end date.
- 1 specifies the period (in years).
- 0.1 is the depreciation rate.

These financial formulas help with a variety of financial calculations, including loans, investments, depreciation, and more. If you'd like more details on a particular financial formula or application, feel free to ask!

Here’s a list of Excel statistical formulas with examples. These are essential for data analysis, descriptive statistics, and statistical modeling.

71. MEDIAN
Returns the median (middle value) of a group of numbers.
=MEDIAN(A1:A10)
Returns the median of the values in A1 to A10.
72. MODE.SNGL
Returns the most frequent value in a dataset.
=MODE.SNGL(A1:A10)
Returns the most common value in the range A1 to A10.
73. MODE.MULT
Returns an array of the most frequent values in a dataset.
=MODE.MULT(A1:A10)
Returns multiple modes from A1 to A10.
74. STDEV.P (Population Standard Deviation)
Calculates the standard deviation based on the entire population.
=STDEV.P(A1:A10)
Returns the population standard deviation for the range A1 to A10.
75. STDEV.S (Sample Standard Deviation)
Calculates the standard deviation based on a sample.
=STDEV.S(A1:A10)
Returns the sample standard deviation for the range A1 to A10.
76. VAR.P (Population Variance)
Calculates the variance based on the entire population.
=VAR.P(A1:A10)
Returns the population variance for the range A1 to A10.
77. VAR.S (Sample Variance)
Calculates the variance based on a sample.
=VAR.S(A1:A10)
Returns the sample variance for the range A1 to A10.
78. COUNT
Counts the number of cells that contain numbers.
=COUNT(A1:A10)
Counts the number of numeric entries in A1 to A10.
79. COUNTA
Counts the number of non-empty cells.
=COUNTA(A1:A10)
Counts non-empty cells in A1 to A10.
80. COUNTBLANK
Counts the number of empty cells in a range.
=COUNTBLANK(A1:A10)
Returns the count of blank cells in A1 to A10.
81. COUNTIF
Counts the number of cells that meet a specific condition.
=COUNTIF(A1:A10, ">10")
Counts how many values in A1 to A10 are greater than 10.
82. COUNTIFS
Counts the number of cells that meet multiple conditions.
=COUNTIFS(A1:A10, ">10", B1:B10, ">20")
Counts how many values in A1 to A10 are greater than 10 and how many values in B1 to B10 are less than 20.
83. PERCENTILE.EXC
Returns the k-th percentile of values in a range, where k is between 0 and 1 (exclusive).
=PERCENTILE.EXC(A1:A10, 0.9)
Returns the 90th percentile of the values in A1 to A10.
84. PERCENTILE.INC
Returns the k-th percentile of values in a range, where k is between 0 and 1 (inclusive).
=PERCENTILE.INC(A1:A10, 0.9)
Returns the 90th percentile of the values in A1 to A10, inclusive.
85. PERCENTRANK.EXC
Returns the rank of a value in a dataset as a percentage, excluding 0 and 1.
=PERCENTRANK.EXC(A1:A10, 50)
Returns the percentage rank of 50 within A1 to A10.
86. PERCENTRANK.INC
Returns the rank of a value in a dataset as a percentage, including 0 and 1.
=PERCENTRANK.INC(A1:A10, 50)
Returns the percentage rank of 50 within A1 to A10, inclusive.
87. QUARTILE.EXC
Returns the quartile of a dataset, based on percentiles, excluding 0 and 1.
=QUARTILE.EXC(A1:A10, 1)
Returns the 1st quartile (25th percentile) from the values in A1 to A10.
88. QUARTILE.INC
Returns the quartile of a dataset, including 0 and 1.
=QUARTILE.INC(A1:A10, 1)
Returns the 1st quartile (25th percentile) from the values in A1 to A10, inclusive.
89. LARGE
Returns the k-th largest value in a dataset.
=LARGE(A1:A10, 3)
Returns the 3rd largest value from A1 to A10.
90. SMALL
Returns the k-th smallest value in a dataset.
=SMALL(A1:A10, 2)
Returns the 2nd smallest value from A1 to A10.
91. MAX
Returns the largest value in a range.
=MAX(A1:A10)
Returns the maximum value from A1 to A10.
92. MIN
Returns the smallest value in a range.
=MIN(A1:A10)
Returns the minimum value from A1 to A10.
93. RANK.EQ
Returns the rank of a number in a list of numbers. Equal numbers receive the same rank.
=RANK.EQ(A1, A1:A10)
Returns the rank of A1 in the range A1 to A10.
94. RANK.AVG
Returns the rank of a number in a list of numbers, with the average rank for ties.
=RANK.AVG(A1, A1:A10)
Returns the rank of A1 in the range A1 to A10, averaging ranks for tied values.
95. FREQUENCY
Calculates how often values occur within a range of values and returns a vertical array of numbers.
=FREQUENCY(A1:A10, B1:B5)
Calculates the frequency of occurrences of A1 to A10 in the intervals specified in B1 to B5.
96. CORREL
Returns the correlation coefficient between two data sets.
=CORREL(A1:A10, B1:B10)
Returns the correlation between the values in A1:A10 and B1:B10.
97. COVARIANCE.P
Calculates the covariance of two data sets (population).
=COVARIANCE.P(A1:A10, B1:B10)
Returns the covariance of the data in A1:A10 and B1:B10.
98. COVARIANCE.S
Calculates the covariance of two data sets (sample).
=COVARIANCE.S(A1:A10, B1:B10)
Returns the sample covariance of the data in A1:A10 and B1:B10.
99. SKEW
Returns the skewness of a distribution (degree of asymmetry of the distribution).
=SKEW(A1:A10)
Calculates the skewness of the data in A1:A10.
100. KURT
Returns the kurtosis of a data set (the peakedness of the distribution).
=KURT(A1:A10)
Calculates the kurtosis of the data in A1:A10.
101. NORM.DIST
Returns the normal distribution for a specified mean and standard deviation.
=NORM.DIST(50, 40, 10, TRUE)
Returns the cumulative probability of a normal distribution with a mean of 40 and a standard deviation of 10, for the value 50.
102. NORM.INV
Returns the inverse of the normal cumulative distribution.
=NORM.INV(0.9, 40, 10)
Returns the value corresponding to the 90th percentile of a normal distribution with a mean of 40 and a standard deviation of 10.
103. T.DIST
Returns the Student’s t-distribution.
=T.DIST(1.5, 9, TRUE)
Returns the cumulative t-distribution for 1.5 with 9 degrees of freedom.
104. T.INV
Returns the inverse of the Student’s t-distribution.
=T.INV(0.9, 9)
Returns the t-value corresponding to the 90th percentile for a t-distribution with 9 degrees of freedom.