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",
U1: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. CUMPRINC 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.