Excel 500 Functions Directory & Syntax Encyclopedia
The ultimate reference manual for every Microsoft Excel function. Browse by category, search by parameter, copy ready-to-use formulas, or jump directly into the live simulator to practice!
Google AdSense Responsive Unit (horizontal)
Publisher ID: ca-pub-7444400132160337. Ad unit ready for live ad serving upon Google approval.
Searches a range or an array for a match and returns the corresponding item from a second range or array.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])=XLOOKUP("EMP-104", A2:A100, D2:D100, "Not Found", 0)Looks for a value in the leftmost column of a table and returns a value in the same row from a column you specify.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=VLOOKUP(102, A2:E50, 3, FALSE)Searches for a value in the top row of a table or an array of values, and returns a value in the same column from a row you specify.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])=HLOOKUP(A2:A10)Returns a value or reference of the cell at the intersection of a particular row and column in a given range.
=INDEX(array, row_num, [column_num])=INDEX(A2:A100, MATCH("Widget Pro", B2:B100, 0))Returns the relative position of an item in an array that matches a specified value in a specified order.
=MATCH(lookup_value, lookup_array, [match_type])=MATCH(A2:A10)Returns the relative position of an item in an array or range of cells. Defaults to exact match.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])=XMATCH(A2:A10)Uses index_num to return a value from the list of value arguments.
=CHOOSE(index_num, value1, [value2], ...)=CHOOSE(A2:A10)Returns the specified columns from an array.
=CHOOSECOLS(array, col_num1, [col_num2], ...)=CHOOSECOLS(A2:A10)Returns the specified rows from an array.
=CHOOSEROWS(array, row_num1, [row_num2], ...)=CHOOSEROWS(A2:A10)Excludes a specified number of rows or columns from the start or end of an array.
=DROP(array, rows, [columns])=DROP(A2:A10)Returns a specified number of contiguous rows or columns from the start or end of an array.
=TAKE(array, rows, [columns])=TAKE(A2:A10)Expands or pads an array to specified row and column dimensions.
=EXPAND(array, rows, [columns], [pad_with])=EXPAND(A2:A10)Appends arrays vertically and in sequence to return a larger combined array.
=VSTACK(array1, [array2], ...)=VSTACK(A2:A10)Appends arrays horizontally and in sequence to return a larger array.
=HSTACK(array1, [array2], ...)=HSTACK(A2:A10)Returns the array in a single column.
=TOCOL(array, [ignore], [scan_by_column])=TOCOL(A2:A10)Returns the array in a single row.
=TOROW(array, [ignore], [scan_by_column])=TOROW(A2:A10)Wraps the provided row or column of values by columns after a specified number of elements.
=WRAPCOLS(vector, wrap_count, [pad_with])=WRAPCOLS(A2:A10)Wraps the provided row or column of values by rows after a specified number of elements.
=WRAPROWS(vector, wrap_count, [pad_with])=WRAPROWS(A2:A10)Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
=OFFSET(reference, rows, cols, [height], [width])=OFFSET(A2:A10)Returns the reference specified by a text string.
=INDIRECT(ref_text, [a1])=INDIRECT(A2:A10)Creates a cell address as text, given specified row and column numbers.
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])=ADDRESS(A2:A10)Returns a vertical range of cells as a horizontal range, or vice versa.
=TRANSPOSE(array)=TRANSPOSE(A2:A10)Creates a shortcut or jump that opens a document stored on your hard drive, a network server, or on the internet.
=HYPERLINK(link_location, [friendly_name])=HYPERLINK(A2:A10)Returns the formula at a given reference as text.
=FORMULATEXT(reference)=FORMULATEXT(A2:A10)Returns the row number of a reference.
=ROW([reference])=ROW(A2:A10)Returns the column number of a reference.
=COLUMN([reference])=COLUMN(A2:A10)Returns the number of rows in a reference or array.
=ROWS(array)=ROWS(A2:A10)Returns the number of columns in a reference or array.
=COLUMNS(array)=COLUMNS(A2:A10)Returns a list of unique values in a list or range.
=UNIQUE(array, [by_col], [exactly_once])=UNIQUE(B2:B200)Filters a range or array based on boolean criteria.
=FILTER(array, include, [if_empty])=FILTER(A2:D100, C2:C100="Electronics", "No records")Sorts the contents of a range or array.
=SORT(array, [sort_index], [sort_order], [by_col])=SORT(A2:A10)Sorts the contents of a range or array based on the values in a corresponding range or array.
=SORTBY(array, by_array1, [sort_order1], ...)=SORTBY(A2:A10)Generates a list of sequential numbers in an array, such as 1, 2, 3, 4.
=SEQUENCE(rows, [columns], [start], [step])=SEQUENCE(A2:A10)Returns an array of random numbers between 0 and 1, or between specified integers.
=RANDARRAY([rows], [columns], [min], [max], [whole_number])=RANDARRAY(A2:A10)Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
=IF(logical_test, [value_if_true], [value_if_false])=IF(C2>=75, "Pass", "Needs Review")Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)=IFS(A2:A10)Returns TRUE if all of its arguments are TRUE; returns FALSE if one or more arguments are FALSE.
=AND(logical1, [logical2], ...)=AND(A2:A10)Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
=OR(logical1, [logical2], ...)=OR(A2:A10)Reverses the value of its argument. Returns TRUE for FALSE and FALSE for TRUE.
=NOT(logical)=NOT(A2:A10)Returns a logical exclusive OR of all arguments.
=XOR(logical1, [logical2], ...)=XOR(A2:A10)Evaluates an expression against a list of values and returns the result corresponding to the first matching value.
=SWITCH(expression, val1, result1, [val2, result2], ..., [default])=SWITCH(A2:A10)Returns value_if_error if expression is an error and the value of the expression itself otherwise.
=IFERROR(value, value_if_error)=IFERROR(A2:A10)Returns the value you specify if the formula returns the #N/A error value; otherwise returns formula result.
=IFNA(value, value_if_na)=IFNA(A2:A10)Assigns names to calculation results, allowing storing intermediate calculations, values, or defining names inside a formula.
=LET(name1, name_value1, calculation_or_name2, ...)=LET(A2:A10)Creates custom, reusable functions and calls them by a friendly name.
=LAMBDA([parameter1, parameter2, ...], calculation)=LAMBDA(A2:A10)Adds all the numbers in a range of cells.
=SUM(number1, [number2], ...)=SUM(A2:A10)Adds the cells specified by a given criteria.
=SUMIF(range, criteria, [sum_range])=SUMIF(A2:A10)Adds the cells in a range that meet multiple criteria.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=SUMIFS(E2:E500, B2:B500, "North", C2:C500, ">5000")Multiplies corresponding components in the given arrays, and returns the sum of those products.
=SUMPRODUCT(array1, [array2], [array3], ...)=SUMPRODUCT(A2:A10)Multiplies all the numbers given as arguments and returns the product.
=PRODUCT(number1, [number2], ...)=PRODUCT(A2:A10)Returns the absolute value of a number (number without its sign).
=ABS(number)=ABS(A2:A10)Rounds a number to a specified number of digits.
=ROUND(number, num_digits)=ROUND(A2:A10)Rounds a number up, away from zero.
=ROUNDUP(number, num_digits)=ROUNDUP(A2:A10)Rounds a number down, toward zero.
=ROUNDDOWN(number, num_digits)=ROUNDDOWN(A2:A10)Returns a number rounded to the desired multiple.
=MROUND(number, multiple)=MROUND(A2:A10)Rounds a number up, to the nearest integer or to the nearest multiple of significance.
=CEILING(number, significance)=CEILING(A2:A10)Rounds a number down, toward zero, to the nearest multiple of significance.
=FLOOR(number, significance)=FLOOR(A2:A10)Rounds a number down to the nearest integer.
=INT(number)=INT(A2:A10)Truncates a number to an integer by removing the fractional part of the number.
=TRUNC(number, [num_digits])=TRUNC(A2:A10)Returns the remainder after number is divided by divisor.
=MOD(number, divisor)=MOD(A2:A10)Returns the result of a number raised to a power.
=POWER(number, power)=POWER(A2:A10)Returns a positive square root.
=SQRT(number)=SQRT(A2:A10)Returns e raised to the power of a given number.
=EXP(number)=EXP(A2:A10)Returns the natural logarithm of a number.
=LN(number)=LN(A2:A10)Returns the logarithm of a number to the base you specify.
=LOG(number, [base])=LOG(A2:A10)Returns the base-10 logarithm of a number.
=LOG10(number)=LOG10(A2:A10)Returns the value of pi: 3.14159265358979, accurate to 15 digits.
=PI()=PI(A2:A10)Returns a subtotal in a list or database.
=SUBTOTAL(function_num, ref1, [ref2], ...)=SUBTOTAL(A2:A10)Returns an aggregate in a list or database with options to ignore hidden rows and error values.
=AGGREGATE(function_num, options, ref1, [ref2], ...)=AGGREGATE(A2:A10)Returns an evenly distributed random real number greater than or equal to 0 and less than 1.
=RAND()=RAND(A2:A10)Returns a random integer number between the numbers you specify.
=RANDBETWEEN(bottom, top)=RANDBETWEEN(A2:A10)Counts the number of cells that contain numbers.
=COUNT(value1, [value2], ...)=COUNT(A2:A10)Counts the number of cells that are not empty.
=COUNTA(value1, [value2], ...)=COUNTA(A2:A10)Counts empty cells in a specified range of cells.
=COUNTBLANK(range)=COUNTBLANK(A2:A10)Counts the number of cells within a range that meet the given condition.
=COUNTIF(range, criteria)=COUNTIF(A2:A10)Returns the average (arithmetic mean) of the arguments.
=AVERAGE(number1, [number2], ...)=AVERAGE(A2:A10)Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.
=AVERAGEIF(range, criteria, [average_range])=AVERAGEIF(A2:A10)Returns the average (arithmetic mean) of all cells that meet multiple conditions.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=AVERAGEIFS(A2:A10)Returns the median, or the number in the middle of the set of given numbers.
=MEDIAN(number1, [number2], ...)=MEDIAN(A2:A10)Returns the most frequently occurring, or repetitive, value in an array or range of data.
=MODE(number1, [number2], ...)=MODE(A2:A10)Returns the smallest number in a set of values.
=MIN(number1, [number2], ...)=MIN(A2:A10)Returns the largest value in a set of values.
=MAX(number1, [number2], ...)=MAX(A2:A10)Returns the minimum value among cells specified by a given set of conditions or criteria.
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=MINIFS(A2:A10)Returns the maximum value among cells specified by a given set of conditions or criteria.
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=MAXIFS(A2:A10)Returns the k-th largest value in a data set.
=LARGE(array, k)=LARGE(A2:A10)Returns the k-th smallest value in a data set.
=SMALL(array, k)=SMALL(A2:A10)Returns the rank of a number in a list of numbers.
=RANK(number, ref, [order])=RANK(A2:A10)Returns the k-th percentile of values in a range.
=PERCENTILE(array, k)=PERCENTILE(A2:A10)Estimates standard deviation based on a sample.
=STDEV(number1, [number2], ...)=STDEV(A2:A10)Estimates variance based on a sample.
=VAR(number1, [number2], ...)=VAR(A2:A10)Splits text strings across columns and/or rows using delimiters.
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])=TEXTSPLIT("John, Smith, Developer", ", ")Combines the text from multiple ranges and/or strings, and includes a delimiter you specify.
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)=TEXTJOIN(A2:A10)Combines the text from multiple ranges and/or strings, but does not provide delimiter or ignore_empty arguments.
=CONCAT(text1, [text2], ...)=CONCAT(A2:A10)Returns the specified number of characters from the start of a text string.
=LEFT(text, [num_chars])=LEFT(A2:A10)Returns the specified number of characters from the end of a text string.
=RIGHT(text, [num_chars])=RIGHT(A2:A10)Returns a specific number of characters from a text string, starting at the position you specify.
=MID(text, start_num, num_chars)=MID(A2:A10)Returns the number of characters in a text string.
=LEN(text)=LEN(A2:A10)Locates one text string within a second text string, and returns the number of the starting position (Case-Sensitive).
=FIND(find_text, within_text, [start_num])=FIND(A2:A10)Locates one text string within a second text string (Not Case-Sensitive, supports * and ? wildcards).
=SEARCH(find_text, within_text, [start_num])=SEARCH(A2:A10)Replaces part of a text string, based on the number of characters you specify, with a different text string.
=REPLACE(old_text, start_num, num_chars, new_text)=REPLACE(A2:A10)Substitutes new_text for old_text in a text string.
=SUBSTITUTE(text, old_text, new_text, [instance_num])=SUBSTITUTE(A2:A10)Removes all spaces from text except for single spaces between words.
=TRIM(text)=TRIM(A2:A10)Capitalizes the first letter in each word of a text string, and converts all other letters to lowercase.
=PROPER(text)=PROPER(A2:A10)Converts text to uppercase.
=UPPER(text)=UPPER(A2:A10)Converts all uppercase letters in a text string to lowercase.
=LOWER(text)=LOWER(A2:A10)Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise (Case-Sensitive).
=EXACT(text1, text2)=EXACT(A2:A10)Converts a value to text in a specific number format (e.g. "$#,##0.00" or "YYYY-MM-DD").
=TEXT(value, format_text)=TEXT(A2:A10)Converts a text string that represents a number to a number.
=VALUE(text)=VALUE(A2:A10)Returns text that occurs before a given character or string.
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])=TEXTBEFORE(A2:A10)Returns text that occurs after a given character or string.
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])=TEXTAFTER(A2:A10)Returns the serial number of the current date.
=TODAY()=TODAY(A2:A10)Returns the serial number of the current date and time.
=NOW()=NOW(A2:A10)Returns the sequential serial number that represents a particular date.
=DATE(year, month, day)=DATE(A2:A10)Returns the decimal number for a particular time.
=TIME(hour, minute, second)=TIME(A2:A10)Returns the year corresponding to a date as a four-digit integer.
=YEAR(serial_number)=YEAR(A2:A10)Returns the month of a date represented by a serial number (1 to 12).
=MONTH(serial_number)=MONTH(A2:A10)Returns the day of the month (integer from 1 to 31).
=DAY(serial_number)=DAY(A2:A10)Returns the serial number that represents the date that is the indicated number of months before or after a start date.
=EDATE(start_date, months)=EDATE(A2:A10)Returns the serial number for the last day of the month that is the indicated number of months before or after start_date.
=EOMONTH(start_date, months)=EOMONTH(A2:A10)Returns the number of whole working days between start_date and end_date (excluding weekends and optional holidays).
=NETWORKDAYS(start_date, end_date, [holidays])=NETWORKDAYS(A2:A10)Returns a date that is the indicated number of working days before or after a date.
=WORKDAY(start_date, days, [holidays])=WORKDAY(A2:A10)Returns the number of days between two dates.
=DAYS(end_date, start_date)=DAYS(A2:A10)Calculates the number of days, months, or years between two dates.
=DATEDIF(start_date, end_date, unit)=DATEDIF(B2, TODAY(), "Y") & " Years"Returns the day of the week corresponding to a date (1 for Sunday to 7 for Saturday by default).
=WEEKDAY(serial_number, [return_type])=WEEKDAY(A2:A10)Returns the week number of a specific date.
=WEEKNUM(serial_number, [return_type])=WEEKNUM(A2:A10)Calculates the payment for a loan based on constant payments and a constant interest rate.
=PMT(rate, nper, pv, [fv], [type])=PMT(0.06/12, 360, 300000)Returns the present value of an investment: the total amount that a series of future payments is worth now.
=PV(rate, nper, pmt, [fv], [type])=PV(A2:A10)Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
=FV(rate, nper, pmt, [pv], [type])=FV(A2:A10)Calculates the net present value of an investment by using a discount rate and a series of future payments and income.
=NPV(rate, value1, [value2], ...)=NPV(A2:A10)Returns the internal rate of return for a series of cash flows.
=IRR(values, [guess])=IRR(A2:A10)Returns the net present value for a schedule of cash flows that is not necessarily periodic.
=XNPV(rate, values, dates)=XNPV(A2:A10)Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
=XIRR(values, dates, [guess])=XIRR(A2:A10)Returns the interest rate per period of an annuity.
=RATE(nper, pmt, pv, [fv], [type], [guess])=RATE(A2:A10)Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
=NPER(rate, pmt, pv, [fv], [type])=NPER(A2:A10)Returns the straight-line depreciation of an asset for one period.
=SLN(cost, salvage, life)=SLN(A2:A10)Returns TRUE if the value refers to an empty cell.
=ISBLANK(value)=ISBLANK(A2:A10)Returns TRUE if the value is a number.
=ISNUMBER(value)=ISNUMBER(A2:A10)Returns TRUE if the value is text.
=ISTEXT(value)=ISTEXT(A2:A10)Returns TRUE if the value is not text (blank cells are nontext).
=ISNONTEXT(value)=ISNONTEXT(A2:A10)Returns TRUE if the value is a logical value (TRUE or FALSE).
=ISLOGICAL(value)=ISLOGICAL(A2:A10)Returns TRUE if the value is any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
=ISERROR(value)=ISERROR(A2:A10)Returns TRUE if the value is any error value except #N/A.
=ISERR(value)=ISERR(A2:A10)Returns TRUE if the value is the #N/A (value not available) error value.
=ISNA(value)=ISNA(A2:A10)Returns TRUE if there is a reference to a cell that contains a formula.
=ISFORMULA(reference)=ISFORMULA(A2:A10)Returns TRUE if the number is odd.
=ISODD(number)=ISODD(A2:A10)Returns TRUE if the number is even.
=ISEVEN(number)=ISEVEN(A2:A10)Returns an integer representing the data type of a value: 1 = number, 2 = text, 4 = logical, 16 = error, 64 = array.
=TYPE(value)=TYPE(A2:A10)Adds the numbers in a column of records in a list or database that match conditions you specify.
=DSUM(database, field, criteria)=DSUM(A2:A10)Counts the cells that contain numbers in a column of records in a database that match conditions you specify.
=DCOUNT(database, field, criteria)=DCOUNT(A2:A10)Averages the values in a column of a list or database that match conditions you specify.
=DAVERAGE(database, field, criteria)=DAVERAGE(A2:A10)Extracts from a database a single record that matches conditions you specify.
=DGET(database, field, criteria)=DGET(A2:A10)Google AdSense Responsive Unit (auto)
Publisher ID: ca-pub-7444400132160337. Ad unit ready for live ad serving upon Google approval.