Read Time:20 Minute, 36 Second

**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)