MS Excel: Worksheet Formulas - Listed by Category
Learn how to use all Excel worksheet formulas entered in the formula bar. 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.
Below is a list of Excel worksheet formulas sorted alphabetically. If you would like a list of these formulas sorted by category, click on the following button:
(Enter a value in the field above to quickly find functions in the list below)
ADDRESS (WS) | Returns a text representation of a cell address |
AREAS (WS) | Returns the number of ranges in a reference |
CHOOSE (WS) | 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) |
BAHTTEXT (WS) | Returns the number in Thai text |
CHAR (WS) | 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) | 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 |
LEFT (WS) | Extract a substring from a string, starting from the left-most character |
LEN (WS) | Returns the length of the specified string |
LOWER (WS) | Converts all letters in the specified string to lowercase |
MID (WS) | 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 |
REPT (WS) | Returns a repeated text value a specified number of times |
RIGHT (WS) | Extracts a substring from a string starting from the right-most character |
SEARCH (WS) | Returns the location of a substring in a string |
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) | Returns a text value with the leading and trailing spaces removed |
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 |
VALUE (WS) | Converts a text value that represents a number to a number |
DATE (WS) | Returns the serial date value for a date |
DATEDIF (WS) | Returns the difference between two date values, based on the interval specified |
DATEVALUE (WS) | Returns the serial number of a date |
DAY (WS) | 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 |
HOUR (WS) | 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) | Returns the minutes (a number from 0 to 59) from a time value |
MONTH (WS) | Returns the month (a number from 1 to 12) given a date value |
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) | 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 |
TIMEVALUE (WS) | Returns the serial number of a time |
TODAY (WS) | Returns the current system date |
WEEKDAY (WS) | Returns a number representing the day of the week, given a date value |
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) | 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) | 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 |
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) | 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) | Returns e raised to the nth power |
FACT (WS) | Returns the factorial of a number |
FLOOR (WS) | Returns a number rounded down based on a multiple of significance |
INT (WS) | 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 |
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 |
ROMAN (WS) | Converts a number to roman numeral |
ROUND (WS) | 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 |
SIGN (WS) | Returns the sign of a number |
SIN (WS) | Returns the sine of an angle |
SINH (WS) | Returns the hyperbolic sine 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) | 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 |
FALSE (WS) | Returns a logical value of FALSE |
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 |
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 |
SWITCH (WS) | Compares an expression to a list of values and returns the corresponding result |
TRUE (WS) | Returns a logical value of TRUE |
CELL (WS) | Used to retrieve information about a cell such as contents, formatting, size, etc. |
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 |
ISERR (WS) | Used to check for error values except #N/A |
ISERROR (WS) | 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 |
ISNUMBER (WS) | 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) | Returns the depreciation of an asset based on the double-declining balance method |
FV (WS) | Returns the future value of an investment |
IPMT (WS) | Returns the interest payment for an investment |
IRR (WS) | Returns the internal rate of return for a series of cash flows |
ISPMT (WS) | Returns the interest payment for an investment |
MIRR (WS) | Returns the modified internal rate of return for a series of cash flows |
NPER (WS) | Returns the number of periods for an investment |
NPV (WS) | Returns the net present value of an investment |
PMT (WS) | Returns the payment amount for a loan |
PPMT (WS) | Returns the payment on the principal for a particular payment |
PV (WS) | Returns the present value of an investment |
RATE (WS) | Returns the interest rate for an annuity |
SLN (WS) | Returns the depreciation of an asset based on the straight-line depreciation method |
SYD (WS) | 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 |
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 words
Convert number into words
Insert a double quote in a formula
Test a string for an alphanumeric value
How to Autonumber in Excel
Delimit values with ASCII 29 character
Creating a custom round function
Creating a custom average function (that excludes outlyers from the calculation)
Advertisements