ExcelFunctions.net Logo

Built-In Excel Functions

Home » Excel-Built-In-Functions



Alphabetically Ordered
Function List:

Search this site:
Custom Search

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.






Disclaimer Privacy Policy

Copyright © 2008-2009 ExcelFunctions.net