1. SUM
Formula: =SUM(A1:A10)
Explanation: Adds all the numbers in a range.
Example: If cells A1 to A10 contain numbers 1 to 10, the formula returns 55.
2. AVERAGE
Formula: =AVERAGE(B1:B10)
Explanation: Calculates the average (arithmetic mean) of a range.
Example: If cells B1 to B10 contain numbers 10 to 20, the result is 15.
3. COUNT
Formula: =COUNT(C1:C10)
Explanation: Counts numeric entries in a range.
Example: If C1 to C10 contains numbers and text, it counts only numbers.
4. COUNTA
Formula: =COUNTA(D1:D10)
Explanation: Counts all non-empty cells.
Example: If D1 to D10 contains values or text in 5 cells, it returns 5.
5. IF
Formula: =IF(A1>10, "Yes", "No")
Explanation: Returns one value if a condition is TRUE, another if FALSE.
Example: If A1 = 12, the formula returns "Yes."
6. VLOOKUP
Formula: =VLOOKUP(1001, A1:D10, 2, FALSE)
Explanation: Looks for a value in the first column of a range and returns a value in the same row from a specified column.
Example: Finds "1001" in column A and returns its corresponding value from column B.
7. HLOOKUP
Formula: =HLOOKUP("Product", A1:D4, 2, FALSE)
Explanation: Searches for a value in the top row and returns a value from the specified row.
Example: Finds "Product" in row 1 and returns its value from row 2.
8. INDEX
Formula: =INDEX(A1:C10, 2, 3)
Explanation: Returns the value of a cell based on its row and column numbers.
Example: Returns the value in row 2, column 3 of the range A1:C10.
9. MATCH
Formula: =MATCH(20, A1:A10, 0)
Explanation: Returns the relative position of a value in a range.
Example: If "20" is in the 4th row of A1:A10, it returns 4.
10. CONCATENATE (or CONCAT)
Formula: =CONCATENATE(A1, " ", B1)
Explanation: Joins two or more strings into one.
Example: If A1 = "John" and B1 = "Doe," the formula returns "John Doe."
11. LEN
Formula: =LEN(A1)
Explanation: Returns the number of characters in a string.
Example: If A1 = "Excel", the result is 5.
12. TRIM
Formula: =TRIM(A1)
Explanation: Removes all extra spaces except single spaces between words.
Example: If A1 = " Excel Formula ", it returns "Excel Formula."
13. LEFT
Formula: =LEFT(A1, 5)
Explanation: Extracts the first n characters from a string.
Example: If A1 = "Microsoft Excel," it returns "Micro."
14. RIGHT
Formula: =RIGHT(A1, 5)
Explanation: Extracts the last n characters from a string.
Example: If A1 = "Microsoft Excel," it returns "Excel."
15. MID
Formula: =MID(A1, 2, 5)
Explanation: Returns n characters from the middle of a string, starting at a specified position.
Example: If A1 = "Microsoft," it returns "icros."
16. FIND
Formula: =FIND("s", A1)
Explanation: Returns the position of a substring within a string (case-sensitive).
Example: If A1 = "Microsoft Excel," it returns 6.
17. SUBSTITUTE
Formula: =SUBSTITUTE(A1, "Excel", "Formulas")
Explanation: Replaces occurrences of a specified text with another.
Example: If A1 = "Excel is great," it returns "Formulas is great."
18. ROUND
Formula: =ROUND(A1, 2)
Explanation: Rounds a number to a specified number of digits.
Example: If A1 = 123.4567, it returns 123.46.
19. NOW
Formula: =NOW()
Explanation: Returns the current date and time.
Example: If today is Jan 5, 2025, at 10:00 AM, it returns "01/05/2025 10:00."
20. TODAY
Formula: =TODAY()
Explanation: Returns the current date without the time.
Example: If today is Jan 5, 2025, it returns "01/05/2025."
21. PROPER
Formula: =PROPER(A1)
Explanation: Capitalizes the first letter of each word.
Example: If A1 = "hello world," it returns "Hello World."
22. UPPER
Formula: =UPPER(A1)
Explanation: Converts text to uppercase.
Example: If A1 = "excel", it returns "EXCEL."
23. LOWER
Formula: =LOWER(A1)
Explanation: Converts text to lowercase.
Example: If A1 = "Excel", it returns "excel."
24. PMT
Formula: =PMT(5%/12, 60, -10000)
Explanation: Calculates the payment for a loan based on constant payments and interest rates.
Example: For a loan of $10,000 at 5% annual interest over 5 years, it returns -$188.71.
25. IFERROR
Formula: =IFERROR(A1/B1, "Error")
Explanation: Returns a custom result if a formula results in an error.
Example: If A1 = 10 and B1 = 0, it returns "Error."
26. POWER
Formula: =POWER(A1, 2)
Explanation: Returns the result of a number raised to a power.
Example: If A1 = 5, the formula returns 52=255^2 = 25.
27. SQRT
Formula: =SQRT(A1)
Explanation: Returns the square root of a number.
Example: If A1 = 16, the formula returns 4.
28. MOD
Formula: =MOD(A1, 3)
Explanation: Returns the remainder after dividing a number by a divisor.
Example: If A1 = 10, the formula returns 1.
29. TEXT
Formula: =TEXT(A1, "DD/MM/YYYY")
Explanation: Converts a value into a text string with a specified format.
Example: If A1 = 01/05/2025, it returns "05/01/2025".
30. ABS
Formula: =ABS(A1)
Explanation: Returns the absolute value of a number.
Example: If A1 = -10, the formula returns 10.
31. LARGE
Formula: =LARGE(A1:A10, 2)
Explanation: Returns the nth largest value in a range.
Example: If A1:A10 contains {10, 20, 30}, it returns 20.
32. SMALL
Formula: =SMALL(A1:A10, 3)
Explanation: Returns the nth smallest value in a range.
Example: If A1:A10 contains {10, 20, 30}, it returns 30.
33. RANK
Formula: =RANK(A1, A1:A10)
Explanation: Returns the rank of a number within a range.
Example: If A1 = 20 and A1:A10 contains {10, 20, 30}, it returns 2.
34. CEILING
Formula: =CEILING(A1, 5)
Explanation: Rounds a number up to the nearest multiple of a specified value.
Example: If A1 = 23, the formula returns 25.
35. FLOOR
Formula: =FLOOR(A1, 5)
Explanation: Rounds a number down to the nearest multiple of a specified value.
Example: If A1 = 23, the formula returns 20.
36. TRANSPOSE
Formula: =TRANSPOSE(A1:A3)
Explanation: Converts a vertical range of cells to a horizontal range or vice versa.
Example: If A1:A3 contains {1, 2, 3}, it converts them to {1, 2, 3} horizontally.
37. UNIQUE
Formula: =UNIQUE(A1:A10)
Explanation: Returns unique values from a range.
Example: If A1:A10 contains {1, 2, 2, 3}, it returns {1, 2, 3}.
38. SORT
Formula: =SORT(A1:A10)
Explanation: Sorts a range in ascending or descending order.
Example: If A1:A10 contains {3, 1, 2}, it returns {1, 2, 3}.
39. FILTER
Formula: =FILTER(A1:A10, A1:A10>10)
Explanation: Filters a range based on criteria.
Example: If A1:A10 contains {5, 15, 20}, it returns {15, 20}.
40. CHOOSE
Formula: =CHOOSE(2, "Red", "Blue", "Green")
Explanation: Returns a value from a list based on its position.
Example: The formula returns "Blue."
41. ISNUMBER
Formula: =ISNUMBER(A1)
Explanation: Checks if a cell contains a numeric value.
Example: If A1 = 123, it returns TRUE.
42. ISTEXT
Formula: =ISTEXT(A1)
Explanation: Checks if a cell contains text.
Example: If A1 = "Excel," it returns TRUE.
43. RAND
Formula: =RAND()
Explanation: Returns a random number between 0 and 1.
Example: Returns a value like 0.5482.
44. RANDBETWEEN
Formula: =RANDBETWEEN(1, 100)
Explanation: Returns a random number between specified numbers.
Example: Returns a value like 45.
45. PRODUCT
Formula: =PRODUCT(A1:A5)
Explanation: Multiplies all the numbers in a range.
Example: If A1:A5 contains {1, 2, 3}, it returns 6.
46. SUBTOTAL
Formula: =SUBTOTAL(9, A1:A10)
Explanation: Returns a subtotal for a list of numbers.
Example: If A1:A10 contains {1, 2, 3}, it returns 6.
47. CONCAT
Formula: =CONCAT(A1:A3)
Explanation: Combines text from multiple ranges.
Example: If A1:A3 contains {"Hello", "World", "!"}, it returns "HelloWorld!".
48. REPT
Formula: =REPT("*", 5)
Explanation: Repeats text a specified number of times.
Example: Returns "*****."
49. TEXTJOIN
Formula: =TEXTJOIN(",", TRUE, A1:A3)
Explanation: Joins text values with a delimiter.
Example: If A1:A3 contains {"A", "B", "C"}, it returns "A,B,C."
50. SUBSTITUTE
Formula: =SUBSTITUTE(A1, "a", "e")
Explanation: Replaces occurrences of a specified character.
Example: If A1 = "Cat", it returns "Cet."
51. SEARCH
Formula: =SEARCH("o", A1)
Explanation: Finds the position of a substring within a string (case-insensitive).
Example: If A1 = "Microsoft", it returns 2 (the position of "o").
52. EXACT
Formula: =EXACT(A1, B1)
Explanation: Checks if two text strings are exactly the same, case-sensitive.
Example: If A1 = "Excel" and B1 = "excel," it returns FALSE.
53. REPLACE
Formula: =REPLACE(A1, 1, 5, "World")
Explanation: Replaces part of a text string with another string.
Example: If A1 = "Hello Excel," it returns "World Excel."
54. ISERROR
Formula: =ISERROR(A1/B1)
Explanation: Returns TRUE if the formula results in an error.
Example: If B1 = 0, it returns TRUE.
55. ISBLANK
Formula: =ISBLANK(A1)
Explanation: Checks if a cell is empty.
Example: If A1 is empty, it returns TRUE.
56. ROW
Formula: =ROW(A1)
Explanation: Returns the row number of a cell.
Example: If A1 is in row 1, it returns 1.
57. COLUMN
Formula: =COLUMN(B1)
Explanation: Returns the column number of a cell.
Example: If B1 is in column 2, it returns 2.
58. OFFSET
Formula: =OFFSET(A1, 1, 1)
Explanation: Returns the value of a cell offset by a specified number of rows and columns.
Example: If A1 = 10 and B2 = 20, it returns 20 when offset by 1 row and 1 column.
59. INDIRECT
Formula: =INDIRECT("A1")
Explanation: Returns the value of a cell specified by a text string.
Example: If A1 = 10, it returns 10.
60. HYPERLINK
Formula: =HYPERLINK("http://www.google.com", "Google")
Explanation: Creates a clickable link.
Example: Displays "Google" and links to Google.
61. TEXTSPLIT
Formula: =TEXTSPLIT(A1, ",")
Explanation: Splits a text string into multiple cells based on a delimiter.
Example: If A1 = "Apple,Banana,Cherry", it returns {Apple; Banana; Cherry}.
62. UNIQUE
Formula: =UNIQUE(A1:A10)
Explanation: Returns unique values from a range.
Example: If A1:A10 contains {1, 2, 2, 3}, it returns {1, 2, 3}.
63. SEQUENCE
Formula: =SEQUENCE(5)
Explanation: Generates a sequence of numbers.
Example: Returns {1; 2; 3; 4; 5} in a column.
64. FILTERXML
Formula: =FILTERXML("<data><item>A</item><item>B</item></data>", "//item")
Explanation: Extracts data from XML.
Example: Returns A and B.
65. UNIQUE
Formula: =UNIQUE(A1:A10)
Explanation: Returns a list of unique values.
Example: {1, 1, 2, 2, 3} becomes {1, 2, 3}.
66. FORECAST
Formula: =FORECAST(A1, B1:B10, C1:C10)
Explanation: Predicts a value based on existing values.
Example: If A1 is 2025, it forecasts a value for that year based on trends.
67. PERCENTILE
Formula: =PERCENTILE(A1:A10, 0.9)
Explanation: Returns the nth percentile of a data set.
Example: Finds the 90th percentile of A1:A10.
68. MEDIAN
Formula: =MEDIAN(A1:A10)
Explanation: Returns the median of a range.
Example: If A1:A10 contains {1, 2, 3, 4, 5}, it returns 3.
69. NETWORKDAYS
Formula: =NETWORKDAYS(A1, B1)
Explanation: Returns the number of workdays between two dates.
Example: From Jan 1 to Jan 10, excluding weekends, it returns 7.
70. WEEKDAY
Formula: =WEEKDAY(A1)
Explanation: Returns the day of the week as a number.
Example: If A1 = Jan 5, 2025 (Sunday), it returns 1.
71. EOMONTH
Formula: =EOMONTH(A1, 1)
Explanation: Returns the last day of the month after a specified number of months.
Example: If A1 = Jan 15, 2025, it returns Feb 28, 2025.
72. WORKDAY
Formula: =WORKDAY(A1, 10)
Explanation: Returns the date after a specified number of workdays.
Example: If A1 = Jan 1, 2025, it returns Jan 15, 2025.
73. XOR
Formula: =XOR(A1, B1)
Explanation: Returns TRUE if one, but not both, conditions are TRUE.
Example: If A1 = TRUE and B1 = FALSE, it returns TRUE.
74. LAMBDA
Formula: =LAMBDA(x, x^2)(5)
Explanation: Creates custom reusable functions.
Example: Returns 52=255^2 = 25.
75. BIN2DEC
Formula: =BIN2DEC("1101")
Explanation: Converts a binary number to decimal.
Example: Returns 13.
76. DEC2BIN
Formula: =DEC2BIN(13)
Explanation: Converts a decimal number to binary.
Example: Returns "1101."
77. DAYS
Formula: =DAYS(B1, A1)
Explanation: Returns the number of days between two dates.
Example: If A1 = Jan 1, 2025, and B1 = Jan 10, 2025, it returns 9.
78. RANDARRAY
Formula: =RANDARRAY(5, 1, 1, 100, TRUE)
Explanation: Generates a random array of numbers.
Example: Produces 5 random integers between 1 and 100.
79. VALUE
Formula: =VALUE(A1)
Explanation: Converts a text string that looks like a number into a numeric value.
Example: If A1 = "123", it returns 123.
80. ROMAN
Formula: =ROMAN(A1)
Explanation: Converts an Arabic numeral to Roman numeral.
Example: If A1 = 2025, the formula returns "MMXXV."
81. CONCATENATE (deprecated, replaced by CONCAT)
Formula: =CONCATENATE(A1, " ", B1)
Explanation: Joins multiple text strings into one.
Example: If A1 = "Hello" and B1 = "World," it returns "Hello World."
82. IFERROR
Formula: =IFERROR(A1/B1, "Error")
Explanation: Returns a custom value if the formula results in an error; otherwise, it returns the result of the formula.
Example: If B1 = 0, it returns "Error."
83. DATEDIF
Formula: =DATEDIF(A1, B1, "Y")
Explanation: Calculates the difference between two dates in years, months, or days.
Example: If A1 = 01/01/2000 and B1 = 01/01/2025, it returns 25.
84. INFO
Formula: =INFO("directory")
Explanation: Returns information about the current operating environment.
Example: Displays the directory path of the active workbook.
85. FORMULATEXT
Formula: =FORMULATEXT(A1)
Explanation: Returns the formula in a cell as text.
Example: If A1 contains =SUM(B1:B5), it displays =SUM(B1:B5).
86. GETPIVOTDATA
Formula: =GETPIVOTDATA("Sales", A1)
Explanation: Extracts data from a pivot table.
Example: Returns the total sales from a pivot table at A1.
87. COLUMNS
Formula: =COLUMNS(A1:C1)
Explanation: Returns the number of columns in a range.
Example: For A1:C1, it returns 3.
88. ROWS
Formula: =ROWS(A1:A10)
Explanation: Returns the number of rows in a range.
Example: For A1:A10, it returns 10.
89. ISNA
Formula: =ISNA(A1)
Explanation: Returns TRUE if a cell contains the #N/A error.
Example: If A1 = #N/A, it returns TRUE.
90. DEC2HEX
Formula: =DEC2HEX(255)
Explanation: Converts a decimal number to a hexadecimal number.
Example: Returns "FF."
91. HEX2DEC
Formula: =HEX2DEC("FF")
Explanation: Converts a hexadecimal number to a decimal number.
Example: Returns 255.
92. HLOOKUP
Formula: =HLOOKUP("Score", A1:C10, 2, FALSE)
Explanation: Searches for a value in the first row of a range and returns a value in the same column from another row.
Example: If "Score" is in the first row and its value is in the second row, it returns that value.
93. T
Formula: =T(A1)
Explanation: Returns text if the value in a cell is text; otherwise, it returns an empty string.
Example: If A1 = "Hello," it returns "Hello."
94. EVEN
Formula: =EVEN(A1)
Explanation: Rounds a number up to the nearest even integer.
Example: If A1 = 3, it returns 4.
95. ODD
Formula: =ODD(A1)
Explanation: Rounds a number up to the nearest odd integer.
Example: If A1 = 2, it returns 3.
96. SUBSTITUTE
Formula: =SUBSTITUTE(A1, "apple", "orange")
Explanation: Replaces occurrences of a specified text with another.
Example: If A1 = "apple pie," it returns "orange pie."
97. CLEAN
Formula: =CLEAN(A1)
Explanation: Removes all non-printable characters from a text string.
Example: If A1 contains non-printable characters, it returns a cleaned version.
98. LEN
Formula: =LEN(A1)
Explanation: Returns the number of characters in a text string, including spaces.
Example: If A1 = "Excel," it returns 5.
99. TRIM
Formula: =TRIM(A1)
Explanation: Removes all extra spaces from text except for single spaces between words.
Example: If A1 = " Hello World ", it returns "Hello World."
100. ARRAYFORMULA (Google Sheets-specific)
Formula: =ARRAYFORMULA(A1:A5 * B1:B5)
Explanation: Applies a formula to an entire range without the need to copy it down.
Example: Multiplies each value in A1:A5 by the corresponding value in B1:B5.