MS Excel: Formulas and Functions - Listed by Category
Excel formulas and functions including worksheet functions entered in the formula bar and VBA functions used in Macros.
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.
| 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 |
| XLOOKUP (WS) | Performs a lookup (either vertical or horizontal) |
| ASC (VBA) | Returns ASCII value of a character |
| BAHTTEXT (WS) | Returns the number in Thai text |
| 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) |
| NUMBERVALUE (WS) | Returns a text to a number specifying the decimal and group separators |
| 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 |
| UNICHAR (WS) | Returns the Unicode character based on the Unicode number provided |
| UNICODE (WS) | Returns the Unicode number of a character or the first character in a string |
| 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 |
| 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 |
| 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) | Returns the logarithm of a number to a specified base |
| LOG (VBA) | Returns the natural logarithm of a number |
| 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 |
| MOD (VBA) | 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 |
| 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) |
| MAXIFS (WS) | Returns the largest value in a range, that meets a single or multiple criteria |
| MEDIAN (WS) | Returns the median of the numbers provided |
| MIN (WS) | Returns the smallest value from the numbers provided |
| MINA (WS) | Returns the smallest value from the values provided (numbers, text and logical values) |
| MINIFS (WS) | Returns the smallest value in a range, that meets a single or multiple criteria |
| MODE (WS) | Returns most frequently occurring number |
| MODE.MULT (WS) | Returns a vertical array of the most frequently occurring numbers |
| MODE.SNGL (WS) | Returns most frequently occurring number |
| PERCENTILE (WS) | Returns the nth percentile from a set of values |
| PERCENTRANK (WS) | Returns the nth percentile from a set of values |
| PERMUT (WS) | Returns the number of permutations for a specified number of items |
| QUARTILE (WS) | Returns the quartile from a set of values |
| RANK (WS) | Returns the rank of a number within a set of numbers |
| SLOPE (WS) | Returns the slope of a regression line based on the data points identified by known_y_values and known_x_values |
| SMALL (WS) | Returns the nth smallest value from a set of values |
| STDEV (WS) | Returns the standard deviation of a population based on a sample of numbers |
| STDEVA (WS) | Returns the standard deviation of a population based on a sample of numbers, text, and logical values |
| STDEVP (WS) | Returns the standard deviation of a population based on an entire population of numbers |
| STDEVPA (WS) | Returns the standard deviation of a population based on an entire population of numbers, text, and logical values |
| VAR (WS) | Returns the variance of a population based on a sample of numbers |
| VARA (WS) | Returns the variance of a population based on a sample of numbers, text, and logical values |
| VARP (WS) | Returns the variance of a population based on an entire population of numbers |
| VARPA (WS) | Returns the variance of a population based on an entire population of numbers, text, and logical values |
| AND (WS) | Returns TRUE if all conditions are TRUE |
| AND (VBA) | Returns TRUE if all conditions are TRUE |
| CASE (VBA) | Has the functionality of an IF-THEN-ELSE statement |
| FALSE (WS) | Returns a logical value of FALSE |
| FOR...NEXT (VBA) | Used to create a FOR LOOP |
| IF (WS) | Returns one value if the condition is TRUE or another value if the condition is FALSE |
| IF (more than 7) (WS) | Nest more than 7 IF functions |
| IF (up to 7) (WS) | Nest up to 7 IF functions |
| IF-THEN-ELSE (VBA) | Returns a value if a specified condition evaluates to TRUE or another value if it evaluates to FALSE |
| IFERROR (WS) | Used to return an alternate value if a formula results in an error |
| IFNA (WS) | Used to return an alternate value if a formula results in #N/A error |
| IFS (WS) | Specify multiple IF conditions within 1 function |
| NOT (WS) | Returns the reversed logical value |
| OR (WS) | Returns TRUE if any of the conditions are TRUE |
| OR (VBA) | Returns TRUE if any of the conditions are TRUE |
| SWITCH (WS) | Compares an expression to a list of values and returns the corresponding result |
| SWITCH (VBA) | Evaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE |
| TRUE (WS) | Returns a logical value of TRUE |
| WHILE...WEND (VBA) | Used to create a WHILE LOOP |
| CELL (WS) | Used to retrieve information about a cell such as contents, formatting, size, etc. |
| ENVIRON (VBA) | Returns the value of an operating system environment variable |
| ERROR.TYPE (WS) | Returns the numeric representation of an Excel error |
| INFO (WS) | Returns information about the operating environment |
| ISBLANK (WS) | Used to check for blank or null values |
| ISDATE (VBA) | Returns TRUE if the expression is a valid date |
| ISEMPTY (VBA) | Used to check for blank cells or uninitialized variables |
| ISERR (WS) | Used to check for error values except #N/A |
| ISERROR (WS, VBA) | Used to check for error values |
| ISLOGICAL (WS) | Used to check for a logical value (TRUE or FALSE) |
| ISNA (WS) | Used to check for #N/A error |
| ISNONTEXT (WS) | Used to check for a value that is not text |
| ISNULL (VBA) | Used to check for a NULL value |
| ISNUMBER (WS) | Used to check for a numeric value |
| ISNUMERIC (VBA) | Used to check for a numeric value |
| ISREF (WS) | Used to check for a reference |
| ISTEXT (WS) | Used to check for a text value |
| N (WS) | Converts a value to a number |
| NA (WS) | Returns the #N/A error value |
| TYPE (WS) | Returns the type of a value |
| ACCRINT (WS) | Returns the accrued interest for a security that pays interest on a periodic basis |
| ACCRINTM (WS) | Returns the accrued interest for a security that pays interest at maturity |
| AMORDEGRC (WS) | Returns the linear depreciation of an asset for each accounting period, on a prorated basis |
| AMORLINC (WS) | Returns the depreciation of an asset for each accounting period, on a prorated basis |
| DB (WS) | Returns the depreciation of an asset based on the fixed-declining balance method |
| DDB (WS, VBA) | Returns the depreciation of an asset based on the double-declining balance method |
| FV (WS, VBA) | Returns the future value of an investment |
| IPMT (WS, VBA) | Returns the interest payment for an investment |
| IRR (WS, VBA) | Returns the internal rate of return for a series of cash flows |
| ISPMT (WS) | Returns the interest payment for an investment |
| MIRR (WS, VBA) | Returns the modified internal rate of return for a series of cash flows |
| NPER (WS, VBA) | Returns the number of periods for an investment |
| NPV (WS, VBA) | Returns the net present value of an investment |
| PMT (WS, VBA) | Returns the payment amount for a loan |
| PPMT (WS, VBA) | Returns the payment on the principal for a particular payment |
| PV (WS, VBA) | Returns the present value of an investment |
| RATE (WS, VBA) | Returns the interest rate for an annuity |
| SLN (WS, VBA) | Returns the depreciation of an asset based on the straight-line depreciation method |
| SYD (WS, VBA) | Returns the depreciation of an asset based on the sum-of-years' digits depreciation method |
| VDB (WS) | Returns the depreciation of an asset based on a variable declining balance depreciation method |
| XIRR (WS) | Returns the internal rate of return for a series of cash flows that may not be periodic |
| DAVERAGE (WS) | Averages all numbers in a column in a list or database, based on a given criteria |
| DCOUNT (WS) | Returns the number of cells in a column or database that contains numeric values and meets a given criteria |
| DCOUNTA (WS) | Returns the number of cells in a column or database that contains nonblank values and meets a given criteria |
| DGET (WS) | Retrieves from a database a single record that matches a given criteria |
| DMAX (WS) | Returns the largest number in a column in a list or database, based on a given criteria |
| DMIN (WS) | Returns the smallest number in a column in a list or database, based on a given criteria |
| DPRODUCT (WS) | Returns the product of the numbers in a column in a list or database, based on a given criteria |
| DSTDEV (WS) | Returns the standard deviation of a population based on a sample of numbers |
| DSTDEVP (WS) | Returns the standard deviation of a population based on the entire population of numbers |
| DSUM (WS) | Sums the numbers in a column or database that meets a given criteria |
| DVAR (WS) | Returns the variance of a population based on a sample of numbers |
| DVARP (WS) | Returns the variance of a population based on the entire population of numbers |
| BIN2DEC (WS) | Converts a binary number to a decimal number |
| BIN2HEX (WS) | Converts a binary number to a hexadecimal number |
| BIN2OCT (WS) | Converts a binary number to an octal number |
| COMPLEX (WS) | Converts coefficients (real and imaginary) into a complex number |
| CONVERT (WS) | Convert a number from one measurement unit to another measurement unit |
| CHDIR (VBA) | Used to change the current directory or folder |
| CHDRIVE (VBA) | Used to change the current drive |
| CURDIR (VBA) | Returns the current path |
| DIR (VBA) | Returns the first filename that matches the pathname and attributes specified |
| FILEDATETIME (VBA) | Returns the date and time of when a file was created or last modified |
| FILELEN (VBA) | Returns the size of a file in bytes |
| GETATTR (VBA) | Returns an integer that represents the attributes of a file, folder, or directory |
| MKDIR (VBA) | Used to create a new folder or directory |
| SETATTR (VBA) | Used to set the attributes of a file |
| CBOOL (VBA) | Converts a value to a boolean |
| CBYTE (VBA) | Converts a value to a byte (ie: number between 0 and 255) |
| CCUR (VBA) | Converts a value to currency |
| CDATE (VBA) | Converts a value to a date |
| CDBL (VBA) | Converts a value to a double |
| CDEC (VBA) | Converts a value to a decimal number |
| CINT (VBA) | Converts a value to an integer |
| CLNG (VBA) | Converts a value to a long integer |
| CSNG (VBA) | Converts a value to a single-precision number |
| CSTR (VBA) | Converts a value to a string |
| CVAR (VBA) | Converts a value to a variant |
More Lookup Functions
Two-Dimensional Lookup (Example #1)Two-Dimensional Lookup (Example #2)Two-Dimensional Lookup (Example #3)Two-Dimensional Lookup (Example #4)
Other
Convert currency into wordsConvert number into wordsInsert a double quote in a formulaTest a string for an alphanumeric valueHow to Autonumber in ExcelDelimit values with ASCII 29 characterCreating a custom round functionCreating a custom average function (that excludes outlyers from the calculation)
