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

- ADDRESS (WS): Returns a text representation of a cell address
- AREAS (WS): Returns the number of ranges in a reference
- CHOOSE (WS, VBA): Returns a value from a list of values based on a given position
- COLUMN (WS): Returns the column number of a cell reference
- COLUMNS (WS): Returns the number of columns in a cell reference
- 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
- HYPERLINK (WS): Creates a shortcut to a file or Internet address
- INDEX (WS): Returns either the value or the reference to a value from a table or range
- INDIRECT (WS): Returns the reference to a cell based on its string representation
- LOOKUP (WS): Returns a value from a range (one row or one column) or from an array
- MATCH (WS): Searches for a value in an array and returns the relative position of that item
- OFFSET (WS): Returns a reference to a range that is offset a number of rows and columns
- ROW (WS): Returns the row number of a cell reference
- ROWS (WS): Returns the number of rows in a cell reference
- TRANSPOSE (WS): Returns a transposed range of cells
- 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:

- ASC (VBA): Returns ASCII value of a character
- CHAR (WS): Returns the character based on the ASCII value
- CHR (VBA): Returns the character based on the ASCII value
- CLEAN (WS): Removes all nonprintable characters from a string
- CODE (WS): Returns the ASCII value of a character or the first character in a cell
- CONCAT (WS): Used to join 2 or more strings together
- CONCATENATE (WS): Used to join 2 or more strings together (replaced by CONCAT Function)
- CONCATENATE with & (WS, VBA): Used to join 2 or more strings together using the & operator
- DOLLAR (WS): Converts a number to text, using a currency format
- EXACT (WS): Compares two strings and returns TRUE if both values are the same
- FIND (WS): Returns the location of a substring in a string (case-sensitive)
- FIXED (WS): Returns a text representation of a number rounded to a specified number of decimal places
- FORMAT STRINGS (VBA): Takes a string expression and returns it as a formatted string
- INSTR (VBA): Returns the position of the first occurrence of a substring in a string
- INSTRREV (VBA): Returns the position of the first occurrence of a string in another string, starting from the end of the string
- LCASE (VBA): Converts a string to lowercase
- LEFT (WS, VBA): Extract a substring from a string, starting from the left-most character
- LEN (WS, VBA): Returns the length of the specified string
- LOWER (WS): Converts all letters in the specified string to lowercase
- LTRIM (VBA): Removes leading spaces from a string
- MID (WS, VBA): Extracts a substring from a string (starting at any position)
- PROPER (WS): Sets the first character in each word to uppercase and the rest to lowercase
- REPLACE (WS): Replaces a sequence of characters in a string with another set of characters
- REPLACE (VBA): Replaces a sequence of characters in a string with another set of characters
- REPT (WS): Returns a repeated text value a specified number of times
- RIGHT (WS, VBA): Extracts a substring from a string starting from the right-most character
- RTRIM (VBA): Removes trailing spaces from a string
- SEARCH (WS): Returns the location of a substring in a string
- SPACE (VBA): Returns a string with a specified number of spaces
- SPLIT (VBA): Used to split a string into substrings based on a delimiter
- STR (VBA): Returns a string representation of a number
- STRCOMP (VBA): Returns an integer value representing the result of a string comparison
- STRCONV (VBA): Returns a string converted to uppercase, lowercase, proper case or Unicode
- STRREVERSE (VBA): Returns a string whose characters are in reverse order
- SUBSTITUTE (WS): Replaces a set of characters with another
- T (WS): Returns the text referred to by a value
- TEXT (WS): Returns a value converted to text with a specified format
- TEXTJOIN (WS): Used to join 2 or more strings together separated by a delimiter
- TRIM (WS, VBA): Returns a text value with the leading and trailing spaces removed
- UCASE (VBA): Converts a string to all uppercase
- UPPER (WS): Convert text to all uppercase
- VAL (VBA): Returns the numbers found in a string
- VALUE (WS): Converts a text value that represents a number to a number

C. Date/Time Functions:

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

D. Math/Trig Functions:

- ABS (WS, VBA): Returns the absolute value of a number
- ACOS (WS): Returns the arccosine (in radians) of a number
- ACOSH (WS): Returns the inverse hyperbolic cosine of a number
- AGGREGATE (WS): Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows
- ASIN (WS): Returns the arcsine (in radians) of a number
- ASINH (WS): Returns the inverse hyperbolic sine of a number
- ATAN (WS): Returns the arctangent (in radians) of a number
- ATAN2 (WS): Returns the arctangent (in radians) of (x,y) coordinates
- ATANH (WS): Returns the inverse hyperbolic tangent of a number
- ATN (VBA): Returns the arctangent of a number
- CEILING (WS): Returns a number rounded up based on a multiple of significance
- CEILING.PRECISE (WS): Returns a number rounded up to the nearest integer or to the nearest multiple of significance
- COMBIN (WS): Returns the number of combinations for a specified number of items
- COMBINA (WS): Returns the number of combinations for a specified number of items and includes repetitions
- COS (WS, VBA): Returns the cosine of an angle
- COSH (WS): Returns the hyperbolic cosine of a number
- DEGREES (WS): Converts radians into degrees
- EVEN (WS): Rounds a number up to the nearest even integer
- EXP (WS, VBA): Returns e raised to the nth power
- FACT (WS): Returns the factorial of a number
- FIX (VBA): Returns the integer portion of a number
- FLOOR (WS): Returns a number rounded down based on a multiple of significance
- FORMAT NUMBERS (VBA): Takes a numeric expression and returns it as a formatted string
- INT (WS, VBA): Returns the integer portion of a number
- LN (WS): Returns the natural logarithm of a number
- LOG (WS, VBA): Returns the logarithm of a number to a specified base
- LOG10 (WS): Returns the base-10 logarithm of a number
- MDETERM (WS): Returns the matrix determinant of an array
- MINVERSE (WS): Returns the inverse matrix for a given matrix
- MMULT (WS): Returns the matrix product of two arrays
- MOD (WS): Returns the remainder after a number is divided by a divisor
- ODD (WS): Rounds a number up to the nearest odd integer
- PI (WS): Returns the mathematical constant called pi
- POWER (WS): Returns the result of a number raised to a given power
- PRODUCT (WS): Multiplies the numbers and returns the product
- RADIANS (WS): Converts degrees into radians
- RAND (WS): Returns a random number that is greater than or equal to 0 and less than 1
- RANDBETWEEN (WS): Returns a random number that is between a bottom and top range
- RANDOMIZE (VBA): Used to change the seed value used by the random number generator for the RND function
- RND (VBA): Used to generate a random number (integer value)
- ROMAN (WS): Converts a number to roman numeral
- ROUND (WS): Returns a number rounded to a specified number of digits
- ROUND (VBA): Returns a number rounded to a specified number of digits
- ROUNDDOWN (WS): Returns a number rounded down to a specified number of digits
- ROUNDUP (WS): Returns a number rounded up to a specified number of digits
- SGN (VBA): Returns the sign of a number
- SIGN (WS): Returns the sign of a number
- SIN (WS, VBA): Returns the sine of an angle
- SINH (WS): Returns the hyperbolic sine of a number
- SQR (VBA): Returns the square root of a number
- SQRT (WS): Returns the square root of a number
- SUBTOTAL (WS): Returns the subtotal of the numbers in a column in a list or database
- SUM (WS): Adds all numbers in a range of cells
- SUMIF (WS): Adds all numbers in a range of cells based on one criteria
- SUMIFS (WS): Adds all numbers in a range of cells, based on a single or multiple criteria
- SUMPRODUCT (WS): Multiplies the corresponding items in the arrays and returns the sum of the results
- SUMSQ (WS): Returns the sum of the squares of a series of values
- SUMX2MY2 (WS): Returns the sum of the difference of squares between two arrays
- SUMX2PY2 (WS): Returns the sum of the squares of corresponding items in the arrays
- SUMXMY2 (WS): Returns the sum of the squares of the differences between corresponding items in the arrays
- TAN (WS, VBA): Returns the tangent of an angle
- TANH (WS): Returns the hyperbolic tangent of a number
- TRUNC (WS): Returns a number truncated to a specified number of digits

E. Statistical Functions:

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