Free ebook
Free ebook: Excel Formulas Cheat Sheet
Every function on this page in a printable cheat sheet — syntax, gotchas, and real examples. Read online or grab the PDF.
Get the ebookFree tools
Build formulas with our free tools
The formula builder writes the formula for you, and the explainer breaks down any formula in plain English — no upload, no sign-up.
Open formula builderWhich function family are you looking up today?
Browse by family
8 function families · 131 guides
131 functions
ABS()
Returns the absolute value of a number — the number without its sign.
ABS(number)Lookup & ReferenceADDRESS()
Builds a cell reference as text from a row and column number.
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])LogicalAND()
Returns TRUE only if every argument is true.
AND(logical1, [logical2], ...)Lookup & ReferenceAREAS()
Returns the number of separate ranges (areas) contained in a reference.
AREAS(reference)StatisticalAVERAGE()
Calculates the arithmetic mean of a set of numbers.
AVERAGE(number1, [number2], ...)StatisticalAVERAGEIF()
Averages the cells in a range that meet a single condition.
AVERAGEIF(range, criteria, [average_range])Math & TrigCEILING()
Rounds a number up to the nearest multiple of a specified significance.
CEILING(number, significance)TextCHAR()
Returns the character that corresponds to a given character code.
CHAR(number)Lookup & ReferenceCHOOSE()
Returns a value from a list based on an index number.
CHOOSE(index_num, value1, [value2], ...)TextCLEAN()
Strips the 32 non-printable control characters that often arrive with imported or pasted text.
CLEAN(text)TextCODE()
Returns the numeric character code for the first character of a text string.
CODE(text)Lookup & ReferenceCOLUMN()
Returns the column number of a reference, or the current column when used without one.
COLUMN([reference])TextCONCAT()
Joins text from multiple cells or strings together, with no delimiter.
CONCAT(text1, [text2], ...)TextCONCATENATE()
Joins two or more text strings into a single string, with no separator added automatically.
CONCATENATE(text1, [text2], ...)StatisticalCOUNT()
Counts how many cells or arguments contain numbers.
COUNT(value1, [value2], ...)StatisticalCOUNTA()
Counts the number of non-empty cells in a range.
COUNTA(value1, [value2], ...)StatisticalCOUNTBLANK()
Counts the number of empty cells in a range.
COUNTBLANK(range)StatisticalCOUNTIF()
Counts the cells in a range that meet a single condition.
COUNTIF(range, criteria)StatisticalCOUNTIFS()
Counts cells that meet multiple conditions at once.
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)Date & TimeDATE()
Builds a date from separate year, month, and day numbers.
DATE(year, month, day)Date & TimeDATEDIF()
Calculates the difference between two dates expressed in complete years, months, or days.
DATEDIF(start_date, end_date, unit)Date & TimeDATEVALUE()
Converts a date stored as text into a true Excel date serial number.
DATEVALUE(date_text)Date & TimeDAY()
Returns the day of the month from a date as a number between 1 and 31.
DAY(serial_number)Date & TimeDAYS()
Returns the number of days between two dates.
DAYS(end_date, start_date)TextDOLLAR()
Rounds a number and converts it to text formatted as currency.
DOLLAR(number, [decimals])Lookup & ReferenceDROP()
Removes a specified number of rows or columns from the start or end of an array.
DROP(array, rows, [columns])Date & TimeEDATE()
Returns the date a specified number of months before or after a start date.
EDATE(start_date, months)Date & TimeEOMONTH()
Returns the last day of the month a specified number of months before or after a start date.
EOMONTH(start_date, months)TextEXACT()
Compares two text strings and returns TRUE only when they match exactly, including letter case.
EXACT(text1, text2)Lookup & ReferenceFILTER()
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])TextFIND()
Returns the position of one text string within another — case-sensitive.
FIND(find_text, within_text, [start_num])TextFIXED()
Rounds a number to a specified number of decimals and returns it as text, with commas by default.
FIXED(number, [decimals], [no_commas])Math & TrigFLOOR()
Rounds a number down to the nearest multiple of a specified significance.
FLOOR(number, significance)FinancialFV()
Calculates the future value of an investment based on a constant interest rate and regular payments.
FV(rate, nper, pmt, [pv], [type])Math & TrigGCD()
Returns the greatest common divisor of two or more integers.
GCD(number1, [number2], ...)Lookup & ReferenceHLOOKUP()
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 & TimeHOUR()
Extracts the hour (0-23) from a time or date/time value.
HOUR(serial_number)Lookup & ReferenceHSTACK()
Combines two or more ranges or arrays side by side into a single array.
HSTACK(array1, [array2], ...)LogicalIF()
Returns one value if a condition is true and another if it's false.
IF(logical_test, value_if_true, [value_if_false])LogicalIFERROR()
Returns a fallback value if a formula evaluates to an error, otherwise returns the formula's normal result.
IFERROR(value, value_if_error)LogicalIFS()
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 & ReferenceINDEX()
Returns the value at a given row and column position within a range.
INDEX(array, row_num, [column_num])Lookup & ReferenceINDIRECT()
Converts a text string into a cell or range reference, evaluated at the time the formula runs.
INDIRECT(ref_text, [a1])Math & TrigINT()
Rounds a number down to the nearest integer.
INT(number)FinancialIRR()
Calculates the internal rate of return for a series of cash flows.
IRR(values, [guess])InformationISBLANK()
Returns TRUE if a cell is completely empty, FALSE otherwise.
ISBLANK(value)InformationISNUMBER()
Returns TRUE if a value is a number, FALSE otherwise.
ISNUMBER(value)Date & TimeISOWEEKNUM()
Returns the ISO 8601 week number of the year for a given date.
ISOWEEKNUM(date)InformationISTEXT()
Returns TRUE if a value is text, FALSE otherwise.
ISTEXT(value)LogicalLAMBDA()
Defines a custom, reusable function using Excel's own formula language — no VBA required.
LAMBDA([parameter1, parameter2, …,] calculation)StatisticalLARGE()
Returns the k-th largest value in a dataset.
LARGE(array, k)Math & TrigLCM()
Returns the least common multiple of two or more integers.
LCM(number1, [number2], ...)TextLEFT()
Returns a specified number of characters from the start of a text string.
LEFT(text, [num_chars])TextLEN()
Returns the number of characters in a text string, including spaces.
LEN(text)LogicalLET()
Names intermediate calculations inside a formula so they can be reused and read like variables.
LET(name1, value1, [name2, value2, …], calculation)TextLOWER()
Converts all letters in a text string to lowercase.
LOWER(text)Lookup & ReferenceMATCH()
Returns the relative position of a value within a range, rather than the value itself.
MATCH(lookup_value, lookup_array, [match_type])StatisticalMAX()
Returns the largest value in a set of values or a range.
MAX(number1, [number2], ...)StatisticalMEDIAN()
Returns the middle value of a set of numbers.
MEDIAN(number1, [number2], ...)TextMID()
Returns characters from the middle of a text string, starting at a position you specify.
MID(text, start_num, num_chars)StatisticalMIN()
Returns the smallest value in a set of values or a range.
MIN(number1, [number2], ...)Date & TimeMINUTE()
Extracts the minute (0-59) from a time or date/time value.
MINUTE(serial_number)Math & TrigMOD()
Returns the remainder after a number is divided by a divisor.
MOD(number, divisor)StatisticalMODE.SNGL()
Returns the most frequently occurring number in a data set.
MODE.SNGL(number1, [number2], ...)Date & TimeMONTH()
Returns the month from a date as a number between 1 and 12.
MONTH(serial_number)InformationN()
Converts a value to a number: numbers pass through, TRUE becomes 1, and everything else becomes 0.
N(value)Date & TimeNETWORKDAYS()
Returns the number of working days between two dates, excluding weekends and optional holidays.
NETWORKDAYS(start_date, end_date, [holidays])Date & TimeNETWORKDAYS.INTL()
Counts whole working days between two dates using a customizable weekend schedule.
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])LogicalNOT()
Reverses TRUE to FALSE and FALSE to TRUE.
NOT(logical)Date & TimeNOW()
Returns the current date and time as a date serial number.
NOW()FinancialNPV()
Calculates the net present value of a series of future cash flows at a given discount rate.
NPV(rate, value1, [value2], ...)TextNUMBERVALUE()
Converts text to a number using explicit decimal and group separators, regardless of regional settings.
NUMBERVALUE(text, [decimal_separator], [group_separator])Lookup & ReferenceOFFSET()
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])LogicalOR()
Returns TRUE if any argument is true.
OR(logical1, [logical2], ...)StatisticalPERCENTILE.INC()
Returns the value at a given percentile within a data set, including the 0th and 100th percentiles.
PERCENTILE.INC(array, k)FinancialPMT()
Calculates the fixed periodic payment for a loan or investment.
PMT(rate, nper, pv, [fv], [type])Math & TrigPOWER()
Raises a number to a specified power.
POWER(number, power)Math & TrigPRODUCT()
Multiplies all of its arguments together.
PRODUCT(number1, [number2], ...)TextPROPER()
Converts text to title case — the first letter of each word is capitalised, the rest are lowercase.
PROPER(text)FinancialPV()
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 & TrigQUOTIENT()
Returns the integer portion of a division, discarding the remainder.
QUOTIENT(numerator, denominator)Math & TrigRAND()
Returns a random decimal number between 0 and 1.
RAND()Math & TrigRANDBETWEEN()
Returns a random whole number between two values you specify.
RANDBETWEEN(bottom, top)StatisticalRANK.EQ()
Returns the rank of a number within a list — tied values receive the same rank.
RANK.EQ(number, ref, [order])FinancialRATE()
Calculates the interest rate per period of a loan or annuity.
RATE(nper, pmt, pv, [fv], [type], [guess])TextREPT()
Repeats a text string a specified number of times, useful for in-cell bars and separators.
REPT(text, number_times)TextRIGHT()
Returns a specified number of characters from the end of a text string.
RIGHT(text, [num_chars])Math & TrigROMAN()
Converts an Arabic numeral into Roman numeral text.
ROMAN(number, [form])Math & TrigROUND()
Rounds a number to a specified number of decimal places.
ROUND(number, num_digits)Math & TrigROUNDUP()
Rounds a number away from zero, regardless of the digit that follows.
ROUNDUP(number, num_digits)Lookup & ReferenceROW()
Returns the row number of a reference, or the current row when used without one.
ROW([reference])TextSEARCH()
Finds the position of one text string inside another, ignoring case, with wildcard support.
SEARCH(find_text, within_text, [start_num])Date & TimeSECOND()
Extracts the second (0-59) from a time or date/time value.
SECOND(serial_number)Lookup & ReferenceSEQUENCE()
Generates a list of sequential numbers as a spilled array — no dragging or manual typing required.
SEQUENCE(rows, [columns], [start], [step])StatisticalSMALL()
Returns the k-th smallest value in a dataset.
SMALL(array, k)Lookup & ReferenceSORT()
Sorts the contents of a range or array — requires Excel 365 or Excel 2021.
SORT(array, [sort_index], [sort_order], [by_col])Math & TrigSQRT()
Returns the positive square root of a number.
SQRT(number)StatisticalSTDEV.S()
Estimates standard deviation based on a sample.
STDEV.S(number1, [number2], ...)TextSUBSTITUTE()
Replaces all or specific occurrences of a substring with a new string.
SUBSTITUTE(text, old_text, new_text, [instance_num])Math & TrigSUBTOTAL()
Aggregates a range with a chosen function, and can ignore rows hidden by a filter.
SUBTOTAL(function_num, ref1, [ref2], ...)Math & TrigSUM()
Adds up a range of numbers or a list of values.
SUM(number1, [number2], ...)Math & TrigSUMIF()
Sums the cells in a range that meet a single condition.
SUMIF(range, criteria, [sum_range])Math & TrigSUMIFS()
Sums cells that meet multiple conditions at once.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Math & TrigSUMPRODUCT()
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 & TrigSUMSQ()
Adds the squares of a set of numbers.
SUMSQ(number1, [number2], ...)LogicalSWITCH()
Tests an expression against a list of values and returns the result for the first match.
SWITCH(expression, value1, result1, [value2, result2, ...], [default])TextT()
Returns its argument unchanged if it is text, or an empty string if it is not.
T(value)Lookup & ReferenceTAKE()
Returns a specified number of rows or columns from the start or end of an array.
TAKE(array, rows, [columns])TextTEXT()
Formats a number as text using a number format code.
TEXT(value, format_text)TextTEXTJOIN()
Joins text with a delimiter between each item, and can skip blank cells.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)Date & TimeTIME()
Combines separate hour, minute, and second numbers into a single time value.
TIME(hour, minute, second)Date & TimeTODAY()
Returns the current date, updated automatically whenever the sheet recalculates.
TODAY()Lookup & ReferenceTRANSPOSE()
Rotates a range so that rows become columns and columns become rows.
TRANSPOSE(array)TextTRIM()
Removes all leading, trailing, and extra internal spaces from a text string.
TRIM(text)Math & TrigTRUNC()
Removes the fractional part of a number, without rounding.
TRUNC(number, [num_digits])TextUNICHAR()
Returns the character that corresponds to a given Unicode code point number.
UNICHAR(number)TextUNICODE()
Returns the Unicode code point number for the first character of a text string.
UNICODE(text)Lookup & ReferenceUNIQUE()
Returns the unique values from a range or array — requires Excel 365 or Excel 2021.
UNIQUE(array, [by_col], [exactly_once])TextUPPER()
Converts all letters in a text string to uppercase.
UPPER(text)TextVALUE()
Converts a text string that represents a number into a numeric value.
VALUE(text)StatisticalVAR.S()
Estimates variance based on a sample.
VAR.S(number1, [number2], ...)Lookup & ReferenceVLOOKUP()
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 & ReferenceVSTACK()
Stacks multiple ranges or arrays vertically into a single array.
VSTACK(array1, [array2], …)Date & TimeWEEKDAY()
Returns which day of the week a date falls on, as a number.
WEEKDAY(serial_number, [return_type])Date & TimeWEEKNUM()
Returns the week number of the year for a given date.
WEEKNUM(serial_number, [return_type])Date & TimeWORKDAY()
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 & TimeWORKDAY.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 & ReferenceXLOOKUP()
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 & ReferenceXMATCH()
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 & TimeYEAR()
Returns the year from a date as a four-digit number.
YEAR(serial_number)Date & TimeYEARFRAC()
Calculates the fraction of a year between two dates, using a chosen day-count basis.
YEARFRAC(start_date, end_date, [basis])


