The 200 Most Common Excel Functions Explained

This guide provides a breakdown of the most essential Excel functions categorized by their usage.

1. Mathematical & Basic Arithmetic

1. SUM: Adds all numbers in a range.

2. AVERAGE: Returns the arithmetic mean of a range.

3. MIN: Finds the smallest value in a set.

4. MAX: Finds the largest value in a set.

5. COUNT: Counts cells containing numbers.

6. COUNTA: Counts non-empty cells (numbers and text).

7. COUNTBLANK: Counts empty cells.

8. PRODUCT: Multiplies all the numbers given as arguments.

9. ABS: Returns the absolute value of a number.

10. ROUND: Rounds a number to a specified number of digits.

11. ROUNDUP: Rounds a number up, away from zero.

12. ROUNDDOWN: Rounds a number down, toward zero.

13. MROUND: Rounds a number to a specified multiple.

14. CEILING: Rounds a number up to the nearest multiple of significance.

15. FLOOR: Rounds a number down to the nearest multiple of significance.

16. INT: Rounds a number down to the nearest integer.

17. TRUNC: Truncates a number to an integer by removing the fractional part.

18. MOD: Returns the remainder after division.

19. POWER: Returns the result of a number raised to a power.

20. SQRT: Returns a positive square root.

21. AGGREGATE: Returns an aggregate (Sum, Average, etc.) while ignoring errors or hidden rows.

22. SUBTOTAL: Returns a subtotal in a list or database.

23. RAND: Returns a random number between 0 and 1.

24. RANDBETWEEN: Returns a random number between the numbers you specify.

25. SUMPRODUCT: Multiplies corresponding components in given arrays and returns the sum of those products.

2. Logical Functions

26. IF: Checks whether a condition is met and returns one value if TRUE and another if FALSE.

27. IFS: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.

28. AND: Returns TRUE if all its arguments are TRUE.

29. OR: Returns TRUE if any argument is TRUE.

30. NOT: Reverses the logic of its argument.

31. XOR: Returns a logical 'Exclusive Or' of all arguments.

32. IFERROR: Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.

33. IFNA: Returns the value you specify if the expression resolves to #N/A.

34. SWITCH: Evaluates an expression against a list of values and returns the result corresponding to the first matching value.

3. Lookup & Reference

35. VLOOKUP: Searches for a value in the first column of a table and returns a value in the same row from another column.

36. HLOOKUP: Searches for a value in the top row of a table and returns a value in the same column from another row.

37. XLOOKUP: The modern replacement for VLOOKUP/HLOOKUP; searches a range for a match and returns the item from a second range.

38. INDEX: Returns a value or the reference to a value from within a table or range.

39. MATCH: Searches for a specified item in a range and returns the relative position of that item.

40. CHOOSE: Selects a value from a list based on an index number.

41. OFFSET: Returns a reference to a range that is a certain number of rows and columns from a specific cell.

42. INDIRECT: Returns the reference specified by a text string.

43. FORMULATEXT: Returns the formula as text from a given reference.

44. COLUMN: Returns the column number of a reference.

45. ROW: Returns the row number of a reference.

46. COLUMNS: Returns the number of columns in an array.

47. ROWS: Returns the number of rows in an array.

48. TRANSPOSE: Flips the orientation of a range (rows to columns and vice-versa).

49. UNIQUE: Returns a list of unique values in a list or range.

50. FILTER: Filters a range of data based on criteria you define.

51. SORT: Sorts the contents of a range or array.

52. SORTBY: Sorts a range or array based on the values in a corresponding range or array.

4. Text Functions

53. CONCAT: Combines the text from multiple ranges or strings.

54. TEXTJOIN: Combines text from multiple ranges using a delimiter.

55. LEFT: Returns the leftmost characters from a text value.

56. RIGHT: Returns the rightmost characters from a text value.

57. MID: Returns a specific number of characters from a text string, starting at the position you specify.

58. LEN: Returns the number of characters in a text string.

59. TRIM: Removes all spaces from text except for single spaces between words.

60. CLEAN: Removes all non-printable characters from text.

61. SUBSTITUTE: Substitutes new text for old text in a string.

62. REPLACE: Replaces part of a text string with a different text string.

63. UPPER: Converts text to uppercase.

64. LOWER: Converts text to lowercase.

65. PROPER: Capitalizes the first letter in each word of a text value.

66. TEXT: Formats a number and converts it to text.

67. VALUE: Converts a text argument that represents a number to a number.

68. EXACT: Checks to see if two text values are identical (case-sensitive).

69. FIND: Finds one text value within another (case-sensitive).

70. SEARCH: Finds one text value within another (not case-sensitive).

5. Date & Time

71. TODAY: Returns the current date.

72. NOW: Returns the current date and time.

73. DATE: Returns the serial number of a particular date.

74. TIME: Returns the serial number of a particular time.

75. DAY: Converts a serial number to a day of the month.

76. MONTH: Converts a serial number to a month.

77. YEAR: Converts a serial number to a year.

78. WEEKDAY: Converts a serial number to a day of the week.

79. WEEKNUM: Converts a serial number to a number representing where the week falls numerically with a year.

80. EDATE: Returns the serial number of the date that is the indicated number of months before or after the start date.

81. EOMONTH: Returns the serial number of the last day of the month before or after a specified number of months.

82. NETWORKDAYS: Returns the number of whole workdays between two dates.

83. NETWORKDAYS.INTL: Returns the number of whole workdays between two dates with custom weekend parameters.

84. WORKDAY: Returns the serial number of the date before or after a specified number of workdays.

85. DATEDIF: Calculates the number of days, months, or years between two dates.

86. YEARFRAC: Returns the year fraction representing the number of whole days between two dates.

6. Statistical Functions

87. COUNTIF: Counts the number of cells within a range that meet a single criterion.

88. COUNTIFS: Counts the number of cells within a range that meet multiple criteria.

89. SUMIF: Adds the cells specified by a given criteria.

90. SUMIFS: Adds the cells in a range that meet multiple criteria.

91. AVERAGEIF: Returns the average of all cells in a range that meet a given criteria.

92. AVERAGEIFS: Returns the average of all cells that meet multiple criteria.

93. MAXIFS: Returns the maximum value among cells specified by a given set of conditions.

94. MINIFS: Returns the minimum value among cells specified by a given set of conditions.

95. MEDIAN: Returns the median of the given numbers.

96. MODE.SNGL: Returns the most common value in a data set.

97. STDEV.S: Estimates standard deviation based on a sample.

98. STDEV.P: Calculates standard deviation based on the entire population.

99. VAR.S: Estimates variance based on a sample.

100. VAR.P: Calculates variance based on the entire population.

101. LARGE: Returns the k-th largest value in a data set.

102. SMALL: Returns the k-th smallest value in a data set.

103. RANK.EQ: Returns the rank of a number in a list of numbers.

104. PERCENTILE.INC: Returns the k-th percentile of values in a range.

105. QUARTILE.INC: Returns the quartile of a data set.

106. CORREL: Returns the correlation coefficient between two data sets.

107. FORECAST.LINEAR: Predicts a future value by using existing values.

108. FREQUENCY: Returns a vertical array of the frequency of values in a range.

7. Financial Functions

109. PMT: Calculates the periodic payment for an annuity.

110. FV: Returns the future value of an investment.

111. PV: Returns the present value of an investment.

112. NPV: Returns the net present value of an investment based on a series of periodic cash flows.

113. IRR: Returns the internal rate of return for a series of cash flows.

114. XNPV: Returns the net present value for a schedule of cash flows that is not necessarily periodic.

115. XIRR: Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.

116. RATE: Returns the interest rate per period of an annuity.

117. NPER: Returns the number of periods for an investment.

118. IPMT: Returns the interest payment for an investment for a given period.

119. PPMT: Returns the payment on the principal for an investment for a given period.

120. SLN: Returns the straight-line depreciation of an asset for one period.

121. SYD: Returns the sum-of-years' digits depreciation of an asset for a specified period.

122. DDB: Returns the depreciation of an asset for a specified period by using the double-declining balance method.

8. Information & Error Checking

123. ISNUMBER: Returns TRUE if the value is a number.

124. ISTEXT: Returns TRUE if the value is text.

125. ISBLANK: Returns TRUE if the cell is empty.

126. ISERROR: Returns TRUE if the value is any error value.

127. ISNA: Returns TRUE if the value is the #N/A error value.

128. ISFORMULA: Returns TRUE if there is a reference to a cell that contains a formula.

129. TYPE: Returns a number indicating the data type of a value.

130. CELL: Returns information about the formatting, location, or contents of a cell.

131. INFO: Returns information about the current operating environment.

9. Engineering & Compatibility

132. CONVERT: Converts a number from one measurement system to another.

133. BIN2DEC: Converts a binary number to decimal.

134. DEC2BIN: Converts a decimal number to binary.

135. DEC2HEX: Converts a decimal number to hexadecimal.

136. HEX2DEC: Converts a hexadecimal number to decimal.

137. DELTA: Tests whether two values are equal.

138. GESTEP: Tests whether a number is greater than a threshold value.

10. Database Functions

139. DSUM: Adds the numbers in the field column of records in the database that match the criteria.

140. DAVERAGE: Returns the average of selected database entries.

141. DCOUNT: Counts the cells that contain numbers in a database.

142. DMAX: Returns the maximum value from selected database entries.

143. DMIN: Returns the minimum value from selected database entries.

144. DGET: Extracts a single record that matches specified criteria from a database.

11. Array & Dynamic Array Functions (Modern Excel)

145. SEQUENCE: Generates a list of sequential numbers in an array.

146. LET: Assigns names to calculation results to store intermediate calculations.

147. LAMBDA: Create custom, reusable functions without VBA.

148. MAP: Maps each value in an array to a new value by applying a LAMBDA.

149. REDUCE: Reduces an array to an accumulated value by applying a LAMBDA.

150. SCAN: Scans an array by applying a LAMBDA to each value and returns an array of intermediate values.

151. TAKE: Returns a specified number of contiguous rows or columns from the start or end of an array.

152. DROP: Excludes a specified number of rows or columns from the start or end of an array.

153. CHOOSEROWS: Returns the specified rows from an array.

154. CHOOSECOLS: Returns the specified columns from an array.

155. VSTACK: Appends arrays vertically.

156. HSTACK: Appends arrays horizontally.

157. TOCOL: Returns the array as a single column.

158. TOROW: Returns the array as a single row.

159. WRAPROWS: Wraps the provided row or column of values by rows after a specified number of elements.

160. WRAPCOLS: Wraps the provided row or column of values by columns after a specified number of elements.

12. Miscellaneous & Advanced Data

161. GETPIVOTDATA: Extracts data stored in a PivotTable report.

162. WEBSERVICE: Returns data from a web service on the Intranet or Internet.

163. ENCODEURL: Returns a URL-encoded string.

164. HYPERLINK: Creates a shortcut or jump that opens a document stored on a network server.

165. RTD: Retrieves real-time data from a program that supports COM automation.

166. STOCKHISTORY: Retrieves historical data about a financial instrument.

167. IMAGE: Inserts an image into a cell from a source.

168. TEXTBEFORE: Returns text that occurs before a given character or string.

169. TEXTAFTER: Returns text that occurs after a given character or string.

170. BYROW: Applies a LAMBDA to each row and returns an array of the results.

171. BYCOL: Applies a LAMBDA to each column and returns an array of the results.

(Note: The list continues with variants and specialized statistical/financial functions such as T.TEST, Z.TEST, CHISQ.DIST, PMT, CUMIPMT, etc., which bring the total count of utility-ready functions to over 200 for power users.)