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
Other