Home » Excel-Built-In-Functions

This page lists the more commonly used Excel Functions. The list is continually being updated, so please bookmark this page, for future reference.

Click on any of the listed Excel functions for a full description, including examples of use, related tips & tricks and Common Errors. The functions are grouped by type below, or can be selected from the alphabetically ordered list on the above right.

Text Functions in Excel

Functions to Remove Extra Characters CLEAN Removes all non-printable characters from a supplied text string TRIM Removes duplicate spaces, and spaces at the start and end of a text string Functions to Convert Excel Data Types BAHTTEXT Converts a number, plus the suffix "Baht" into Thai text DOLLAR Converts a supplied number into text, using a currency format FIXED Rounds a supplied number to a specified number of decimal places, and then converts this into text TEXT Converts a supplied value into text, using a user-specified format VALUE Converts a text string into a numeric value Functions to Convert Between Upper & Lower Case LOWER Converts all characters in a supplied text string to lower case PROPER Converts all characters in a supplied text string to proper case (ie. the first character in each word is upper case and all other characters are lower case) UPPER Converts all characters in a supplied text string to upper case Converting Between Characters & Numeric Codes CHAR Returns the character that corresponds to a supplied numeric value CODE Returns the numeric code for the first character of a supplied string

Replacing / Substituting Parts of a Text String REPLACE Replaces all or part of a text string with another string (from a user supplied position) SUBSTITUTE Substitutes all occurrences of a search text string, within an original text string, with the supplied replacement text Cutting Up & Piecing Together Text Strings CONCATENATE Joins together two or more text strings LEFT Returns a specified number of characters from the start of a supplied text string MID Returns a specified number of characters from the middle of a supplied text string RIGHT Returns a specified number of characters from the end of a supplied text string REPT Returns a string consisting of a supplied text string, repeated a specified number of times Information Functions LEN Returns the length of a supplied text string FIND Returns the position of a supplied character or text string from within a supplied text string (case-sensitive) SEARCH Returns the position of a supplied character or text string from within a supplied text string (non-case-sensitive) EXACT Tests if two supplied text strings are exactly the same and if so, returns TRUE; Otherwise, returns FALSE. (case-sensitive) T Tests whether a supplied value is text and if so, returns the supplied text; If not, returns an empty text string.

Logical Functions in Excel

Boolean Operator Functions AND Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or FALSE otherwise OR Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE, or FALSE otherwise NOT Returns a logical value that is the opposite of a user supplied logical value or expression (ie. returns FALSE is the supplied argument is TRUE and returns TRUE if the supplied argument is FALSE)

Conditional Functions IF Tests a user-defined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE IFERROR Tests if an initial supplied value (or expression) returns an error, and if so the function returns a supplied value; Otherwise the function returns the initial value. (New in Excel 2007) Functions Returning Constant Values TRUE Simply returns the logical value TRUE FALSE Simply returns the logical value FALSE

Information Functions in Excel

Error Information Functions ISERROR Tests if an initial supplied value (or expression) returns an error and if so, returns TRUE; Otherwise returns FALSE ISERR Tests if an initial supplied value (or expression) returns an error (EXCEPT for the #N/A error) and if so, returns TRUE; Otherwise returns FALSE ISNA Tests if an initial supplied value (or expression) returns the Excel #N/A error and if so, returns TRUE; Otherwise returns FALSE ERROR.TYPE Tests a supplied value and returns an integer relating to the supplied value's error type Numerical Information Functions ISNUMBER Tests if a supplied value is a number, and if so, returns TRUE; Otherwise, returns FALSE. ISEVEN Tests if a supplied number (or expression) is an even number, and if so, returns TRUE; Otherwise, returns FALSE. ISODD Tests if a supplied number (or expression) is an odd number, and if so, returns TRUE; Otherwise, returns FALSE. N Converts a non-number value to a number, a date to a serial number, the logical value TRUE to 1 and all other values to 0

Other Data Type Functions ISBLANK Tests if a supplied cell is blank (empty), and if so, returns TRUE; Otherwise, returns FALSE ISLOGICAL Tests if a supplied value is a logical value, and if so, returns TRUE; Otherwise, returns FALSE ISTEXT Tests if a supplied value is text, and if so, returns TRUE; Otherwise, returns FALSE ISNONTEXT Tests if a supplied value is text, and if it is NOT, returns TRUE; Otherwise, returns FALSE ISREF Tests if a supplied value is a reference, and if so, returns TRUE; Otherwise, returns FALSE TYPE Returns information about the data type of a supplied value General Information Functions CELL Returns information about the contents, formatting or location of a given cell INFO Returns information about the the current operating environment Function Returning a Constant Value NA Returns the Excel #N/A error

Date & Time Functions in Excel

Before using the Date and Time Excel Functions, it is advised that you ensure you have a clear understanding of the way Excel stores Dates and Times - For details, see the Excel Dates and Times pages.

Creating Dates & Times DATE Returns a date, from a user-supplied year, month and day TIME Returns a time, from a user-supplied hour, minute and second DATEVALUE Converts a text string showing a date, to an integer that represents the date in Excel's date-time code TIMEVALUE Converts a text string showing a time, to a decimal that represents the time in Excel Current Date & Time NOW Returns the current date & time TODAY Returns today's date Extracting The Components of a Date DAY Returns the day (of the month) from a user-supplied date MONTH Returns the month from a user-supplied date YEAR Returns the year from a user-supplied date WEEKNUM Returns an integer representing the week number (from 1 to 53) of the year from a user-supplied date (New in Excel 2007) WEEKDAY Returns an integer representing the day of the week for a supplied date

Extracting The Components of a Time HOUR Returns the hour part of a user-supplied time MINUTE Returns the minute part of a user-supplied time SECOND Returns the seconds part of a user-supplied time Performing Calculations with Dates EDATE Returns a date that is the specified number of months before or after an initial supplied start date (New in Excel 2007) EOMONTH Returns a date that is the last day of the month which is a specified number of months before or after an initial supplied start date (New in Excel 2007) WORKDAY Returns a date that is a supplied number of working days (excluding weekends and holidays) ahead of a given start date (New in Excel 2007) DAYS360 Calculates the number of days between 2 dates, based on a 360-day year (12 x 30 months) NETWORKDAYS Returns the number of whole networkdays between two supplied dates (New in Excel 2007) YEARFRAC Calculates the fraction of the year represented by the number of whole days between two dates (New in Excel 2007)

Lookup & Reference Functions in Excel

Functions To Return References to Cell Ranges ADDRESS Returns a reference, in text format, for a supplied row and column number INDEX Returns a reference to a cell (or range of cells) for requested rows and columns within a supplied range INDIRECT Returns a cell or range reference that is represented by a supplied text string OFFSET Returns a reference to a range of cells that is a specified number of rows and columns from an initial supplied range Row / Column / Area Information ROW Returns the current row number of the spreadsheet COLUMN Returns the current column number of the spreadsheet ROWS Returns the number of rows in a supplied range COLUMNS Returns the number of columns in a supplied range AREAS Returns the number of areas in a supplied range

Data Lookup Functions HLOOKUP Looks up a supplied value in the first row of a table, and returns the corresponding value from another row VLOOKUP Looks up a supplied value in the first column of a table, and returns the corresponding value from another column LOOKUP Searches for a specific value in one data vector, and returns a value from the corresponding position of a second data vector GETPIVOTDATA Extracts data stored in a Pivot Table CHOOSE Returns one of a list of values, depending on the value of a supplied index number MATCH Finds the relative position of a value in a supplied array Other HYPERLINK Creates a hyperlink to a document in a supplied location. TRANSPOSE Performs a transpose transformation on a range of cells (ie. transforms a horizontal range of cells into a vertical range and vice versa) RTD Retrieves real-time data from a program that supports COM automation

Maths & Trig Functions in Excel

Basic Numeric Information ABS Returns the absolute value (ie. the modulus) of a supplied number SIGN Returns the sign (+1, -1 or 0) of a supplied number GCD Returns the Greatest Common Divisor of two or more supplied numbers (New in Excel 2007) LCM Returns the Least Common Multiple of two or more supplied numbers (New in Excel 2007) Basic Mathematical Operations SUM Returns the sum of a supplied list of numbers PRODUCT Returns the product of a supplied list of numbers POWER Returns the result of a given number raised to a supplied power SQRT Returns the positive square root of a given number QUOTIENT Returns the integer portion of a division between two supplied numbers (New in Excel 2007) MOD Returns the remainder from a division between two supplied numbers SUBTOTAL Performs a specified calculation (eg. the sum, product, average, etc.) for a supplied set of values Conditional Sums SUMIF Adds the cells in a supplied range, that satisfy a given criteria SUMIFS Adds the cells in a supplied range, that satisfy multiple criteria (New in Excel 2007) Rounding Functions CEILING Rounds a number away from zero (ie. rounds a positive number up and a negative number down), to a multiple of a given number EVEN Rounds a number away from zero (ie. rounds a positive number up and a negative number down), to the next even number FLOOR Rounds a number towards zero, (ie. rounds a positive number down and a negative number up), to a multiple of a given number INT Rounds a number down to the next integer MROUND Rounds a number up or down, to the nearest multiple of a given number (New in Excel 2007) ODD Rounds a number away from zero (ie. rounds a positive number up and a negative number down), to the next odd number ROUND Rounds a number up or down, to a given number of digits ROUNDDOWN Rounds a number towards zero, (ie. rounds a positive number down and a negative number up), to a given number of digits ROUNDUP Rounds a number away from zero (ie. rounds a positive number up and a negative number down), to a given number of digits TRUNC Truncates a number towards zero (ie. rounds a positive number down and a negative number up), to the next integer. Exponentials & Logarithms EXP Returns e raised to a given power LN Returns the natural logarithm of a given number LOG Returns the logarithm of a given number, to a specified base LOG10 Returns the base 10 logarithm of a given number

Advanced Mathematical Operations SUMPRODUCT Returns the sum of the products of corresponding values in two or more supplied arrays SUMSQ Returns the sum of the squares of a supplied list of numbers SUMX2MY2 Returns the sum of the difference of squares of corresponding values in two supplied arrays SUMX2PY2 Returns the sum of the sum of squares of corresponding values in two supplied arrays SUMXMY2 Returns the sum of squares of differences of corresponding values in two supplied arrays SERIESSUM Returns the sum of a power series (New in Excel 2007) Trigonometry Functions PI Returns the constant value of pi SQRTPI Returns the square root of a supplied number multiplied by pi (New in Excel 2007) DEGREES Converts Radians to Degrees RADIANS Converts Degrees to Radians COS Returns the Cosine of a given angle ACOS Returns the Arccosine of a number COSH Returns the hyperbolic cosine of a number ACOSH Returns the inverse hyperbolic cosine of a number SIN Returns the Sine of a given angle ASIN Returns the Arcsine of a number SINH Returns the Hyperbolic Sine of a number ASINH Returns the Inverse Hyperbolic Sine of a number TAN Returns the Tangent of a given angle ATAN Returns the Arctangent of a given number ATAN2 Returns the Arctangent of a given pair of x and y coordinates TANH Returns the Hyperbolic Tangent of a given number ATANH Returns the Inverse Hyperbolic Tangent of a given number Matrix Functions MDETERM Returns the matrix determinant of a supplied array MINVERSE Returns the matrix inverse of a supplied array MMULT Returns the matrix product of two supplied arrays Factorials FACT Returns the Factorial of a given number FACTDOUBLE Returns the Double Factorial of a given number (New in Excel 2007) MULTINOMIAL Returns the Multinomial of a given set of numbers (New in Excel 2007) Random Numbers RAND Returns a random number between 0 and 1 RANDBETWEEN Returns a random number between two given integers (New in Excel 2007) Miscellaneous COMBIN Returns the number of combinations for a given number of objects ROMAN Returns a text string depicting the roman numeral for a given number

Statistical Functions in Excel

Finding the Largest & Smallest Values MAX Returns the largest value from a list of supplied numbers MAXA Returns the largest value from a list of supplied values, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 MIN Returns the smallest value from a list of supplied numbers MINA Returns the smallest value from a list of supplied values, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 LARGE Returns the Kth LARGEST value from a list of supplied numbers, for a given value K SMALL Returns the Kth SMALLEST value from a list of supplied numbers, for a given value K Averages, Frequency & Rank AVERAGE Returns the Average of a list of supplied numbers AVERAGEA Returns the Average of a list of supplied numbers, counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 AVERAGEIF Calculates the Average of the cells in a supplied range, that satisfy a given criteria (New in Excel 2007) AVERAGEIFS Calculates the Average of the cells in a supplied range, that satisfy multiple criteria (New in Excel 2007) MEDIAN Returns the Median (the middle value) of a list of supplied numbers MODE Returns the Mode (the most frequently occurring value) of a list of supplied numbers GEOMEAN Returns the geometric mean of a set of supplied numbers HARMEAN Returns the harmonic mean of a set of supplied numbers TRIMMEAN Returns the mean of the interior of a supplied set of values FREQUENCY Returns an array showing the number of values from a supplied array, which fall into specified ranges RANK Returns the statistical rank of a given value, within a supplied array of values KURT Returns the kurtosis of a data set PERCENTILE Returns the K'th percentile of values in a supplied range for a given value of K PERCENTRANK Returns the percentage rank of a value in a data set QUARTILE Returns the quartile of a set of supplied numbers Deviation & Variance AVEDEV Returns the average of the absolute deviations of data points from their mean STDEV Returns the standard deviation of a supplied set of values (which represent a sample of a population) STDEVA Returns the standard deviation of a supplied set of values (which represent a sample of a population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 STDEVP Returns the standard deviation of a supplied set of values (which represent an entire population) STDEVPA Returns the standard deviation of a supplied set of values (which represent an entire population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 VAR Returns the variance of a supplied set of values (which represent a sample of a population) VARA Returns the variance of a supplied set of values (which represent a sample of a population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 VARP Returns the variance of a supplied set of values (which represent an entire population) VARPA Returns the variance of a supplied set of values (which represent an entire population), counting text and the logical value FALSE as the value 0 and counting the logical value TRUE as the value 1 COVAR Returns covariance (ie. the average of the products of deviations for each pair within two supplied data sets) CONFIDENCE Returns the confidence interval for a population mean DEVSQ Returns the sum of the squares of the deviations of a set of data points from their sample mean Miscellaneous PERMUT Returns the number of permutations for a given number of objects

Counting Cells COUNT Returns the number of numerical values in a supplied set of cells or values COUNTA Returns the number of non-blanks in a supplied set of cells or values COUNTBLANK Returns the number of blank cells in a supplied range COUNTIF Returns the number of cells (of a supplied range), that satisfy a given criteria COUNTIFS Returns the number of cells (of a supplied range), that satisfy a set of given criteria (New in Excel 2007) Trend Line Functions FORECAST Predicts a future point on a linear trend line fitted to a supplied set of x- and y- values INTERCEPT Calculates the best fit regression line, through a supplied series of x- and y- values and returns the value at which this line intercepts the y-axis LINEST Returns statistical information describing the trend of the line of best fit, through a supplied series of x- and y- values SLOPE Returns the slope of the linear regression line through a supplied series of x- and y- values TREND Calculates the trend line through a given set of y-values and returns additional y-values for a supplied set of new x-values GROWTH Returns numbers in a exponential growth trend, based on a set of supplied x- and y- values LOGEST Returns the parameters of an exponential trend for a supplied set of x- and y- values STEYX Returns the standard error of the predicted y-value for each x in the regression line for a set of supplied x- and y- values Distribution & Tests of Probability STANDARDIZE Returns a normalized value CORREL Returns the correlation coefficient between two sets of values FISHER Returns the Fisher transformation FISHERINV Returns the inverse of the Fisher transformation BETADIST Returns the cumulative beta probability density function BETAINV Returns inverse of the cumulative beta probability density function BINOMDIST Returns the individual term binomial distribution probability NEGBINOMDIST Returns the negative binomial distribution CRITBINOM Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value CHIDIST Returns the one-tailed probability of the chi-squared distribution CHIINV Returns the inverse of the one-tailed probability of the chi-squared distribution CHITEST Returns the chi-squared statistical test for independence EXPONDIST Returns the exponential distribution FDIST Returns the F probability distribution for two data sets FINV Returns the inverse of the F probability distribution for two data sets FTEST Returns the result of an F-Test for 2 supplied data sets GAMMADIST Returns the gamma distribution GAMMAINV Returns the inverse gamma cumulative distribution GAMMALN Calculates the natural logarithm of the gamma function for a supplied value HYPGEOMDIST Returns the hypergeometric distribution NORMDIST Returns the normal cumulative distribution NORMINV Returns the inverse of the normal cumulative distribution NORMSDIST Returns the standard normal cumulative distribution NORMSINV Returns the inverse of the standard normal cumulative distribution LOGNORMDIST Returns the cumulative lognormal distribution LOGINV Returns the inverse of the lognormal distribution PEARSON Returns the Pearson product moment correlation coefficient RSQ Returns the square of the Pearson product moment correlation coefficient POISSON Returns the Poisson distribution PROB Returns the probablity that values in a supplied range are within given limits SKEW Returns the skewness of a distribution TDIST Returns the Student's T-distribution TINV Returns the inverse of the Student's T-distribution TTEST Returns the probability associated with a Student's T-Test WEIBULL Returns the Weibull distribution ZTEST Returns the one-tailed probability value of a z-test

Engineering Functions in Excel

CONVERT

Converts a number from one unit type to another (New in Excel 2007)

Financial Functions in Excel

IRR	Calculates the internal rate of return for a series of cash flows
NPV	Calculates the net present value of an investment, based on a supplied discount rate, and a series of future payments and income.
PMT	Calculates the payments required to reduce a loan, from a supplied present value to a specified future value
RATE	Calculates the interest rate required to pay off a specified amount of a loan, or reach a target amount on an investment over a given period.

Excel Operators

Although the Excel Operators are not, strictly speaking, Excel Functions, a brief explanation of them is included in the following page:

Excel Operators

Excel Mathematical Operators - used to perform basic Mathematical operations, such as addition, subtraction, multiplication, division and raising to a power, OR the & Excel Operator is used to concatenate text strings

Although there are numerous Excel spreadsheet functions that have not yet been included in this page, more are being added all the time, so please check back here soon. Or visit the Microsoft Office Website (link opens in a new window), for a full list of all Excel functions.