=FUNCTIONS

The free Excel function reference

Syntax, arguments, and worked examples for every function — filter by category or search by name.

Search functions

130 functions

Math & Trig

ABS()

Returns the absolute value of a number — the number without its sign.

ABS(number)View syntax & examples →Lookup & Reference

ADDRESS()

Builds a cell reference as text from a row and column number.

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])View syntax & examples →Logical

AND()

Returns TRUE only if every argument is true.

AND(logical1, [logical2], ...)View syntax & examples →Lookup & Reference

AREAS()

Returns the number of separate ranges (areas) contained in a reference.

AREAS(reference)View syntax & examples →Statistical

AVERAGE()

Calculates the arithmetic mean of a set of numbers.

AVERAGE(number1, [number2], ...)View syntax & examples →Statistical

AVERAGEIF()

Averages the cells in a range that meet a single condition.

AVERAGEIF(range, criteria, [average_range])View syntax & examples →Math & Trig

CEILING()

Rounds a number up to the nearest multiple of a specified significance.

CEILING(number, significance)View syntax & examples →Text

CHAR()

Returns the character that corresponds to a given character code.

CHAR(number)View syntax & examples →Lookup & Reference

CHOOSE()

Returns a value from a list based on an index number.

CHOOSE(index_num, value1, [value2], ...)View syntax & examples →Text

CLEAN()

Strips the 32 non-printable control characters that often arrive with imported or pasted text.

CLEAN(text)View syntax & examples →Text

CODE()

Returns the numeric character code for the first character of a text string.

CODE(text)View syntax & examples →Lookup & Reference

COLUMN()

Returns the column number of a reference, or the current column when used without one.

COLUMN([reference])View syntax & examples →Text

CONCAT()

Joins text from multiple cells or strings together, with no delimiter.

CONCAT(text1, [text2], ...)View syntax & examples →Text

CONCATENATE()

Joins two or more text strings into a single string, with no separator added automatically.

CONCATENATE(text1, [text2], ...)View syntax & examples →Statistical

COUNT()

Counts how many cells or arguments contain numbers.

COUNT(value1, [value2], ...)View syntax & examples →Statistical

COUNTA()

Counts the number of non-empty cells in a range.

COUNTA(value1, [value2], ...)View syntax & examples →Statistical

COUNTBLANK()

Counts the number of empty cells in a range.

COUNTBLANK(range)View syntax & examples →Statistical

COUNTIF()

Counts the cells in a range that meet a single condition.

COUNTIF(range, criteria)View syntax & examples →Statistical

COUNTIFS()

Counts cells that meet multiple conditions at once.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)View syntax & examples →Date & Time

DATE()

Builds a date from separate year, month, and day numbers.

DATE(year, month, day)View syntax & examples →Date & Time

DATEDIF()

Calculates the difference between two dates expressed in complete years, months, or days.

DATEDIF(start_date, end_date, unit)View syntax & examples →Date & Time

DATEVALUE()

Converts a date stored as text into a true Excel date serial number.

DATEVALUE(date_text)View syntax & examples →Date & Time

DAY()

Returns the day of the month from a date as a number between 1 and 31.

DAY(serial_number)View syntax & examples →Date & Time

DAYS()

Returns the number of days between two dates.

DAYS(end_date, start_date)View syntax & examples →Text

DOLLAR()

Rounds a number and converts it to text formatted as currency.

DOLLAR(number, [decimals])View syntax & examples →Lookup & Reference

DROP()

Removes a specified number of rows or columns from the start or end of an array.

DROP(array, rows, [columns])View syntax & examples →Date & Time

EDATE()

Returns the date a specified number of months before or after a start date.

EDATE(start_date, months)View syntax & examples →Date & Time

EOMONTH()

Returns the last day of the month a specified number of months before or after a start date.

EOMONTH(start_date, months)View syntax & examples →Text

EXACT()

Compares two text strings and returns TRUE only when they match exactly, including letter case.

EXACT(text1, text2)View syntax & examples →Lookup & Reference

FILTER()

Filters a range to the rows (or columns) that meet one or more conditions — requires Excel 365 or Excel 2021.

FILTER(array, include, [if_empty])View syntax & examples →Text

FIND()

Returns the position of one text string within another — case-sensitive.

FIND(find_text, within_text, [start_num])View syntax & examples →Text

FIXED()

Rounds a number to a specified number of decimals and returns it as text, with commas by default.

FIXED(number, [decimals], [no_commas])View syntax & examples →Math & Trig

FLOOR()

Rounds a number down to the nearest multiple of a specified significance.

FLOOR(number, significance)View syntax & examples →Financial

FV()

Calculates the future value of an investment based on a constant interest rate and regular payments.

FV(rate, nper, pmt, [pv], [type])View syntax & examples →Math & Trig

GCD()

Returns the greatest common divisor of two or more integers.

GCD(number1, [number2], ...)View syntax & examples →Lookup & Reference

HLOOKUP()

Like VLOOKUP, but searches the top row of a range and returns a value from a row you specify below it.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])View syntax & examples →Date & Time

HOUR()

Extracts the hour (0-23) from a time or date/time value.

HOUR(serial_number)View syntax & examples →Lookup & Reference

HSTACK()

Combines two or more ranges or arrays side by side into a single array.

HSTACK(array1, [array2], ...)View syntax & examples →Logical

IF()

Returns one value if a condition is true and another if it's false.

IF(logical_test, value_if_true, [value_if_false])View syntax & examples →Logical

IFERROR()

Returns a fallback value if a formula evaluates to an error, otherwise returns the formula's normal result.

IFERROR(value, value_if_error)View syntax & examples →Logical

IFS()

Checks multiple conditions in order and returns the value for the first one that's true — no nesting required.

IFS(logical_test1, value1, [logical_test2, value2], ...)View syntax & examples →Lookup & Reference

INDEX()

Returns the value at a given row and column position within a range.

INDEX(array, row_num, [column_num])View syntax & examples →Lookup & Reference

INDIRECT()

Converts a text string into a cell or range reference, evaluated at the time the formula runs.

INDIRECT(ref_text, [a1])View syntax & examples →Math & Trig

INT()

Rounds a number down to the nearest integer.

INT(number)View syntax & examples →Financial

IRR()

Calculates the internal rate of return for a series of cash flows.

IRR(values, [guess])View syntax & examples →Information

ISBLANK()

Returns TRUE if a cell is completely empty, FALSE otherwise.

ISBLANK(value)View syntax & examples →Information

ISNUMBER()

Returns TRUE if a value is a number, FALSE otherwise.

ISNUMBER(value)View syntax & examples →Date & Time

ISOWEEKNUM()

Returns the ISO 8601 week number of the year for a given date.

ISOWEEKNUM(date)View syntax & examples →Information

ISTEXT()

Returns TRUE if a value is text, FALSE otherwise.

ISTEXT(value)View syntax & examples →Logical

LAMBDA()

Defines a custom, reusable function using Excel's own formula language — no VBA required.

LAMBDA([parameter1, parameter2, …,] calculation)View syntax & examples →Statistical

LARGE()

Returns the k-th largest value in a dataset.

LARGE(array, k)View syntax & examples →Math & Trig

LCM()

Returns the least common multiple of two or more integers.

LCM(number1, [number2], ...)View syntax & examples →Text

LEFT()

Returns a specified number of characters from the start of a text string.

LEFT(text, [num_chars])View syntax & examples →Text

LEN()

Returns the number of characters in a text string, including spaces.

LEN(text)View syntax & examples →Logical

LET()

Names intermediate calculations inside a formula so they can be reused and read like variables.

LET(name1, value1, [name2, value2, …], calculation)View syntax & examples →Text

LOWER()

Converts all letters in a text string to lowercase.

LOWER(text)View syntax & examples →Lookup & Reference

MATCH()

Returns the relative position of a value within a range, rather than the value itself.

MATCH(lookup_value, lookup_array, [match_type])View syntax & examples →Statistical

MAX()

Returns the largest value in a set of values or a range.

MAX(number1, [number2], ...)View syntax & examples →Statistical

MEDIAN()

Returns the middle value of a set of numbers.

MEDIAN(number1, [number2], ...)View syntax & examples →Text

MID()

Returns characters from the middle of a text string, starting at a position you specify.

MID(text, start_num, num_chars)View syntax & examples →Statistical

MIN()

Returns the smallest value in a set of values or a range.

MIN(number1, [number2], ...)View syntax & examples →Date & Time

MINUTE()

Extracts the minute (0-59) from a time or date/time value.

MINUTE(serial_number)View syntax & examples →Math & Trig

MOD()

Returns the remainder after a number is divided by a divisor.

MOD(number, divisor)View syntax & examples →Statistical

MODE.SNGL()

Returns the most frequently occurring number in a data set.

MODE.SNGL(number1, [number2], ...)View syntax & examples →Date & Time

MONTH()

Returns the month from a date as a number between 1 and 12.

MONTH(serial_number)View syntax & examples →Information

N()

Converts a value to a number: numbers pass through, TRUE becomes 1, and everything else becomes 0.

N(value)View syntax & examples →Date & Time

NETWORKDAYS()

Returns the number of working days between two dates, excluding weekends and optional holidays.

NETWORKDAYS(start_date, end_date, [holidays])View syntax & examples →Date & Time

NETWORKDAYS.INTL()

Counts whole working days between two dates using a customizable weekend schedule.

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])View syntax & examples →Logical

NOT()

Reverses TRUE to FALSE and FALSE to TRUE.

NOT(logical)View syntax & examples →Date & Time

NOW()

Returns the current date and time as a date serial number.

NOW()View syntax & examples →Financial

NPV()

Calculates the net present value of a series of future cash flows at a given discount rate.

NPV(rate, value1, [value2], ...)View syntax & examples →Text

NUMBERVALUE()

Converts text to a number using explicit decimal and group separators, regardless of regional settings.

NUMBERVALUE(text, [decimal_separator], [group_separator])View syntax & examples →Lookup & Reference

OFFSET()

Returns a reference offset from a starting cell by a given number of rows and columns, with optional height and width.

OFFSET(reference, rows, cols, [height], [width])View syntax & examples →Logical

OR()

Returns TRUE if any argument is true.

OR(logical1, [logical2], ...)View syntax & examples →Statistical

PERCENTILE.INC()

Returns the value at a given percentile within a data set, including the 0th and 100th percentiles.

PERCENTILE.INC(array, k)View syntax & examples →Financial

PMT()

Calculates the fixed periodic payment for a loan or investment.

PMT(rate, nper, pv, [fv], [type])View syntax & examples →Math & Trig

POWER()

Raises a number to a specified power.

POWER(number, power)View syntax & examples →Math & Trig

PRODUCT()

Multiplies all of its arguments together.

PRODUCT(number1, [number2], ...)View syntax & examples →Text

PROPER()

Converts text to title case — the first letter of each word is capitalised, the rest are lowercase.

PROPER(text)View syntax & examples →Financial

PV()

Calculates the present value of an investment: the lump sum today equivalent to a series of future payments.

PV(rate, nper, pmt, [fv], [type])View syntax & examples →Math & Trig

QUOTIENT()

Returns the integer portion of a division, discarding the remainder.

QUOTIENT(numerator, denominator)View syntax & examples →Math & Trig

RAND()

Returns a random decimal number between 0 and 1.

RAND()View syntax & examples →Math & Trig

RANDBETWEEN()

Returns a random whole number between two values you specify.

RANDBETWEEN(bottom, top)View syntax & examples →Statistical

RANK.EQ()

Returns the rank of a number within a list — tied values receive the same rank.

RANK.EQ(number, ref, [order])View syntax & examples →Financial

RATE()

Calculates the interest rate per period of a loan or annuity.

RATE(nper, pmt, pv, [fv], [type], [guess])View syntax & examples →Text

REPT()

Repeats a text string a specified number of times, useful for in-cell bars and separators.

REPT(text, number_times)View syntax & examples →Text

RIGHT()

Returns a specified number of characters from the end of a text string.

RIGHT(text, [num_chars])View syntax & examples →Math & Trig

ROMAN()

Converts an Arabic numeral into Roman numeral text.

ROMAN(number, [form])View syntax & examples →Math & Trig

ROUND()

Rounds a number to a specified number of decimal places.

ROUND(number, num_digits)View syntax & examples →Math & Trig

ROUNDUP()

Rounds a number away from zero, regardless of the digit that follows.

ROUNDUP(number, num_digits)View syntax & examples →Lookup & Reference

ROW()

Returns the row number of a reference, or the current row when used without one.

ROW([reference])View syntax & examples →Text

SEARCH()

Finds the position of one text string inside another, ignoring case, with wildcard support.

SEARCH(find_text, within_text, [start_num])View syntax & examples →Date & Time

SECOND()

Extracts the second (0-59) from a time or date/time value.

SECOND(serial_number)View syntax & examples →Lookup & Reference

SEQUENCE()

Generates a list of sequential numbers as a spilled array — no dragging or manual typing required.

SEQUENCE(rows, [columns], [start], [step])View syntax & examples →Statistical

SMALL()

Returns the k-th smallest value in a dataset.

SMALL(array, k)View syntax & examples →Lookup & Reference

SORT()

Sorts the contents of a range or array — requires Excel 365 or Excel 2021.

SORT(array, [sort_index], [sort_order], [by_col])View syntax & examples →Math & Trig

SQRT()

Returns the positive square root of a number.

SQRT(number)View syntax & examples →Statistical

STDEV.S()

Estimates standard deviation based on a sample.

STDEV.S(number1, [number2], ...)View syntax & examples →Text

SUBSTITUTE()

Replaces all or specific occurrences of a substring with a new string.

SUBSTITUTE(text, old_text, new_text, [instance_num])View syntax & examples →Math & Trig

SUM()

Adds up a range of numbers or a list of values.

SUM(number1, [number2], ...)View syntax & examples →Math & Trig

SUMIF()

Sums the cells in a range that meet a single condition.

SUMIF(range, criteria, [sum_range])View syntax & examples →Math & Trig

SUMIFS()

Sums cells that meet multiple conditions at once.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)View syntax & examples →Math & Trig

SUMPRODUCT()

Multiplies corresponding items across arrays and adds up the results — a flexible way to do conditional sums without helper columns.

SUMPRODUCT(array1, [array2], [array3], ...)View syntax & examples →Math & Trig

SUMSQ()

Adds the squares of a set of numbers.

SUMSQ(number1, [number2], ...)View syntax & examples →Logical

SWITCH()

Tests an expression against a list of values and returns the result for the first match.

SWITCH(expression, value1, result1, [value2, result2, ...], [default])View syntax & examples →Text

T()

Returns its argument unchanged if it is text, or an empty string if it is not.

T(value)View syntax & examples →Lookup & Reference

TAKE()

Returns a specified number of rows or columns from the start or end of an array.

TAKE(array, rows, [columns])View syntax & examples →Text

TEXT()

Formats a number as text using a number format code.

TEXT(value, format_text)View syntax & examples →Text

TEXTJOIN()

Joins text with a delimiter between each item, and can skip blank cells.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)View syntax & examples →Date & Time

TIME()

Combines separate hour, minute, and second numbers into a single time value.

TIME(hour, minute, second)View syntax & examples →Date & Time

TODAY()

Returns the current date, updated automatically whenever the sheet recalculates.

TODAY()View syntax & examples →Lookup & Reference

TRANSPOSE()

Rotates a range so that rows become columns and columns become rows.

TRANSPOSE(array)View syntax & examples →Text

TRIM()

Removes all leading, trailing, and extra internal spaces from a text string.

TRIM(text)View syntax & examples →Math & Trig

TRUNC()

Removes the fractional part of a number, without rounding.

TRUNC(number, [num_digits])View syntax & examples →Text

UNICHAR()

Returns the character that corresponds to a given Unicode code point number.

UNICHAR(number)View syntax & examples →Text

UNICODE()

Returns the Unicode code point number for the first character of a text string.

UNICODE(text)View syntax & examples →Lookup & Reference

UNIQUE()

Returns the unique values from a range or array — requires Excel 365 or Excel 2021.

UNIQUE(array, [by_col], [exactly_once])View syntax & examples →Text

UPPER()

Converts all letters in a text string to uppercase.

UPPER(text)View syntax & examples →Text

VALUE()

Converts a text string that represents a number into a numeric value.

VALUE(text)View syntax & examples →Statistical

VAR.S()

Estimates variance based on a sample.

VAR.S(number1, [number2], ...)View syntax & examples →Lookup & Reference

VLOOKUP()

Looks up a value in the first column of a range and returns a value from another column in the same row.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])View syntax & examples →Lookup & Reference

VSTACK()

Stacks multiple ranges or arrays vertically into a single array.

VSTACK(array1, [array2], …)View syntax & examples →Date & Time

WEEKDAY()

Returns which day of the week a date falls on, as a number.

WEEKDAY(serial_number, [return_type])View syntax & examples →Date & Time

WEEKNUM()

Returns the week number of the year for a given date.

WEEKNUM(serial_number, [return_type])View syntax & examples →Date & Time

WORKDAY()

Returns the date a given number of working days before or after a start date, skipping weekends and optional holidays.

WORKDAY(start_date, days, [holidays])View syntax & examples →Date & Time

WORKDAY.INTL()

Returns the date a given number of working days before or after a start date, using a customizable weekend schedule.

WORKDAY.INTL(start_date, days, [weekend], [holidays])View syntax & examples →Lookup & Reference

XLOOKUP()

The modern replacement for VLOOKUP — searches any direction, defaults to an exact match, and returns a custom value when nothing is found.

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])View syntax & examples →Lookup & Reference

XMATCH()

Returns the relative position of a value within a range or array, with more flexible matching than MATCH.

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])View syntax & examples →Date & Time

YEAR()

Returns the year from a date as a four-digit number.

YEAR(serial_number)View syntax & examples →Date & Time

YEARFRAC()

Calculates the fraction of a year between two dates, using a chosen day-count basis.

YEARFRAC(start_date, end_date, [basis])View syntax & examples →

Sources we reference — not partners

26 places we cite for accuracy. We link to them; none has reviewed, endorsed, or collaborated on this site.

Official documentation

Educators and channels

Course platforms

AI & data tools