Excel Tutorial

Excel Functions

Excel has many built-in functions which can be used in Worksheet and VBA. Worksheet functions are those built-in functions which can be used as part of a formula in a cell, whereas VBA functions are those built-in functions which can be used in Excel's programming environment called Visual Basic for Applications (VBA).

Below is the list of Excel functions, sorted by category. In the tables below, a function can be of type: WS (Worksheet function), VBA (VBA function) or both.

Search Excel Function:

(Enter a function name in the field above to quickly find it in the tables below)

Lookup and Reference Functions

Function	Type	Description
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)

String/Text Functions

Function	Type	Description
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 and Time Functions

Function	Type	Description
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

Math and Trigonometry Functions

Function	Type	Description
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 trigonometric cosine of an angle
COSH	WS	Returns the hyperbolic cosine of a number
COT	WS	Returns the trigonometric cotangent of an angle
CSC	WS	Returns the trigonometric cosecant of an angle
DEGREES	WS	Converts angles expressed in 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 angles expressed in 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
SEC	WS	Returns the trigonometric secant of an angle
SIN	WS, VBA	Returns the trigonometric 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 trigonometric 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

Statistical Functions

Function	Type	Description
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

Logical Functions

Function	Type	Description
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

Information Functions

Function	Type	Description
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

Financial Functions

Function	Type	Description
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

Database Functions

Function	Type	Description
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

Engineering Functions

Function	Type	Description
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

File/Directory Functions

Function	Type	Description
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

Data Type Conv. Functions

Function	Type	Description
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