Function
In Microsoft Excel, a function is a prebuilt formula that performs a specific calculation or task. Functions are designed to simplify and automate common calculations, saving you time and reducing the chances of errors in your spreadsheets.
Contents
ToggleExcel provides a wide range of functions to perform various tasks. Categories as below:
 Information Function
 Logical Function
 Date & Time Function
 Text Function
 Statistical Function
 Math & Trigonometry Function
 Lookup & Reference Function
 Financial Function
 Engineering Function
 Cube Function
 Compatibility Function
 Web Function
 Database Function
 Addin and Automation Function
Functions in Excel typically follow a specific syntax and consist of the function name, arguments enclosed in parentheses, and sometimes additional parameters or options.
Here are some common examples of Excel functions:
 SUM: Adds up a range of numbers.
Example: =SUM(A1:A5) adds the values in cells A1 through A5.  AVERAGE: Calculates the average of a range of numbers.
Example: =AVERAGE(B1:B10) calculates the average of values in cells B1 through B10.  CONCATENATE: Combines multiple text strings into one.
Example: =CONCATENATE(“Hello, “, “world!”) combines the two text strings into “Hello, world!”  IF: Performs a conditional test and returns different values based on the test result.
Example: =IF(A1 > 10, “Yes”, “No”) checks if the value in cell A1 is greater than 10 and returns “Yes” or “No” accordingly.  TODAY: Returns the current date.
Example: =TODAY() returns the current date.  VLOOKUP: Searches for a value in a table and returns a corresponding value from another column.
Example: =VLOOKUP(A1, B1:C10, 2, FALSE) searches for the value in cell A1 in the first column of the table B1:C10 and returns the value from the second column.
You can access Excel’s functions through the “Formulas” tab on the ribbon or by typing them directly into a cell. Excel also provides a Function Wizard to help you select and enter functions correctly.
By using Excel functions, you can perform complex calculations and data manipulations efficiently and accurately in your spreadsheets.
Difference between Function and Formula in Excel
In Microsoft Excel, functions and formulas are related but distinct concepts. They both involve performing calculations or operations on data within a spreadsheet, but they have key differences. For details, see the menu of Formulas.
List of some frequently used functions
Note:
Version indicate the version of Excel a function was introduced. These functions aren’t available in earlier versions.
Square brackets (“[ ]”) – it show the optional argument within the function. Means argument mention within square brackets is not compulsory.
Information Functions
Function  Version  Description  Syntax 

CELL  Returns information about the formatting, location, or contents of the first cell, according to the sheet's reading order, in a reference. Info_type = address, col, color, contents, filename, format, parentheses, prefix, protect, row, type, width.  =CELL(info_type,[reference])  
INFO  Returns information about the current operating environment The INFO function is not available in Excel Web App. type_text = "directory": Path of the current directory or folder. "numfile": Number of active worksheets in the open workbooks. "osversion": Current operating system version, as text. "recalc": Current recalculation mode; returns "Automatic" or "Manual". "release": Version of Microsoft Excel, as text. "system": Name of the operating environment:Macintosh = "mac", Windows = "pcdos" "origin": Returns the absolute cell reference of the top and leftmost cell visible in the window, based on the current scrolling position, as text prepended with "$A:". This value is intended for for Lotus 123 release 3.x compatibility. The actual value returned depends on the current reference style setting. Using D9 as an example, the return value would be: A1 reference style:"$A:$D$9". R1C1 reference style:"$A:R9C4"  =INFO(type_text)  
ERROR.TYPE  Returns a number corresponding to an error type. Error_val = #NULL!1; #DIV/0!2; #VALUE!3; #REF!4; #NAME?!5; #NUM!6; #N/A7; #GETTING_DATA8  =ERROR.TYPE(error_val)  
ISBLANK  Returns TRUE if the value is blank. Check whether a reference is to an empty cell.  =ISBLANK(value)  
ISERR  Returns TRUE if the value is any error value except #N/A. Check whether a value is an error (#value!, #ref!, #div/0!, #num!, #name?! or #null!) excluding #n/a.  =ISERR((value)  
ISERROR  Returns TRUE if the value is any error value, Check whether a value is an error (#value!, #ref!, #div/0!, #num!, #name?! or #null!, #n/a).  =ISERROR(value)  
ISEVEN  Returns TRUE if the number is even  =ISEVEN(number)  
ISFORMULA  2013  checks whether a reference is to a cell containing a formula, and returns TRUE or FALSE  =ISFORMULA(reference) 
ISLOGICAL  Returns TRUE if the value is a logical value (TRUE or FALSE)  =ISLOGICAL(value)  
ISNA  Returns TRUE if the value is the #N/A error value  =ISNA(value)  
ISNONTEXT  Returns TRUE if the value is not text (Blank cells are not text)  =ISNONTEXT(vauue)  
ISNUMBER  Returns TRUE if the value is a number  =ISNUMBER(value)  
ISODD  Returns TRUE if the number is odd  =ISODD(number)  
ISREF  Returns TRUE if the value is a reference  =ISREF(value)  
ISTEXT  Returns TRUE if the value is text  =ISTEXT(value)  
N  Returns a value converted to a number  =N(value)  
NA  Returns the error value #N/A (Value not available)  =NA()  
SHEET  2013  Description Returns the sheet number of the reference sheet  =SHEET([value]) 
SHEETS  2013  Returns the number of sheets in a reference.  =SHEETS([reference]) 
TYPE  Returns an integar representing the data type of a value; number=1; text2; logical value=4; error value=16; array=64.  =TYPE(value) 
Logical Functions
Function  Version  Description  Syntax 

AND  Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE (255 logics can be added).  =AND(logical1, [logical2], [logical3],………)  
OR  Checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returs FALSE only if all arguments are FALSE. (255 logics can be added)  =OR(logical1, [logical2], [logical3],………)  
XOR  Excel 2013  Returns a logical exclusive OR of all arguments. 1 to 254 conditions you want to test that can be either TRUE or FALSE, and can be logical values, arrays, or references.  =XOR(logical1, [logical2],…) 
TRUE  Returns the logical value TRUE. You can enter the value TRUE directly into cells and formulas without using this function  =TRUE()  
FALSE  Returns the logical value FALSE. You can also type the word FALSE directly onto the worksheet or into the formula, and Microsoft Excel interprets it as the logical value FALSE.  =FALSE()  
IF  Specifies a logical test to perform. Checks whether a condition is met, and return one value if TRUE, and another value if FALSE.  =IF(logical_test, [value_if_true], [value_if_false])  
IFS  Excel 2019  The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions. Please note that the IFS function allows you to test up to 127 different conditions.  =IFS([Something is True1, Value if True1, Something is True2, Value if True2, Something is True3, Value if True3) 
IFERROR  You can use the IFERROR function to trap and handle errors in a formula. Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.  =IFERROR(value, value_if_error)  
IFNA  Excel 2013  Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression.  =IFNA(value, value_if_na) 
LET  Excel  365  The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax. The function can process up to 126 name/value pairs.  =LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...]) 
NOT  Reverses the logic of its argument, Changes FALSE to TRUE or TRUE to FALSE  =NOT(logical)  
SWITCH  Excel 2016  The SWITCH function evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.  =SWITCH(Value to switch, Value to match1...[2126], Value to return if there's a match1...[2126], Value to return if there's no match) 
Date and Time Functions
Function  Version  Description  Syntax 

DATE  Returns the serial number of a particular date  =Date(year, Month, Day)  
DATEVALUE  Converts a date in the form of text to a serial number  =DATEVALUE(date_text)  
DAYS360  Calculates the number of days between two dates based on a 360day year  =DAYS360( start_date, end_date, [method]) 

EDATE  Returns the serial number of the date that is the indicated number of months before or after the start date  =EDATE(start_date, months)  
EOMONTH  Returns the serial number of the last day of the month before or after a specified number of months  =EOMONTH(start_date, months)  
HOUR  Converts a serial number to an hour, Returns the hours as a number from 0 (12:00 A.M.) to 23 (23:00 P.M.)  =HOUR(serial_number)  
MINUTE  Converts a serial number to a minute. Returns the minute, a number from 0 to 59  =MINUTE(serial_number)  
SECOND  Converts a serial number to a second. Returns the second, a number from 0 to 59  =SECOND(serial_number)  
TIME  Returns the serial number of a particular time. Converts hours, minutes & seconds given as a number to an excel serial number, formatted with a time format.  =TIME(hour, minute, second)  
TIMEVALUE  Converts a time in the form of text to a serial number  =TIMEVALUE(time_text)  
DAY  Converts a serial number to a day of the month  =DAY(serial_number)  
DAYS  2013  Returns the number of days between two dates.  =DAYS((end_date, start_date) 
MONTH  Converts a serial number to a month. Returns the month, a number from 1 (January) to 12 (December)  =MONTH(serial_number)  
YEAR  Converts a serial number to a year. Returns the year corresponding to a date. The year is returned as an integer in the range 19009999.  =YEAR( serial_number i.e. 42766) 

YEARFRAC  Returns the year fraction representing the number of whole days between start_date and end_date  =YEARFRAC( start_date, end_date, [basis]) 

DATEDIF  2016  Calculates the number of days, months, or years between two dates. This function is useful in formulas where you need to calculate an age. Intro in 2016 VER but work in 2013 (no hind) also. (formation Option >>"Y"=year, "YM"=month, "MD" = day)  =DATEDIF( date1, date2, Format Option) 
NOW  Returns the serial number of the current date and time. Returns the serial number of the current date and time. If the cell format was General before the function was entered, Excel changes the cell format so that it matches the date and time format of your regional settings.  =NOW()  
TODAY  Returns the serial number of today's date. Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 31, 2017 is serial number 42766 because it is 42,766 days after January 1, 1900  =TODAY()  
NETWORKDAYS  Returns the number of whole workdays between two dates. Working days exclude weekends and any dates identified in holidays.  =NETWORKDAYS( start_date, end_date, [holidays]) 

NETWORKDAYS .INTL  2010  Returns the number of whole workdays between two dates with custom weekend parameter. Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays. Weekend number values indicate the following weekend days: Weekend number Weekend days 1 or omitted  Saturday, Sunday 2  Sunday, Monday 3  Monday, Tuesday 4  Tuesday, Wednesday 5  Wednesday, Thursday 6  Thursday, Friday 7  Friday, Saturday 11  Sunday only 12  Monday only 13  Tuesday only 14  Wednesday only 15  Thursday only 16  Friday only 17  Saturday only  =NETWORKDAYS .INTL( start_date, end_date, [weekend], [holidays]) 
WEEKDAY  Converts a serial number to a day of the week. 1 to 7 identifying th day of the week of the date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.  =WEEKDAY( serial_number, [Return_type]) 

WEEKNUM  Converts a serial number to a number representing where the week falls numerically with a year. week number in the year. Return_type (Optional): A number that determines on which day the week begins. The default is 1. Return_type Week begins on System 1 or omitted Sunday 1 2 Monday 1 11 Monday 1 12 Tuesday 1 13 Wednesday 1 14 Thursday 1 15 Friday 1 16 Saturday 1 17 Sunday 1 21 Monday 2  =WEEKNUM( serial_number, [Return_type]) 

ISOWEEKNUM  2013  Returns number of ISO week number of the year for a given date.  =ISOWEEKNUM(date) 
WORKDAY  Returns the serial number of the date before or after a specified number of workdays  =WORKDAY( start_date, days, [holidays]) 

WORKDAY.INTL  2010  Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays. Weekend number values indicate the following weekend days: Weekend number Weekend days 1 or omitted  Saturday, Sunday 2  Sunday, Monday 3  Monday, Tuesday 4  Tuesday, Wednesday 5  Wednesday, Thursday 6  Thursday, Friday 7  Friday, Saturday 11  Sunday only 12  Monday only 13  Tuesday only 14  Wednesday only 15  Thursday only 16  Friday only 17  Saturday only  =WORKDAY.INTL( start_date, days, [weekend], [holidays]) 
Text Functions
Function  Version  Description  Syntax 

BAHTTEXT  Converts a number to Thai text and adds a suffix of "Baht", using the ß (baht) currency format. You can change the Baht format to a different style in the Excel desktop application by using Regional and Language Options from control pannel.  =BAHTTEXT((number)  
ASC  Changes fullwidth (doublebyte) English letters or katakana within a character string to halfwidth (singlebyte) characters  =ASC (text)  
CHAR  Returns the character specified by the code number from the character set for your computer. The CHAR function handles 256 characters but nothing more.  =CHAR(number)  
CODE  Returns a numeric code for the first character in a text string, in the character set used by your computer.  =CODE(text)  
UNICHAR  Excel 2013  Returns the Unicode character that is references by the given numeric value. The CHAR function handles more than 256 characters.  =UNICHAR(number) 
UNICODE  Excel 2013  Returns the number (code point) that corresponds to the first character of the text  =UNICODE(text) 
CLEAN  Removes all nonprintable characters from text  =CLEAN(text)  
CONCAT  Excel 2016  Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments. It replaces the CONCATENATE function. However, the CONCATENATE function will stay available for compatibility with earlier versions of Excel.  =CONCAT(text1, [text2],…) 
CONCATENATE  Joins several text items into one text item  =CONCATENATE(text1,text2,…..)  
DOLLAR  Converts a number to text, using the $ (dollar) currency format  =DOLLAR(number,[decimal])  
EXACT  Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is casesensitive.  =EXACT(text1,text2)  
FIND, FINDB  Returns the starting position of one text string within another text string, FIND is casesensitive.  =FIND(find_text,within_text,[start_num])  
SEARCH, SEARCHB  Returns the number of the character at which a specific character or text string is first found, reading left to right, (not casesensitive)  =SEARCH(find_text,within_text,[start_num])  
FIXED  Round number to the specified number of decimal and returns the result as text with or without commas.  =FIXED(number,[decimal],[no_commas])  
LEFT, LEFTB  Returns the specified number of characters from the start of a textstrings.  =LEFT(text,[num_chars])  
RIGHT, RIGHTB  Returns the specified number of characters from the end of a textstrings.  =RIGHT(text,[num_chars])  
MID, MIDB  Returns the characters from the middle of a text string, given a starting position and length.  =MID(text,srart_num,num_chars)  
LEN, LENB  Returns the number of characters in a text string  =LEN(text)  
LOWER  Converts all letters in a text string to lowercase.  =LOWER(text)  
UPPER  Converts a text string to all uppercase letters.  =UPPER(text)  
PROPER  Converts a text string to proper case; the first letter in each word in uppercase and all other letters to lowercase.  =PROPER(text)  
REPT  Repeats text a given number of times  =REPT(Text, number_times)  
REPLACE, REPLACEB  Replace part of a text string with a different text string.  =REPLACE(old_text, start_num, num_chars, new_text)  
SUBSTITUTE  Replace existing text with new text in a text string.  =SUBSTITUTE(text, old_text, new_text, [instance_num])  
T  Checks whether a value is text, and returs the text if it is, or returns double quotes (empty text) if it is not.  =T(value)  
TEXT  Converts a value to text in a specific number format.  =TEXT(value, format_text)  
TEXTAFTER  Excel  365  Returns text that occurs after given character or string.  =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) 
TEXTBEFORE  Excel  365  Returns text that occurs before a given character or string.  =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) 
TEXTJOIN  Excel 2019  Combines the text from multiple ranges and/or strings.  =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) 
TEXTSPLIT  Excel  365  Splits text strings by using column and row delimiters. col_delimiter: The text that marks the point where to spill the text across columns. row_delimiter: The text that marks the point where to spill the text down rows. Optional. ignore_empty: Specify TRUE to ignore consecutive delimiters. Defaults to FALSE, which creates an empty cell. Optional. match_mode: Specify 1 to perform a caseinsensitive match. Defaults to 0, which does a casesensitive match. Optional. pad_with: The value with which to pad the result. The default is #N/A.  =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) 
ARRAYTOTEXT  Excel  365  Returns an array of text values from any specified range. Format: Optional. It can be one of two values: 0 Default. The text returned will be the same as the text rendered in a cell that has general formatting applied. 1 Strict format that includes escape characters and row delimiters. Encapsulates returned strings in quotes except for Booleans, Numbers and Errors.  =ARRAYTOTEXT(array, [format]) 
TRIM  Removes all spaces from a text string except for single spaces between words.  =TRIM(text)  
VALUE  Converts a text string that represents a number to a number  =VALUE(text)  
VALUETOTEXT  Excel  365  Returns text from any specified value. Format: Optional. It can be one of two values: 0 Default. The text returned will be the same as the text rendered in a cell that has general formatting applied. 1 Strict format that includes escape characters and row delimiters. Encapsulates returned strings in quotes except for Booleans, Numbers and Errors.  =VALUETOTEXT(value, [format]) 
NUMBERVALUE  Excel 2013  Converts text to number in a localeindependent manner  =NUMBERVALUE(Text, [Decimal_separator], [Group_separator ]) 
Statistical Functions
Function  Version  Description  Syntax 

AVEDEV  Returns the average of the absolute deviations of data points from their mean  =AVEDEV(number1,[number2],….)  
AVERAGE  Returns the average of its arguments. Arguments can be numbers, names, arrays, or references that contain numbers.  =AVERAGE(number1,[number2],…..)  
AVERAGEA  Returns the average of its arguments, including numbers, text, and logical values  =AVERAGEA(value1,[value2],…..)  
AVERAGEIF  Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria  =AVERAGEIF(range, criteria, [averagerange])  
AVERAGEIFS  Excel 2019  Returns the average (arithmetic mean) of all cells that meet multiple criteria.  =AVERAGEIFS(average_range, criteria_range1, criteria1,[criteria_range2, criteria2],…..) 
COUNT  Counts how many numbers are in the list of arguments. only numbers & Date in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. 255 arguments can be added.  =COUNT(value1, [value2], [value3],…….)  
COUNTA  Counts the number of cells that are not empty.  =COUNTA(value1,[value2],…….)  
COUNTBLANK  Counts the number of empty cells in a specified range of cells.  =COUNTBLANK(range)  
COUNTIF  Counts the number of nonblank cells within a range that meet the given criteria  =COUNTIF(range, criteria)  
COUNTIFS  Excel 2019  Counts the number of cells specified by a given set of conditions or criteria.  =COUNTIFS(criteria_range1, criteria1,[criteria_range2, criteria2],……..) 
FREQUENCY  Returns a frequency distribution as a vertical array. Calculate how often values occur within a range of values and then returns a vertical array of numbers of having one more element than bins_array.  =FREQUENCY(data_array, bins_array)  
LARGE  Returns the kth largest value in a data set  =LARGE(array, k)  
SMALL  Returns the kth smallest value in a data set  =SMALL(array, k)  
MAX  Returns the maximum value in a list of arguments. It ignores logical value and text.  =MAX(number1, [number2], ……..)  
MAXA  Returns the maximum value in a list of arguments, including numbers, text, and logical values. Does not ignore logical value and text.  =MAXA(value1, [value2], …….)  
MAXIFS  Excel 2019  Returns the maximum value among cells specified by a given set of conditions or criteria  =MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 
MIN  Returns the minimum value in a list of arguments. It ignores logical value and text.  =MIN(number1, [number2], ……..)  
MINA  Returns the smallest value in a list of arguments, including numbers, text, and logical values. Does not ignore logical value and text.  =MINA(value1, [value2], …….)  
MINIFS  Excel 2019  Returns the minimum value among cells specified by a given set of conditions or criteria.  =MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 
MEDIAN  Returns the median, or the number in the middle of the set of given numbers.  =MEDIAN(number1, [number2], ……..)  
MODE  Returns the most frequently occuring or repetitive value in an array or range of data. This function is available for compatibility with excel 2007 and earlier.  =MODE(number1, [number2], ……..)  
MODE.SNGL  Excel 2010  Returns the most frequently occuring or repetitive value in an array or range of data. Arguments 2 to 254 for which you want to calculate the mode. You can also use a single array or a reference to an array instead of arguments separated by commas.  =MODE.SNGL(number1, [number2],……..) 
MODE.MULT  Excel 2010  Returns the vertical array of the most frequently occurring, or repetitive value in an array or range of data. For a horizontal array, =TRANSPOSE(MODE.MULT(number1,number2,.....)).  =MODE.MULT(number1, [number2],……..) 
FORECAST  Returns a value along a linear trend . Note: In Excel 2016, this function is replaced with FORECAST.LINEAR as part of the new Forecasting functions, but it's still available for compatibility with earlier versions.  =FORECAST(x, known_y's, known_x's)  
FORECAST.LINEAR  Excel 2016  Returns a future value based on existing values  =FORECAST.LINEAR(x, known_y's, known_x's) 
GROWTH  Returns values along an exponential trend  =GROWTH(known_y's, [known_x's], [new_x's], [const])  
RANK  Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.) Nonnumeric values in ref are ignored. Important: This function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage. This function is available for compatibility with excel 2007 and earlier.  =RANK(number,ref,[order])  
RANK.AVG  Excel 2010  Returns the rank of a number in a list of numbers. If more than one value has the same rank, the average rank is returned. Order is 0 (zero) or omitted = Descending order Order is any nonzero value = Ascending order  =RANK.AVG(number,ref,[order]) 
RANK.EQ  Excel 2010  Returns the rank of a number in a list of numbers. If more than one value has the same rank, the top rank of that set of values is returned. Order is 0 (zero) or omitted = Descending order Order is any nonzero value = Ascending order  =RANK.EQ(number,ref,[order]) 
Math Functions
Function  Version  Description  Syntax 

ABS  Returns the absolute value of a number, a number without its sign.  =ABS(number)  
CEILING  Rounds a number to the nearest integer or to the nearest multiple of significance.  =CEILING(number, significance)  
CEILING.MATH  Excel 2013  Rounds a number up, to the nearest integer or to the nearest multiple of significance.  =CEILING.MATH(number, [significance], [mode]) 
DEGREES  Converts radians to degrees  =DEGREES(angle)  
RADIANS  Converts degrees to radians  =RADIANS(angle)  
EVEN  Round the positive number up and negative number down to the nearest even integar.  =EVEN(number)  
ODD  Round the positive number up and negative number down to the nearest odd integar.  =ODD(number)  
EXP  Returns e raised to the power of a given number  =EXP(number)  
FACT  Returns the factorial of a number, equal to 1*2*3*numbers.  =FACT(number)  
INT  Rounds a number down to the nearest integer  =INT(number)  
MOD  Returns the remainder (after a number is divided by a divisor) from division.  =MOD(number, divisor)  
POWER  Returns the result of a number raised to a power  =POWER(number, power)  
PRODUCT  Multiplies its arguments. 255 num can be added.  =PRODUCT(number1, [number2],……..)  
QUOTIENT  Returns the integer portion of a division  =QUOTIENT(numerator, denominator)  
RAND  Returns a random number greater than or equal to 0 and less than 1  =RAND()  
RANDBETWEEN  Returns a random number between the numbers you specify  =RANDBETWEEN(bottom, top)  
RANDARRAY  Excel 365  Returns an array of random numbers between 0 and 1. However, you can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values.  =RANDARRAY([rows],[columns],[min],[max],[whole_number]) 
ROMAN  Converts an arabic numeral to roman, as text. form1 = 0classic; 1more concise; 2more concise; 3more concise; 4simplified  =ROMAN(number,[form1])  
FLOOR  Excel 2013  Rounds number down, toward zero, to the nearest multiple of significance.  =FLOOR(number, significance) 
ROUND  Rounds a number to a specified number of digits  =ROUND(number, num_digits)  
ROUNDUP  Rounds a number up, away from zero  =ROUNDUP(number, num_digits)  
ROUNDDOWN  Rounds a number down, toward zero  =ROUNDDOWN(number, num_digits)  
SUBTOTAL  Returns a subtotal in a list or database. Function num = 1AVERAGE; 2COUNT; 3COUNTA; 4MAX; 5MIN; 6PRODUCT; 9SUM; 101AVERAGE;102COUNT; 103COUNTA; 104MAX; 105MIN; 106PRODUCT; 109SUM. (101 to 111 is work on only visible cells while 1 to 9 work on invisible cell too.) 254 ref can be added.  =SUBTOTAL(function_num, ref1, [ref2], ……..)  
SUM  Adds all the numbers in the range of cells. 255 num can be added.  =SUM(number1, [number2],………)  
SUMIF  Adds the cells specified by a given condition or criteria  =SUMIF(range, criteria, [sum_range])  
SUMIFS  Excel 2019  Adds the cells in a range that meet multiple criteria. (127 criterias can be added).  =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],………) 
SUMPRODUCT  Returns the sum of the products of corresponding ranges or array components. (255 arrays can be added)  =SUMPRODUCT(array1, [array2], [array3],……..)  
TRUNC  Truncates a number to an integer by removing the decimal, or fractional, part of the number.  =TRUNC(number, [num_digits]) 
Lookup & Reference Functions
Function  Version  Description  Syntax 

AREAS  Returns the number of areas in a reference. An area is an continuous range of cells or a single cell.  =AREAS(reference)  
ADDRESS  Creates a cell reference as text to a single cell in a worksheet, given specified row and column numbers.  =ADDRESS(row_num, column_num, [abs_num1], [a1], [sheet_text])  
COLUMN  Returns the column number of a reference  =COLUMN([reference])  
COLUMNS  Returns the number of columns in an array or reference  =COLUMNS(array)  
ROW  Returns the row number of a reference  =ROW([reference])  
ROWS  Returns the number of rows in an array or reference  =ROWS(array)  
GETPIVOTDATA  Returns/extracts data stored in a PivotTable  =GETPIVOTDATA(data_field, pivot_table, [field1, item1],……..)  
HYPERLINK  Creates a shortcut or jump that opens a document stored on your hard drive, on a network server, an intranet, or the Internet  =HYPERLINK(link_location, [friendly_name])  
INDIRECT  Returns a reference indicated by a text value  =INDIRECT(ref_text, [a1])  
LOOKUP  Looks up values in a vector or array (lookup_vector = lookup_array)  =LOOKUP(lookup_value, array) & LOOKUP(lookup_value, lookup_vector, [result_vector])  
XLOOKUP  Excel  365  Use the XLOOKUP function to find things in a table or range by row. With XLOOKUP, you can look in one column for a search term and return a result from the same row in another column, regardless of which side the return column is on. Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A is returned. [match_mode] : 0  Exact match. If none found, return #N/A. This is the default. 1  Exact match. If none found, return the next smaller item. 1  Exact match. If none found, return the next larger item. 2  A wildcard match where *, ?, and ~ have special meaning.  =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 
HLOOKUP  Looks for a value in the top row of a table or array of values and returns the value in the same column from a row you specify.  =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])  
VLOOKUP  Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be stored in an ascending order.  =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])  
CHOOSE  Choose a value or action to perform from a list of values, based on an index number. 254 values can be added.  =CHOOSE(index_num, value1,[value2],…….)  
CHOOSECOLS  Excel  365  Returns the specified columns from an array.  =CHOOSECOLS(array, col_num1, [col_num2],…) 
CHOOSEROWS  Excel  365  Returns the specified rows from an array.  =CHOOSEROWS(array, row_num1, [row_num2],…) 
INDEX  Returns a value or reference of the cell at a intersection of a particular row and column, in a given range. Uses an index to choose a value from a reference or array  =INDEX(array, row_num, [column_num]) or =INDEX(reference, row_num, [column_num], [area_num])  
MATCH  Returns the relative position of an item in array that match a specified values in specified order. Looks up values in a reference or array.  =MATCH(lookup_value, lookup_array, [match_type {1=less than, 0=Exact match, 1=Greater than}])  
XMATCH  Excel  365  The XMATCH function searches for a specified item in an array or range of cells, and then returns the item's relative position. Match_mode specifies which match type to use: 0 or omitted (default)  exact match 1  exact match or the next smallest value 1  exact match or the next largest value 2  wildcard match (*, ?) Search_mode specifies the search direction and algorithm: 1 or omitted (default)  search from first to last. 1  search in reverse order from last to first. 2  binary search ascending. Requires lookup_array to be sorted in ascending order. 2  binary search descending. Requires lookup_array to be sorted in descending order.  =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) 
OFFSET  Returns a reference of a range that is a given number of rows and columns from a given reference.  =OFFSET(reference, rows, cols, [height], [width])  
TRANSPOSE  Converts a vertical range of cells to a horizontal range, or vice versa.  =TRANSPOSE(array)  
RTD  Retrieves realtime data from a program that supports COM automation (Automation: A way to work with an application's objects from another application or development tool. Formerly called OLE Automation, Automation is an industry standard and a feature of the Component Object Model (COM).)  
FORMULATEXT  Excel 2013  Returns the formula at the given reference as text/string.  =FORMULATEXT(reference) 
DROP  Excel  365  Excludes a specified number of rows or columns from the start or end of an array. You might find this function useful to remove headers and footers in an Excel report to return only the data.  =DROP(array, rows, [columns]) 
EXPAND  Excel  365  Expands or pads an array to specified row and column dimensions.  =Expand(array, rows, [columns], [pad_with]) 
FILTER  Excel  365  Filters a range of data based on criteria you specify.  =FILTER(array, include, [if_empty]) 
SORT  Excel  365  Sorts the contents of a range or array. [sort_order]: 1 for ascending order (default), 1 for descending order. [by_col]: A logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column.  =SORT(array, [sort_index], [sort_order], [by_col]) 
SORTBY  Excel  365  Sorts the contents of a range or array based on the values in a corresponding range or array. by_array1: The array or range to sort on  =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…) 
TAKE  Excel  365  Returns a specified number of contiguous rows or columns from the start or end of an array.  =TAKE(array, rows, [columns]) 
TOCOL  Excel  365  Returns the array in a single column. Ignore: Whether to ignore certain types of values. By default, no values are ignored. Specify one of the following: 0 Keep all values (default) 1 Ignore blanks 2 Ignore errors 3 Ignore blanks and errors scan_by_column: If it is omitted or FALSE, the array is scanned by row; If TRUE, the array is scanned by column.  =TOCOL(array, [ignore], [scan_by_column]) 
TOROW  Excel  365  Returns the array in a single row. Arguments are same as TOCOL.  =TOROW(array, [ignore], [scan_by_column]) 
UNIQUE  Excel  365  Returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range. The data to filter by unique entries. by_column: Whether to filter the data by columns or by rows. By default, this is FALSE. exactly_once: Whether to return only entries with no duplicates. By default, this is FALSE.  =UNIQUE(range, [by_column], [exactly_once]) 
Database Functions
Function  Version  Description  Syntax 

DAVERAGE  Returns the average of selected database entries  =DAVERAGE(database, field, criteria)  
DCOUNT  Counts the cells that contain numbers in a database  =DCOUNT(database, field, criteria)  
DCOUNTA  Counts nonblank cells in a database  =DCOUNTA(database, field, criteria)  
DGET  Extracts from a database a single record that matches the specified criteria  =DGET(database, field, citeria)  
DMAX  Returns the maximum value from selected database entries  =DMAX(database, field, criteria)  
DMIN  Returns the minimum value from selected database entries  =DMIN(database, field, criteria)  
DPRODUCT  Multiplies the values in a particular field of records that match the criteria in a database  =DPRODUCT(database, field, criteria)  
DSUM  Adds the numbers in the field column of records in the database that match the criteria  =DSUM(database, field, criteria)  
DSTDEV  Estimates the standard deviation based on a sample of selected database entries  =DSTDEV(database, field, criteria)  
DSTDEVP  Calculates the standard deviation based on the entire population of selected database entries  =DSTDEVP(database, field, criteria)  
DVAR  Estimates variance based on a sample from selected database entries  =DVAR(database, field, criteria)  
DVARP  Calculates variance based on the entire population of selected database entries  =DVARP(database, field, criteria) 