Excel-Ur-Data

Here, you will find 200+ Excel Functions to help you in your daily tasks. For a more detailed tutorial, with formulas and examples, click on any of the Excel Functions below.

Note: The Minimum versions indicate which version of Excel a function was introduced. This means that these functions are not available in earlier versions.

Excel Functions – DATE Functions

Excel Functions	Minimum Version	Description
DATE		Excel DATE function creates a date by returning a serial number that represents the date specified.
DATEDIF		The DATEDIF function calculates the number of years, months or days between two specified dates. For example, to calculate age.
DATEVALUE		The Excel DATEVALUE function converts a date sored in text format to a valid date.
DAY		The DAY function extracts the day from a date and returns the specified value between 0 and 31.
DAYS	Office 2013	The DAYS function returns the number of days between two dates.
DAYS360		The DAYS360 calculates the number of days between two dates based on a 360-day year.
EDATE		Excel EDATE function adds a specified number of months to a date and returns the serial number of the date.
EMONTH		EMONTH function returns the serial number of the last day of the month after adding or deducting a specified number of months.
ISOWEEKNUM	Office 2013	The ISOWEEKNUM function returns the number of the ISO standard week number of the year for a given date.
MONTH		The MONTH extracts the month from a specified date and returns a number between 1 and 12.
NETWORKDAYS		NETWORKDAYS function gets the number of working days (excluding Saturdays and Sunday) between two specified dates. It does not count weekends and any date specified as holiday.
NETWORKDAYS.INTL	Office 2010	The NETWORKDAYS.INTL function counts the number of working days between two dates. It allows you to specify any day of the week as weekend. For example, Friday and Saturday, or only Sunday.
NOW		The NOW function returns a serial number that represents the current date and time value.
TODAY		Excel TODAY function returns a serial number that represents the current date.
WEEKDAY		The WEEKDAY function gets the day of the week of a specified date. It returns a number between 1 and 7 that represents the corresponding day of the week.
WEEKNUM		The WEEKNUM function returns a week number (1 – 54) that corresponds to the week of the year.
WORKDAY		The WORKDAY function is used to get the date after a given number of working days. It does not count weekends which by default, is Saturday and Sunday.
WORKDAY.INTL		The WORKDAY.INTL function gets the date after a given number of working days. It allows you to specify any day, other than Saturday or Sunday as weekend.
YEARFRAC		YEARFRAC function calculates the precise difference between two dates and returns a number in decimal values like 1.3 years or 1.25years.

Excel Functions – TIME Functions

Excel Functions – TEXT Functions

CHAR		CHAR function returns a character specified by the code number.
CLEAN		The CLEAN function removes non-printable characters and line breaks from a text.
CODE		Excel CODE function returns the numeric code of a specified character or the first character in a given text string.
CONCAT	Office 2019	The Excel CONCAT function combines text from multiple strings and cell ranges. This function replaces the CONCATENATE function.
CONCATENATE		The CONCATENATE function combines two or more text strings together. It can be used to join texts, numbers, cell references or a combination of these.
DOLLAR		The DOLLAR function converts a number to text using a currency format.
EXACT		The EXACT function compares two strings and returns a TRUE if they are an exact match.
FIND		The FIND function locates the position of a substring inside another text string and returns a number that represents the starting position of the string you are trying to find. It is case-sensitive.
FIXED		The FIXED function rounds up a given number to a specified number of decimal places and converts them into text format.
LEFT		The LEFT function extracts text from the left side of the string and returns the specified number of characters from the left side.
LEN		The LEN function gets the length (total number) of characters in a text string.
LOWER		The LOWER function converts all uppercase letters in a string to lowercase. It does not change Numbers, special characters and punctuations.
MID		The MID function extracts a specified number of characters from a text string starting from the position specified.
NUMBERVALUE	Office 2013	The NUMBERVALUE function converts a number in text format to a number by specifying the decimal and group separators.
PROPER		The PROPER function capitalizes only the first letter in a text value. It does not change Numbers, Special characters and punctuations.
REPLACE		The REPLACE function replaces a part of the text string with another string and returns a text string with the replaced text.
REPT		The REPT function repeats text a specified number of times.
RIGHT		The RIGHT function extracts text from the right side of the string and returns the specified number of characters from the right side of the string.
SEARCH		The SEARCH function looks for a text string inside another text string and returns a number of the starting position of the string you are searching for in another string. SEARCH function is not case-sensitive.
SUBSTITUTE		The SUBSTITUTE function substitutes an old text with a new specified text in a text string.
T		Excel T function returns a text when given a text value.
TEXT		The TEXT function is used to convert a number to text format.
TEXTJOIN	Office 2019	Excel TEXTJOIN function combines two or more strings together with a delimiter separating each value.
TRIM		The TRIM function removes double spaces from a text.
UNICHAR	Office 2013	Excel UNICHAR function returns the Unicode character of the Unicode number specified.
UNICODE	Office 2013	Excel UNICODE function returns the Unicode number of the first character of the text Specified.
UPPER		The UPPER function converts all lowercase letters in a text string to uppercase letters. It does not change Numbers, special characters and punctuations.

Excel Functions – LOGICAL Functions

AND		The AND function checks multiple conditions and returns TRUE only when specified conditions are true.
IF		The IF function evaluates a specific condition and returns a value if a specific condition is TRUE and another value if the condition is FALSE.
IFERROR		IFERROR function checks a formula in a cell for errors and returns the value specified.
IFNA	Office 2013	IFNA functions returns the specified formula of a value if it results to #N/A.
IFS	Office 2019	IFS function checks if one or more conditions are met and returns a value that corresponds to the TRUE condition.
NOT		The NOT function reverses the result of a logical argument to either a TRUE or FALSE.
OR		The OR function checks multiple conditions and returns a TRUE if the condition is true.
SWITCH	Office 2016	The SWITCH function evaluates an expression from a list of values and returns result corresponding to the first matching value.
XOR	Office 2013	The XOR function returns a logical exclusive OR of all arguments.
FALSE		The FALSE function does not take any input arguments and returns the logical value FALSE.
TRUE		TRUE function does not take any input arguments and returns the logical value TRUE.

Excel LOOKUP & REFERENCE Functions

ADDRESS		The ADDRESS function returns the address of a cell in a worksheet based on the specified row and column number. For example, ADDRESS(1,3) returns $C$1.
AREAS		The AREAS function returns the number of ranges in a reference.
CHOOSE		The CHOOSE function returns a value or reference from a list based on the index number specified. For example, =CHOOSE(3, “Orange”, “Banana”, “Pear”) returns “Pear” since it is the 3rd value in the list after the index number.
COLUMN		COLUMN function gets the column number of a specified cell reference.
COLUMNS		The COLOUMNS function returns a number that represents the total number of columns of a specified cell range.
FILTER	Office 365	The FILTER function allows you to quickly filter and extract data based on specific conditions or multiple conditions.
FORMULATEXT	Office 2013	The FORMULATEXT function returns a formula as a text string based on the specified cell reference.
GETPIVOTDATA		The GETPIVOTDATA function extracts data from your pivot table.
HLOOKUP		HLOOKUP function looks up for a certain value in a row or an array, and returns the  corresponding value from a different row in the same column.
HYPERLINK		The HYPERLINK function creates a shortcut that links you to a document stored on the internet, intranet or a network server.
INDEX		INDEX function fetches a value in a table using the position (row and column number). INDEX function is often used with MATCH function and is a preferable alternative to VLOOKUP function.
INDIRECT		The INDIRECT function gets the values of the references that are stored as text. It returns the reference specified by the text string.
LOOKUP		Excel LOOKUP function searches through a single column or row to find a specific value from the same place in a second column or row.
MATCH		The MATCH function locates the position of a lookup value in a row, column or table and returns a number that represents the position of the lookup value in the list.
OFFSET		The OFFSET function returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.
ROW		The ROW function gets the row number of a cell reference. For example, =ROW(A2) will return 2, which is the second row.
ROWS		The ROWS function returns a number that represents the total number of rows in the specified range.
SORT	Office 365	The SORT function sorts the contents of a cell range in ascending or descending order.
SORTBY	Office 365	SORTBY function sorts the contents of a cell range based on the values in another cell range.
TRANSPOSE		Excel TRANSPOSE function switches the arrangement of values in rows to columns or columns to rows.
UNIQUE	Office 365	UNIQUE function returns a list of unique values in a list or cell range.
VLOOKUP		VLOOKUP function looks up a value in a column and returns a matching value from a different column in the same row.
XLOOKUP	Office 365	XLOOKUP function searches for a value in a range or array and returns the corresponding value in a different row or column. It is an enhanced version of VLOOKUP/HLOOKUP and can perform most of the functions of the older lookup.
XMATCH	Office 365	The XMATCH function searches for a specified item in a cell range, and returns the position of the corresponding item.

Excel Functions – FINANCIAL Functions

ACCRINT		ACCRINT function calculates interest for a security that pays interest on a periodic basis.
ACCRINTM		The ACCRINTM function calculates the accrued interest for a security that pays interest on maturity.
AMORDEGRC		The AMORDEGRC function calculates the linear depreciation of an asset for each accounting period, on a pro-rata basis.
AMORLINC		AMORLINC function calculates depreciation of an asset for each accounting period, on a pro-rata basis.
CUMIPMT		CUMIPMT function calculates the cumulative interest paid on a loan.
CUMPRINC		CUMPRINC function calculates the cumulative principal paid on a loan.
DB		DB function calculates depreciation of an asset based on the fixed-declining balance method.
DDB		DDB function calculates depreciation of an asset based on double-declining balance method.
DISC		The DISC function calculates the rate of discount for a bond.
DOLLARDE		The DOLLARDE function concerts a dollar value from a fractional notation to a decimal notation.
DOLLARFR		The DOLLARFR function converts a dollar value from a decimal notation to a fractional notation.
DURATION		The DURATION function returns the duration of a security.
EFFECT		The EFFECT function calculates the annual interest rate and the number of compounding periods per year.
FV		The FV function calculates the future value of an investment.
FVSCHEDULE		The FVSCHEDULE calculates the future value of an investment with a variable interest rate.
INTRATE		The INTRATE function calculates the interest rate for a security.
IPMT		The IPMT function calculates the interest payment for an investment based on an interest rate and a constant payment schedule.
IRR		Excel IRR function calculates the Internal Rate of Return for a series of cashflows.
ISPMT		ISPMT function calculates the interest payment during a specific period of investment.
MIRR		The MIRR function calculates the modified internal rate of return for a series of cashflows that occur at “regular” intervals.
NPER		The NPER function returns the number of period for a loan or investment.
NPV		Excel NPV function calculates the Net Present Value of an investment using a discounted rate and a series of cashflows.
PDURATION		The PDURATION function calculates the time or specific number of periods required for an investment to reach a certain value.
PMT		Excel PMT function calculates the payment made on a loan based on the total loan amount, interest rate, and the number of constant payments.
PPMT		The PPMT function calculates the principal for an investment or a loan payment.
PV		The PV function calculates the present value of an investment.
RATE		The RATE function calculates the interest rate per period of an annuity.
RECEIVED		The RECEIVED function calculates the amount received at maturity for a fully invested security.
SLN		SLN function calculates the depreciation of an asset using the straight-line depreciation method.
SYD		The SYD function calculates the depreciation of an asset using the sum of years digits depreciation method.
VDB		The VDB function calculates depreciation of an asset using the variable declining balance depreciation method.
XIRR		The XIRR function calculates the internal rate of return for a series of cash flows that occur at “irregular” intervals.
XNPV		The XNPV function calculates the Net Present Value for a series of cashflows that may not be periodic.
YIELD		The YIELD function calculates the yield on a security that pays interest periodically.
YIELDDISC		The YIELDDISC function calculates the annual yield for a discounted security.
YIELDMAT		The YIELDMAT function calculates the annual yield of a security that pays interest at maturity.

Excel Functions – STATISTICS Functions

AVERAGE		The AVERAGE function calculates the average (arithmetic mean) of numbers in the specified cell range.
AVERAGEA		The AVERAGEA function calculates the average (arithmetic mean) of a group of specified numbers.
AVERAGEIF		The AVERAGEIF function calculates the average (arithmetic mean) of all the values in a cell range that meets a given condition / criteria.
AVERAGEIFS	Office 2019	The AVERAGEIFS function calculates the average (arithmetic mean) of all values in a cell range that meets multiple criteria.
CORREL		The CORREL function calculates the correlation coefficient between two cell ranges (variables or columns)
COUNT		The COUNT function count the number of cells that contains numbers.
COUNTA		COUNTA function is used to count all the cells in a range that are not blank/empty. It includes cells that contain numbers, text, error values, logical values and empty text(“”).
COUNTBLANK		The COUNTBLANK function counts the number of empty/blank cells in a range.
COUNTIF		COUNTIF function counts the number of cells within a range that meets specific criteria.
COUNTIFS	Office 2019	The COUNTIFS function is used to count the number of cells that meet a single or multiple criteria.
COVARIANCE.P	Office 2010	The COVARIANCE.P function calculates the population covariance of two specified sets of values.
COVARIANCE.S	Office 2010	The COVARIANCE.S function calculates the sample covariance of two sets of values specified by the user.
FREQUENCY		The FREQUENCY function calculates the frequency distribution of a specified range of values.
GEOMEAN		The GEOMEAN function returns the geometrical mean for a specified set of numerical values.
HARMEAN		The HARMEAN function returns the harmonic mean for a specified set of numerical values.
LARGE		The LARGE function gets the Kth largest value from a range of cells. For example, if you want to get the second largest value from a range of cells.
MAX		The MAX function gets the largest (Maximum) value in a set of values.
MAXA		The MAXA function returns the maximum value in a specified range of values.
MAXIFS	Office 2019	The MAXIFS function returns the maximum (largest) numeric value that meets one or more criteria in a range of values.
MEDIAN		The MEDIAN function returns the median (middle) value in a specified set of data.
MIN		The MIN function gets the smallest (Minimum) value in a set of values.
MINA		The MINA function returns the smallest value in a range of numeric values, text and logical values specified.
MINIFS	Office 2019	The MINIFS function returns the smallest numeric value in a range that meets a single or multiple criteria.
MODE.MULT	Office 2010	Excel MODE.MULT function returns a vertical array of the most frequently occurring numbers in a set of numbers.
MODE.SNGL	Office 2010	Excel MODE.SNGL function returns the number with the highest occurrence in the specified set of data.
RANK		RANK function ranks a number against a list of values and returns a number that represents the relative rank of number against the list of values.
SMALL		The SMALL function gets the Kth smallest value from a range of cells. For example, if you want to get the second smallest value from a range of cells.
STDEV.P	Office 2010	Excel STDEV.P function calculates the standard deviation for a set of data based on the entire population as specified in the argument.
STDEV.S	Office 2010	Excel STDEV.S function calculates the standard deviation for a set of data based on a sample of the population.
TRIMMEAN		The TRIMMEAN function calculates the mean (average) after excluding a percentage of data points (outliers) from the top and bottom tail of the dataset.
VAR.P	Office 2010	The Excel VAR.P function calculates the variance based on the entire population.
VAR.S	Office 2010	Excel VAR.S function calculates the sample variance of a specified set of values.

Excel Functions – MATH Functions

ABS		Excel ABS function returns the absolute number of a number. It converts a negative number to positive number, and leaves positive numbers unchanged.
AGGREGATE		Excel AGGREGATE function returns the aggregate calculation like AVERGAE, COUNT,  MAX, e.t.c
ARABIC		Excel ARABIC function converts a roman number to Arabic as a number.
BASE	Office 2013	The BASE function converts a number to a specified base (radix) and returns a text representation of the same value.
CEILING		The CEILING function rounds up a number to the nearest multiple of significance.
CEILING.MATH	Office 2013	CEILING.MATH function rounds up a number to the nearest integer or nearest multiple of significance.
CEILING.PRECISE		CEILING.PRECISE function rounds up a number to the nearest multiple of a specified number. It rounds up numbers regardless of the sign.
DECIMAL	Office 2013	DECIMAL function converts a text representation of a number in a given base into its equivalent decimal number.
EVEN		Excel EVEN function rounds up a number to the nearest even number.
EXP		The EXP (Exponential) function returns the value of constant ‘e’ (Euler’s number) raised to the power of a specified number.
FACT		The FACT function calculates the factorial of a specified number.
FLOOR		The FLOOR function rounds up a given number down towards zero, to the nearest specified multiple.
FLOOR.MATH	Office 2013	The FLOOR.MATH function rounds up a given number down to the nearest integer or nearest multiple of significance.
FLOOR.PRECISE		The FLOOR.PRECISE function rounds up a specified number down to the nearest integer or the nearest multiple of significance specified, regardless of any sign on the number.
GCD		The GCD function returns the greatest common divisor of two or more integers.
INT		The INT (Integer) functions rounds down a specified decimal number to the nearest integer.
LCM		The LCM function returns the lowest common multiple of two or more specified integers.
LN		The LN function returns the natural logarithm of a specified number.
LOG		The LOG function computes the logarithm of a given number based on the specified base.
LOG10		The LOG10 function returns the base 10 logarithm of a number.
MOD		The MOD function finds a remainder after one number is divided by another and returns the remainder of a division.
ODD		The ODD function rounds up a number to the nearest odd number.
PI		The PI function returns the value of the geometric constant pi.
POWER		The POWER function computes and returns the result of a number raised to a power.
PRODUCT		The PRODUCT function multiplies all the numbers specified as input arguments and returns the product.
QUOTIENT		The QUOTIENT function returns only the integer portion of a division without its remainder.
RAND		The RAND function  generates evenly distributed random numbers between 0 and 1.
RANDARRAY	Office 365	The RANDARRAY function generates an array of random numbers between 0 and 1 and allows you to specify the number of rows and columns to fill, the minimum and maximum values, and whether it should return whole numbers or decimal values.
RANDBETWEEN		The RANDBETWEEN function generates evenly distributed random numbers between the top and bottom range numbers specified.
ROMAN		The ROMAN function converts a number to a Roman numeral as text.
ROUND		The ROUND function rounds up a number to a specified number of digits.
ROUNDDOWN		The ROUNDDOWN function rounds down a number to a specified number of decimal places.
ROUNDUP		The ROUNDUP function rounds up a number to a given number of decimal places.
SEQUENCE	Office 365	The SEQUENCE function generates a list of sequential numbers in an array.
SIGN		The SIGN function tells you whether a number is positive or negative. It returns the sign (-1, 0 or +1).
SQRT		The SQRT function returns the square root of a positive number.
SUBTOTAL		The SUBTOTAL function returns the subtotal of numbers in a range of cells.
SUM		SUM function adds up all the values in a range of cells.
SUMIF		The SUMIF function adds up the values in a range of cells that meet a specified condition.
SUMIFS	Office 2019	The SUMIFS function adds up the values in a range of cells that meet the specified multiple conditions.
SUMPRODUCT		The SUMPRODUCT function multiplies range of cells or arrays together and returns the sum of products.
TRUNC		The TRUNC function truncates a number to a specified number of digits or decimal places.

Excel VBA Functions

VBA DIR		Excel VBA DIR function uses the path name to get the name of the a file or folder.
VBA INSTR		VBA INSTR function finds the position of a specified substring within the string and returns the first position of its occurrence.
VBA LCASE		VBA LCASE function takes a string as input argument and converts all uppercase characters to lowercase.
VBA MsgBox		Excel VBA MsgBox function is used to display a dialog box that shows a custom message or gets some basic inputs (such as Yes/No or OK/Cancel) purposely for informing your users.
VBA SPLIT		Excel VBA SPLIT function is used to split a text string based on the delimiter – a comma, tab, space or colon.
VBA TRIM		Excel VBA TRIM function removes all leading and trailing spaces from a text string. It is a useful VBA function for cleaning data.
VBA UCASE		VBA UCASE function takes a string as the input argument and converts all lowercase characters to uppercase.

Excel INFORMATION Functions

CELL		The CELL function returns information relating to the formatting, location or contents of a cell.
ERROR.TYPE		The ERROR.TYPE function returns a number that corresponds with the error values in Excel. It returns #N/A error if no error exists.
INFO		The INFO function returns information relating to the current operating environment.
ISBLANK		The ISBLANK function returns TRUE if a cell is blank or empty.
ISERR		The ISERR function returns TRUE for all error type except the #N/A error.
ISERROR		The ISERROR function identifies cells containing an error and returns TRUE.
ISEVEN		The ISEVEN function returns TRUE if a value is an even number.
ISFORMULA		The ISFORMULA function checks the cells that contains a formula and returns TRUE or FALSE.
ISLOGICAL		The ISLOGICAL function checks cells that contain logical values TRUE or FALSE and returns TRUE.
ISNA		The ISNA function checks if the value of a cell is the #N/A error and returns TRUE.
ISNONTEXT		The ISNONTEXT function returns TRUE if the value in a cell is not a text.
ISNUMBER		The ISNUMBER function returns TRUE if the value in cell is a number.
ISODD		The ISODD function returns TRUE if the value in a cell is an odd number.
ISREF		The ISREF function returns TRUE if a cell contains a reference.
ISTEXT		The ISTEXT function returns TRUE if values in cell is a text.
N		Excel N function converts a value to a number.
NA		The NA function returns the #N/A (“No value available”) error. You can use N/A to mark empty cells or include in a formula to display #N/A.
SHEET		The SHEET function returns the sheet number of the referenced sheet.
SHEETS		The SHEETS function returns the total number of sheets in a specified reference.
TYPE		Excel TYPE function returns a number that represents the type of data in the specified cell.

Excel DATABASE Functions

DAVERAGE		Excel DAVERAGE function calculates the average (Mean) of numbers in a column in a database based on specified criteria.
DCOUNT		Excel DCOUNT function counts the cells in a column list or database that contain values and meet a specified criteria.
DCOUNTA		The DCOUNTA function counts the number of non-blank cells in a column or database that meet specified criteria.
DGET		The DGET function extracts a single value from a record or database that matched the specified criteria.
DMAX		The DMAX function returns the maximum value from a column of records or database that matches the specified criteria.
DMIN		The DMIN function returns the minimum value from a column of records or database that matches specified criteria.
DPRODUCT		The DPRODUCT function multiplies the values in a column of records or database that matches the specified criteria.
DSTDEV		The DSTDEV function calculates the standard deviation based on a sample data extracted from records or database that meets the specified criteria.
DSTDEVP		The DSTDEVP function calculates the standard deviation based on the entire population of records in a column list or database, and the specified criteria.
DSUM		Excel DSUM function calculates the sum of values in a set of records that match the specified criteria.
DVAR		The DVAR function calculates the variance based on a sample from selected column or database that match the specified condition.
DVARP		The DVARP function calculates the variance based on the entire population of numbers in a column list or database that matches the specified condition.