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)
Compliant responsive placement adhering to Google Publisher Policies. Ads will display here upon adding your Publisher ID in .env.local.
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)
Compliant responsive placement adhering to Google Publisher Policies. Ads will display here upon adding your Publisher ID in .env.local.