MS Excel: Formulas and Functions: Listed by Category

MS Excel: Formulas and Functions: Listed by Category

Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual Basic for Applications (VBA).

Below is a list of Excel formulas sorted by category.

(Enter a value in the field above to quickly find functions in the list below)

A. Lookup/Ref Functions

1. ADDRESS (WS): Returns a text representation of a cell address
2. AREAS (WS): Returns the number of ranges in a reference
3. CHOOSE (WS, VBA): Returns a value from a list of values based on a given position
4. COLUMN (WS): Returns the column number of a cell reference
5. COLUMNS (WS): Returns the number of columns in a cell reference
6. HLOOKUP (WS): Performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index_number
7. HYPERLINK (WS): Creates a shortcut to a file or Internet address
8. INDEX (WS): Returns either the value or the reference to a value from a table or range
9. INDIRECT (WS): Returns the reference to a cell based on its string representation
10. LOOKUP (WS): Returns a value from a range (one row or one column) or from an array
11. MATCH (WS): Searches for a value in an array and returns the relative position of that item
12. OFFSET (WS): Returns a reference to a range that is offset a number of rows and columns
13. ROW (WS): Returns the row number of a cell reference
14. ROWS (WS): Returns the number of rows in a cell reference
15. TRANSPOSE (WS): Returns a transposed range of cells
16. VLOOKUP (WS): Performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position

B. String/Text Functions:

1. ASC (VBA): Returns ASCII value of a character
2. CHAR (WS): Returns the character based on the ASCII value
3. CHR (VBA): Returns the character based on the ASCII value
4. CLEAN (WS): Removes all nonprintable characters from a string
5. CODE (WS): Returns the ASCII value of a character or the first character in a cell
6. CONCAT (WS): Used to join 2 or more strings together
7. CONCATENATE (WS): Used to join 2 or more strings together (replaced by CONCAT Function)
8. CONCATENATE with & (WS, VBA): Used to join 2 or more strings together using the & operator
9. DOLLAR (WS): Converts a number to text, using a currency format
10. EXACT (WS): Compares two strings and returns TRUE if both values are the same
11. FIND (WS): Returns the location of a substring in a string (case-sensitive)
12. FIXED (WS): Returns a text representation of a number rounded to a specified number of decimal places
13. FORMAT STRINGS (VBA): Takes a string expression and returns it as a formatted string
14. INSTR (VBA): Returns the position of the first occurrence of a substring in a string
15. INSTRREV (VBA): Returns the position of the first occurrence of a string in another string, starting from the end of the string
16. LCASE (VBA): Converts a string to lowercase
17. LEFT (WS, VBA): Extract a substring from a string, starting from the left-most character
18. LEN (WS, VBA): Returns the length of the specified string
19. LOWER (WS): Converts all letters in the specified string to lowercase
20. LTRIM (VBA): Removes leading spaces from a string
21. MID (WS, VBA): Extracts a substring from a string (starting at any position)
22. PROPER (WS): Sets the first character in each word to uppercase and the rest to lowercase
23. REPLACE (WS): Replaces a sequence of characters in a string with another set of characters
24. REPLACE (VBA): Replaces a sequence of characters in a string with another set of characters
25. REPT (WS): Returns a repeated text value a specified number of times
26. RIGHT (WS, VBA): Extracts a substring from a string starting from the right-most character
27. RTRIM (VBA): Removes trailing spaces from a string
28. SEARCH (WS): Returns the location of a substring in a string
29. SPACE (VBA): Returns a string with a specified number of spaces
30. SPLIT (VBA): Used to split a string into substrings based on a delimiter
31. STR (VBA): Returns a string representation of a number
32. STRCOMP (VBA): Returns an integer value representing the result of a string comparison
33. STRCONV (VBA): Returns a string converted to uppercase, lowercase, proper case or Unicode
34. STRREVERSE (VBA): Returns a string whose characters are in reverse order
35. SUBSTITUTE (WS): Replaces a set of characters with another
36. T (WS): Returns the text referred to by a value
37. TEXT (WS): Returns a value converted to text with a specified format
38. TEXTJOIN (WS): Used to join 2 or more strings together separated by a delimiter
39. TRIM (WS, VBA): Returns a text value with the leading and trailing spaces removed
40. UCASE (VBA): Converts a string to all uppercase
41. UPPER (WS): Convert text to all uppercase
42. VAL (VBA): Returns the numbers found in a string
43. VALUE (WS): Converts a text value that represents a number to a number

C. Date/Time Functions:

1. DATE (WS): Returns the serial date value for a date
2. DATE (VBA): Returns the current system date
3. DATEADD (VBA): Returns a date after which a certain time/date interval has been added
4. DATEDIF (WS): Returns the difference between two date values, based on the interval specified
5. DATEDIFF (VBA): Returns the difference between two date values, based on the interval specified
6. DATEPART (VBA): Returns a specified part of a given date
7. DATESERIAL (VBA): Returns a date given a year, month, and day value
8. DATEVALUE (WS, VBA): Returns the serial number of a date
9. DAY (WS, VBA): Returns the day of the month (a number from 1 to 31) given a date value
10. DAYS (WS): Returns the number of days between 2 dates
11. DAYS360 (WS): Returns the number of days between two dates based on a 360-day year
12. EDATE (WS): Adds a specified number of months to a date and returns the result as a serial date
13. EOMONTH (WS): Calculates the last day of the month after adding a specified number of months to a date
14. FORMAT DATES (VBA): Takes a date expression and returns it as a formatted string
15. HOUR (WS, VBA): Returns the hours (a number from 0 to 23) from a time value
16. ISOWEEKNUM (WS): Returns the ISO week number for a date
17. MINUTE (WS, VBA): Returns the minutes (a number from 0 to 59) from a time value
18. MONTH (WS, VBA): Returns the month (a number from 1 to 12) given a date value
19. MONTHNAME (VBA): Returns a string representing the month given a number from 1 to 12
20. NETWORKDAYS (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
21. NETWORKDAYS.INTL (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
22. NOW (WS, VBA): Returns the current system date and time
23. SECOND (WS): Returns the seconds (a number from 0 to 59) from a time value
24. TIME (WS): Returns a decimal number given an hour, minute and second value
25. TIMESERIAL (VBA): Returns a time given an hour, minute, and second value
26. TIMEVALUE (WS, VBA): Returns the serial number of a time
27. TODAY (WS): Returns the current system date
28. WEEKDAY (WS, VBA): Returns a number representing the day of the week, given a date value
29. WEEKDAYNAME (VBA): Returns a string representing the day of the week given a number from 1 to 7
30. WEEKNUM (WS): Returns the week number for a date
31. WORKDAY (WS): Adds a specified number of work days to a date and returns the result as a serial date
32. WORKDAY.INTL (WS): Adds a specified number of work days to a date and returns the result as a serial date (customizable weekends)
33. YEAR (WS, VBA): Returns a four-digit year (a number from 1900 to 9999) given a date value
34. YEARFRAC (WS): Returns the number of days between 2 dates as a year fraction

D. Math/Trig Functions:

1. ABS (WS, VBA): Returns the absolute value of a number
2. ACOS (WS): Returns the arccosine (in radians) of a number
3. ACOSH (WS): Returns the inverse hyperbolic cosine of a number
4. AGGREGATE (WS): Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows
5. ASIN (WS): Returns the arcsine (in radians) of a number
6. ASINH (WS): Returns the inverse hyperbolic sine of a number
7. ATAN (WS): Returns the arctangent (in radians) of a number
8. ATAN2 (WS): Returns the arctangent (in radians) of (x,y) coordinates
9. ATANH (WS): Returns the inverse hyperbolic tangent of a number
10. ATN (VBA): Returns the arctangent of a number
11. CEILING (WS): Returns a number rounded up based on a multiple of significance
12. CEILING.PRECISE (WS): Returns a number rounded up to the nearest integer or to the nearest multiple of significance
13. COMBIN (WS): Returns the number of combinations for a specified number of items
14. COMBINA (WS): Returns the number of combinations for a specified number of items and includes repetitions
15. COS (WS, VBA): Returns the cosine of an angle
16. COSH (WS): Returns the hyperbolic cosine of a number
17. DEGREES (WS): Converts radians into degrees
18. EVEN (WS): Rounds a number up to the nearest even integer
19. EXP (WS, VBA): Returns e raised to the nth power
20. FACT (WS): Returns the factorial of a number
21. FIX (VBA): Returns the integer portion of a number
22. FLOOR (WS): Returns a number rounded down based on a multiple of significance
23. FORMAT NUMBERS (VBA): Takes a numeric expression and returns it as a formatted string
24. INT (WS, VBA): Returns the integer portion of a number
25. LN (WS): Returns the natural logarithm of a number
26. LOG (WS, VBA): Returns the logarithm of a number to a specified base
27. LOG10 (WS): Returns the base-10 logarithm of a number
28. MDETERM (WS): Returns the matrix determinant of an array
29. MINVERSE (WS): Returns the inverse matrix for a given matrix
30. MMULT (WS): Returns the matrix product of two arrays
31. MOD (WS): Returns the remainder after a number is divided by a divisor
32. ODD (WS): Rounds a number up to the nearest odd integer
33. PI (WS): Returns the mathematical constant called pi
34. POWER (WS): Returns the result of a number raised to a given power
35. PRODUCT (WS): Multiplies the numbers and returns the product
36. RADIANS (WS): Converts degrees into radians
37. RAND (WS): Returns a random number that is greater than or equal to 0 and less than 1
38. RANDBETWEEN (WS): Returns a random number that is between a bottom and top range
39. RANDOMIZE (VBA): Used to change the seed value used by the random number generator for the RND function
40. RND (VBA): Used to generate a random number (integer value)
41. ROMAN (WS): Converts a number to roman numeral
42. ROUND (WS): Returns a number rounded to a specified number of digits
43. ROUND (VBA): Returns a number rounded to a specified number of digits
44. ROUNDDOWN (WS): Returns a number rounded down to a specified number of digits
45. ROUNDUP (WS): Returns a number rounded up to a specified number of digits
46. SGN (VBA): Returns the sign of a number
47. SIGN (WS): Returns the sign of a number
48. SIN (WS, VBA): Returns the sine of an angle
49. SINH (WS): Returns the hyperbolic sine of a number
50. SQR (VBA): Returns the square root of a number
51. SQRT (WS): Returns the square root of a number
52. SUBTOTAL (WS): Returns the subtotal of the numbers in a column in a list or database
53. SUM (WS): Adds all numbers in a range of cells
54. SUMIF (WS): Adds all numbers in a range of cells based on one criteria
55. SUMIFS (WS): Adds all numbers in a range of cells, based on a single or multiple criteria
56. SUMPRODUCT (WS): Multiplies the corresponding items in the arrays and returns the sum of the results
57. SUMSQ (WS): Returns the sum of the squares of a series of values
58. SUMX2MY2 (WS): Returns the sum of the difference of squares between two arrays
59. SUMX2PY2 (WS): Returns the sum of the squares of corresponding items in the arrays
60. SUMXMY2 (WS): Returns the sum of the squares of the differences between corresponding items in the arrays
61. TAN (WS, VBA): Returns the tangent of an angle
62. TANH (WS): Returns the hyperbolic tangent of a number
63. TRUNC (WS): Returns a number truncated to a specified number of digits

E. Statistical Functions:

1. AVEDEV (WS): Returns the average of the absolute deviations of the numbers provided
2. AVERAGE (WS): Returns the average of the numbers provided
3. AVERAGEA (WS): Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0
4. AVERAGEIF (WS): Returns the average of all numbers in a range of cells, based on a given criteria
5. AVERAGEIFS (WS): Returns the average of all numbers in a range of cells, based on multiple criteria
6. BETA.DIST (WS): Returns the beta distribution
7. BETA.INV (WS): Returns the inverse of the cumulative beta probability density function
8. BETADIST (WS): Returns the cumulative beta probability density function
9. BETAINV (WS): Returns the inverse of the cumulative beta probability density function
10. BINOM.DIST (WS): Returns the individual term binomial distribution probability
11. BINOM.INV (WS): Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion
12. BINOMDIST (WS): Returns the individual term binomial distribution probability
13. CHIDIST (WS): Returns the one-tailed probability of the chi-squared distribution
14. CHIINV (WS): Returns the inverse of the one-tailed probability of the chi-squared distribution
15. CHITEST (WS): Returns the value from the chi-squared distribution
16. COUNT (WS): Counts the number of cells that contain numbers as well as the number of arguments that contain numbers
17. COUNTA (WS): Counts the number of cells that are not empty as well as the number of value arguments provided
18. COUNTBLANK (WS): Counts the number of empty cells in a range
19. COUNTIF (WS): Counts the number of cells in a range, that meets a given criteria
20. COUNTIFS (WS): Counts the number of cells in a range, that meets a single or multiple criteria
21. COVAR (WS): Returns the covariance, the average of the products of deviations for two data sets
22. FORECAST (WS): Returns a prediction of a future value based on existing values provided
23. FREQUENCY (WS): Returns how often values occur within a set of data. It returns a vertical array of numbers
24. GROWTH (WS): Returns the predicted exponential growth based on existing values provided
25. INTERCEPT (WS): Returns the y-axis intersection point of a line using x-axis values and y-axis values
26. LARGE (WS): Returns the nth largest value from a set of values
27. LINEST (WS): Uses the least squares method to calculate the statistics for a straight line and returns an array describing that line
28. MAX (WS): Returns the largest value from the numbers provided
29. MAXA (WS): Returns the largest value from the values provided (numbers, text and logical values)
30. MEDIAN (WS): Returns the median of the numbers provided
31. MIN (WS): Returns the smallest