Function
In Microsoft Excel, a function is a pre-built 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.
Excel 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
- Add-in 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 1-2-3 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/A-7; #GETTING_DATA-8 | =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; text-2; 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...[2-126], Value to return if there's a match1...[2-126], 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 360-day 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 1900-9999. | =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 full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) 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 case-sensitive. | =EXACT(text1,text2) | |
| FIND, FINDB | Returns the starting position of one text string within another text string, FIND is case-sensitive. | =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 case-sensitive) | =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 text-strings. | =LEFT(text,[num_chars]) | |
| RIGHT, RIGHTB | Returns the specified number of characters from the end of a text-strings. | =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 case-insensitive match. Defaults to 0, which does a case-sensitive 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 locale-independent 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, [average-range]) | |
| 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 k-th largest value in a data set | =LARGE(array, k) | |
| SMALL | Returns the k-th 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 = 0-classic; 1-more concise; 2-more concise; 3-more concise; 4-simplified | =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 = 1-AVERAGE; 2-COUNT; 3-COUNTA; 4-MAX; 5-MIN; 6-PRODUCT; 9-SUM; 101-AVERAGE;102-COUNT; 103-COUNTA; 104-MAX; 105-MIN; 106-PRODUCT; 109-SUM. (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 real-time 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) |