For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
REFERENCE

The free Excel function reference | all 131 functions explained

Every argument, return value, common error fix (#N/A, #VALUE!), and worked example explained in plain English.

Inspect XLOOKUP()

Not sure which function you need?

Tell our interactive Formula Builder what you want to calculate and get the exact formula in seconds.

Open Formula Builder

Which function family are you looking up today?

Browse by family

8 function families · 131 guides

Practice in Classroom →

131 functions

Math & Trig

ABS()

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

ABS(number)
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])
Logical

AND()

Returns TRUE only if every argument is true.

AND(logical1, [logical2], ...)
Lookup & Reference

AREAS()

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

AREAS(reference)
Statistical

AVERAGE()

Calculates the arithmetic mean of a set of numbers.

AVERAGE(number1, [number2], ...)
Statistical

AVERAGEIF()

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

AVERAGEIF(range, criteria, [average_range])
Math & Trig

CEILING()

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

CEILING(number, significance)
Text

CHAR()

Returns the character that corresponds to a given character code.

CHAR(number)
Lookup & Reference

CHOOSE()

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

CHOOSE(index_num, value1, [value2], ...)
Text

CLEAN()

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

CLEAN(text)
Text

CODE()

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

CODE(text)
Lookup & Reference

COLUMN()

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

COLUMN([reference])
Text

CONCAT()

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

CONCAT(text1, [text2], ...)
Text

CONCATENATE()

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

CONCATENATE(text1, [text2], ...)
Statistical

COUNT()

Counts how many cells or arguments contain numbers.

COUNT(value1, [value2], ...)
Statistical

COUNTA()

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

COUNTA(value1, [value2], ...)
Statistical

COUNTBLANK()

Counts the number of empty cells in a range.

COUNTBLANK(range)
Statistical

COUNTIF()

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

COUNTIF(range, criteria)
Statistical

COUNTIFS()

Counts cells that meet multiple conditions at once.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Date & Time

DATE()

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

DATE(year, month, day)
Date & Time

DATEDIF()

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

DATEDIF(start_date, end_date, unit)
Date & Time

DATEVALUE()

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

DATEVALUE(date_text)
Date & Time

DAY()

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

DAY(serial_number)
Date & Time

DAYS()

Returns the number of days between two dates.

DAYS(end_date, start_date)
Text

DOLLAR()

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

DOLLAR(number, [decimals])
Lookup & Reference

DROP()

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

DROP(array, rows, [columns])
Date & Time

EDATE()

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

EDATE(start_date, months)
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)
Text

EXACT()

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

EXACT(text1, text2)
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])
Text

FIND()

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

FIND(find_text, within_text, [start_num])
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])
Math & Trig

FLOOR()

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

FLOOR(number, significance)
Financial

FV()

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

FV(rate, nper, pmt, [pv], [type])
Math & Trig

GCD()

Returns the greatest common divisor of two or more integers.

GCD(number1, [number2], ...)
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])
Date & Time

HOUR()

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

HOUR(serial_number)
Lookup & Reference

HSTACK()

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

HSTACK(array1, [array2], ...)
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])
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)
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], ...)
Lookup & Reference

INDEX()

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

INDEX(array, row_num, [column_num])
Lookup & Reference

INDIRECT()

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

INDIRECT(ref_text, [a1])
Math & Trig

INT()

Rounds a number down to the nearest integer.

INT(number)
Financial

IRR()

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

IRR(values, [guess])
Information

ISBLANK()

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

ISBLANK(value)
Information

ISNUMBER()

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

ISNUMBER(value)
Date & Time

ISOWEEKNUM()

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

ISOWEEKNUM(date)
Information

ISTEXT()

Returns TRUE if a value is text, FALSE otherwise.

ISTEXT(value)
Logical

LAMBDA()

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

LAMBDA([parameter1, parameter2, …,] calculation)
Statistical

LARGE()

Returns the k-th largest value in a dataset.

LARGE(array, k)
Math & Trig

LCM()

Returns the least common multiple of two or more integers.

LCM(number1, [number2], ...)
Text

LEFT()

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

LEFT(text, [num_chars])
Text

LEN()

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

LEN(text)
Logical

LET()

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

LET(name1, value1, [name2, value2, …], calculation)
Text

LOWER()

Converts all letters in a text string to lowercase.

LOWER(text)
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])
Statistical

MAX()

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

MAX(number1, [number2], ...)
Statistical

MEDIAN()

Returns the middle value of a set of numbers.

MEDIAN(number1, [number2], ...)
Text

MID()

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

MID(text, start_num, num_chars)
Statistical

MIN()

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

MIN(number1, [number2], ...)
Date & Time

MINUTE()

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

MINUTE(serial_number)
Math & Trig

MOD()

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

MOD(number, divisor)
Statistical

MODE.SNGL()

Returns the most frequently occurring number in a data set.

MODE.SNGL(number1, [number2], ...)
Date & Time

MONTH()

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

MONTH(serial_number)
Information

N()

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

N(value)
Date & Time

NETWORKDAYS()

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

NETWORKDAYS(start_date, end_date, [holidays])
Date & Time

NETWORKDAYS.INTL()

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

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
Logical

NOT()

Reverses TRUE to FALSE and FALSE to TRUE.

NOT(logical)
Date & Time

NOW()

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

NOW()
Financial

NPV()

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

NPV(rate, value1, [value2], ...)
Text

NUMBERVALUE()

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

NUMBERVALUE(text, [decimal_separator], [group_separator])
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])
Logical

OR()

Returns TRUE if any argument is true.

OR(logical1, [logical2], ...)
Statistical

PERCENTILE.INC()

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

PERCENTILE.INC(array, k)
Financial

PMT()

Calculates the fixed periodic payment for a loan or investment.

PMT(rate, nper, pv, [fv], [type])
Math & Trig

POWER()

Raises a number to a specified power.

POWER(number, power)
Math & Trig

PRODUCT()

Multiplies all of its arguments together.

PRODUCT(number1, [number2], ...)
Text

PROPER()

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

PROPER(text)
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])
Math & Trig

QUOTIENT()

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

QUOTIENT(numerator, denominator)
Math & Trig

RAND()

Returns a random decimal number between 0 and 1.

RAND()
Math & Trig

RANDBETWEEN()

Returns a random whole number between two values you specify.

RANDBETWEEN(bottom, top)
Statistical

RANK.EQ()

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

RANK.EQ(number, ref, [order])
Financial

RATE()

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

RATE(nper, pmt, pv, [fv], [type], [guess])
Text

REPT()

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

REPT(text, number_times)
Text

RIGHT()

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

RIGHT(text, [num_chars])
Math & Trig

ROMAN()

Converts an Arabic numeral into Roman numeral text.

ROMAN(number, [form])
Math & Trig

ROUND()

Rounds a number to a specified number of decimal places.

ROUND(number, num_digits)
Math & Trig

ROUNDUP()

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

ROUNDUP(number, num_digits)
Lookup & Reference

ROW()

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

ROW([reference])
Text

SEARCH()

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

SEARCH(find_text, within_text, [start_num])
Date & Time

SECOND()

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

SECOND(serial_number)
Lookup & Reference

SEQUENCE()

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

SEQUENCE(rows, [columns], [start], [step])
Statistical

SMALL()

Returns the k-th smallest value in a dataset.

SMALL(array, k)
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])
Math & Trig

SQRT()

Returns the positive square root of a number.

SQRT(number)
Statistical

STDEV.S()

Estimates standard deviation based on a sample.

STDEV.S(number1, [number2], ...)
Text

SUBSTITUTE()

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

SUBSTITUTE(text, old_text, new_text, [instance_num])
Math & Trig

SUBTOTAL()

Aggregates a range with a chosen function, and can ignore rows hidden by a filter.

SUBTOTAL(function_num, ref1, [ref2], ...)
Math & Trig

SUM()

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

SUM(number1, [number2], ...)
Math & Trig

SUMIF()

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

SUMIF(range, criteria, [sum_range])
Math & Trig

SUMIFS()

Sums cells that meet multiple conditions at once.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
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], ...)
Math & Trig

SUMSQ()

Adds the squares of a set of numbers.

SUMSQ(number1, [number2], ...)
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])
Text

T()

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

T(value)
Lookup & Reference

TAKE()

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

TAKE(array, rows, [columns])
Text

TEXT()

Formats a number as text using a number format code.

TEXT(value, format_text)
Text

TEXTJOIN()

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

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Date & Time

TIME()

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

TIME(hour, minute, second)
Date & Time

TODAY()

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

TODAY()
Lookup & Reference

TRANSPOSE()

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

TRANSPOSE(array)
Text

TRIM()

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

TRIM(text)
Math & Trig

TRUNC()

Removes the fractional part of a number, without rounding.

TRUNC(number, [num_digits])
Text

UNICHAR()

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

UNICHAR(number)
Text

UNICODE()

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

UNICODE(text)
Lookup & Reference

UNIQUE()

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

UNIQUE(array, [by_col], [exactly_once])
Text

UPPER()

Converts all letters in a text string to uppercase.

UPPER(text)
Text

VALUE()

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

VALUE(text)
Statistical

VAR.S()

Estimates variance based on a sample.

VAR.S(number1, [number2], ...)
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])
Lookup & Reference

VSTACK()

Stacks multiple ranges or arrays vertically into a single array.

VSTACK(array1, [array2], …)
Date & Time

WEEKDAY()

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

WEEKDAY(serial_number, [return_type])
Date & Time

WEEKNUM()

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

WEEKNUM(serial_number, [return_type])
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])
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])
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])
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])
Date & Time

YEAR()

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

YEAR(serial_number)
Date & Time

YEARFRAC()

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

YEARFRAC(start_date, end_date, [basis])