Excel formulas cheat sheet

The syntax of every function on this site on one page, with the basics of formulas at the top. Tap a function to open its page with worked examples. Arguments in square brackets are optional.

How to read this sheet

Each line in the function tables below shows a function the way Excel's own tip shows it while you type: the name, then its arguments in order, separated by commas. An argument in square brackets may be left out, and Excel then uses a default, which the function's page explains. Three dots mean you may add more of the same kind.

The words used for arguments are Excel's own, so they match what you see on screen. A few appear again and again. A range or array is a block of cells such as A2:A10. Criteria is a condition, written as in the conditions table below. A serial_number is a date, which Excel stores as a number. Text is a piece of text or a cell holding one.

The sheet is a reminder, not a lesson. If a line does not make sense, tap the function name. Its page shows the function at work on a small sample sheet, with results calculated in Excel.

If your Excel uses a semicolon between arguments, because of your regional settings, read every comma in these formulas as a semicolon.

Formula basics

To do this	Write
start a formula	`=`
add, subtract, multiply, divide	`+` `-` `*` `/`
raise to a power	`^`
join text	`&`
compare	`=` `<>` `>` `<` `>=` `<=`
a range of cells	`A2:A10`
a whole column	`A:A`
a cell on another sheet	`Sheet2!A1`
text inside a formula	`"in double quotation marks"`

Order of calculation: brackets, then powers, then multiply and divide, then add and subtract.

Reference styles

Reference	When copied
`A1`	row and column both change
`$A$1`	nothing changes
`A$1`	the row stays, the column changes
`$A1`	the column stays, the row changes

Press F4 while editing to cycle through them. See cell references.

Conditions in SUMIF, COUNTIF and their relatives

Condition	Meaning
`"North"`	equal to the text North
`100`	equal to 100
`">100"`	greater than 100
`"<>0"`	not equal to zero
`">="&B1`	greater than or equal to the value in B1
`"A*"`	starts with A
`"kg"`	contains kg
`""`	empty
`"<>"`	not empty

Error values

Error	Meaning
`#N/A`	lookup value not found
`#VALUE!`	wrong kind of data
`#REF!`	reference to cells that do not exist
`#DIV/0!`	division by zero or an empty cell
`#NAME?`	unknown function or name, or text without quotation marks
`#NUM!`	impossible number
`#SPILL!`	results blocked by other cells
`#####`	column too narrow

See formula errors.

Keyboard shortcuts for formulas

Keys	Action
F2	edit the cell
F4	add or change dollar signs in a reference
F9	recalculate
Alt and =	AutoSum
Ctrl and `	show formulas or results
Ctrl and D	fill down
Ctrl and E	Flash Fill
Ctrl and ;	enter today's date
Ctrl, Shift and Enter	enter an array formula in older versions
Tab	accept a suggested function name

These are the keys on Windows. On a Mac, use Cmd in place of Ctrl for most of them.

Format codes for TEXT

Code	Shows
`"0.00"`	two decimals
`"#,##0"`	thousands separator
`"0%"`	percentage
`"dd-mmm-yyyy"`	05-Jan-2026
`"dddd"`	Monday
`"mmmm yyyy"`	January 2026
`"h:mm AM/PM"`	6:30 PM

Lookup

`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`	VLOOKUP finds a value in the first column of a table and returns a value from the same row in another column
`=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`	HLOOKUP finds a value in the top row of a table and returns a value from the same column in a row below
`=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`	XLOOKUP finds a value in one range and returns the matching value from another range, in any direction
`=INDEX(array, row_num, [column_num])`	INDEX returns the value at a given row and column position inside a range
`=MATCH(lookup_value, lookup_array, [match_type])`	MATCH returns the position of a value in a row or column
`=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])`	XMATCH returns the position of a value in a range, with exact match as the default and a choice of search direction
`=CHOOSE(index_num, value1, [value2], ...)`	CHOOSE returns one value from a list that you type into the formula, picked by its position number

Logical

`=IF(logical_test, value_if_true, [value_if_false])`	IF checks a condition and returns one value when it is true and another when it is false
`=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)`	IFS checks several conditions in order and returns the result for the first one that is true
`=AND(logical1, [logical2], ...)`	AND is true when every condition is true, OR when at least one is true, and NOT reverses a condition
`=OR(logical1, [logical2], ...)`	AND is true when every condition is true, OR when at least one is true, and NOT reverses a condition
`=NOT(logical)`	AND is true when every condition is true, OR when at least one is true, and NOT reverses a condition
`=IFERROR(value, value_if_error)`	IFERROR returns a value you choose when a formula gives an error, and the formula's own result when it does not
`=IFNA(value, value_if_na)`	IFERROR returns a value you choose when a formula gives an error, and the formula's own result when it does not
`=SWITCH(expression, value1, result1, [value2, result2], ..., [default])`	SWITCH compares one value with a list of possible values and returns the result that goes with the first match

Math

`=SUM(number1, [number2], ...)`	SUM adds up numbers, cells and ranges
`=SUMIF(range, criteria, [sum_range])`	SUMIF adds up the cells that meet one condition
`=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`	SUMIFS adds up the cells that meet several conditions at once
`=SUMPRODUCT(array1, [array2], ...)`	SUMPRODUCT multiplies ranges together item by item and adds up the results
`=ROUND(number, num_digits)`	ROUND rounds a number to a chosen number of decimal places; ROUNDUP and ROUNDDOWN force the direction, and MROUND rounds to a multiple
`=ROUNDUP(number, num_digits)`	ROUND rounds a number to a chosen number of decimal places; ROUNDUP and ROUNDDOWN force the direction, and MROUND rounds to a multiple
`=ROUNDDOWN(number, num_digits)`	ROUND rounds a number to a chosen number of decimal places; ROUNDUP and ROUNDDOWN force the direction, and MROUND rounds to a multiple
`=INT(number)`	INT rounds a number down to the whole number below it; TRUNC simply cuts off the decimals
`=TRUNC(number, [num_digits])`	INT rounds a number down to the whole number below it; TRUNC simply cuts off the decimals
`=MOD(number, divisor)`	MOD returns the remainder after one number is divided by another
`=SUBTOTAL(function_num, ref1, [ref2], ...)`	SUBTOTAL totals, counts or averages a list while ignoring rows hidden by a filter

Statistical

`=AVERAGE(number1, [number2], ...)`	AVERAGE gives the arithmetic mean of a set of numbers; MEDIAN gives the middle value
`=MEDIAN(number1, [number2], ...)`	AVERAGE gives the arithmetic mean of a set of numbers; MEDIAN gives the middle value
`=AVERAGEIF(range, criteria, [average_range])`	AVERAGEIF averages the cells that meet one condition; AVERAGEIFS allows several conditions
`=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`	AVERAGEIF averages the cells that meet one condition; AVERAGEIFS allows several conditions
`=COUNT(value1, [value2], ...)`	COUNT counts cells that hold numbers, COUNTA counts cells that are not empty, and COUNTBLANK counts empty cells
`=COUNTA(value1, [value2], ...)`	COUNT counts cells that hold numbers, COUNTA counts cells that are not empty, and COUNTBLANK counts empty cells
`=COUNTBLANK(range)`	COUNT counts cells that hold numbers, COUNTA counts cells that are not empty, and COUNTBLANK counts empty cells
`=COUNTIF(range, criteria)`	COUNTIF counts the cells in a range that meet one condition
`=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)`	COUNTIFS counts the rows that meet several conditions at once
`=MAX(number1, [number2], ...)`	MAX and MIN return the largest and smallest numbers in a range; MAXIFS and MINIFS do the same for the rows that meet conditions
`=MIN(number1, [number2], ...)`	MAX and MIN return the largest and smallest numbers in a range; MAXIFS and MINIFS do the same for the rows that meet conditions
`=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`	MAX and MIN return the largest and smallest numbers in a range; MAXIFS and MINIFS do the same for the rows that meet conditions
`=LARGE(array, k)`	LARGE returns the nth largest value in a range and SMALL returns the nth smallest
`=SMALL(array, k)`	LARGE returns the nth largest value in a range and SMALL returns the nth smallest
`=RANK.EQ(number, ref, [order])`	RANK.EQ gives the position of a number within a list, with equal numbers sharing the same rank
`=RANK.AVG(number, ref, [order])`	RANK.EQ gives the position of a number within a list, with equal numbers sharing the same rank

Text

`=LEFT(text, [num_chars])`	LEFT returns characters from the start of a text and RIGHT returns characters from the end
`=RIGHT(text, [num_chars])`	LEFT returns characters from the start of a text and RIGHT returns characters from the end
`=MID(text, start_num, num_chars)`	MID returns characters from the middle of a text, starting at a position you give
`=LEN(text)`	LEN returns the number of characters in a text, counting spaces
`=TRIM(text)`	TRIM removes extra spaces from a text, leaving single spaces between words; CLEAN removes characters that cannot be printed
`=CLEAN(text)`	TRIM removes extra spaces from a text, leaving single spaces between words; CLEAN removes characters that cannot be printed
`=UPPER(text)`	UPPER changes text to capital letters, LOWER to small letters, and PROPER capitalises the first letter of each word
`=LOWER(text)`	UPPER changes text to capital letters, LOWER to small letters, and PROPER capitalises the first letter of each word
`=PROPER(text)`	UPPER changes text to capital letters, LOWER to small letters, and PROPER capitalises the first letter of each word
`=CONCAT(text1, [text2], ...)`	CONCAT joins pieces of text into one; the & sign does the same between two values
`=CONCATENATE(text1, [text2], ...)`	CONCAT joins pieces of text into one; the & sign does the same between two values
`=text1 & text2`	CONCAT joins pieces of text into one; the & sign does the same between two values
`=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)`	TEXTJOIN joins text from several cells or ranges with a separator between the items, and can skip empty cells
`=TEXT(value, format_text)`	TEXT turns a number or date into text in the format you choose
`=SUBSTITUTE(text, old_text, new_text, [instance_num])`	SUBSTITUTE swaps one piece of text for another wherever it appears; REPLACE swaps characters at a given position
`=REPLACE(old_text, start_num, num_chars, new_text)`	SUBSTITUTE swaps one piece of text for another wherever it appears; REPLACE swaps characters at a given position
`=FIND(find_text, within_text, [start_num])`	FIND and SEARCH return the position of one piece of text inside another; FIND is case sensitive and SEARCH is not
`=SEARCH(find_text, within_text, [start_num])`	FIND and SEARCH return the position of one piece of text inside another; FIND is case sensitive and SEARCH is not
`=VALUE(text)`	VALUE turns text that looks like a number into a real number
`=NUMBERVALUE(text, [decimal_separator], [group_separator])`	VALUE turns text that looks like a number into a real number

Date and time

`=TODAY()`	TODAY returns the current date and NOW returns the current date and time, both updating whenever the sheet recalculates
`=NOW()`	TODAY returns the current date and NOW returns the current date and time, both updating whenever the sheet recalculates
`=DATE(year, month, day)`	DATE builds a date from a year, a month and a day given as three numbers
`=YEAR(serial_number)`	YEAR, MONTH and DAY return the year, the month number and the day of the month of a date
`=MONTH(serial_number)`	YEAR, MONTH and DAY return the year, the month number and the day of the month of a date
`=DAY(serial_number)`	YEAR, MONTH and DAY return the year, the month number and the day of the month of a date
`=WEEKDAY(serial_number, [return_type])`	WEEKDAY returns the day of the week of a date as a number
`=EDATE(start_date, months)`	EDATE returns the date a number of months before or after a date; EOMONTH returns the last day of the month that many months away
`=EOMONTH(start_date, months)`	EDATE returns the date a number of months before or after a date; EOMONTH returns the last day of the month that many months away
`=DATEDIF(start_date, end_date, unit)`	DATEDIF returns the number of complete years, months or days between two dates
`=NETWORKDAYS(start_date, end_date, [holidays])`	NETWORKDAYS counts the working days between two dates; WORKDAY returns the date a number of working days ahead
`=WORKDAY(start_date, days, [holidays])`	NETWORKDAYS counts the working days between two dates; WORKDAY returns the date a number of working days ahead
`=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])`	NETWORKDAYS counts the working days between two dates; WORKDAY returns the date a number of working days ahead

Dynamic arrays

`=FILTER(array, include, [if_empty])`	FILTER returns the rows of a range that meet a condition, as a list that updates by itself
`=SORT(array, [sort_index], [sort_order], [by_col])`	SORT returns a range sorted by one of its columns; SORTBY sorts a range by the values of another range
`=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)`	SORT returns a range sorted by one of its columns; SORTBY sorts a range by the values of another range
`=UNIQUE(array, [by_col], [exactly_once])`	UNIQUE returns the different values in a range, with each one listed once
`=SEQUENCE(rows, [columns], [start], [step])`	SEQUENCE returns a list of numbers in order, such as 1 to 10, in rows and columns of your choosing

Financial

=PMT(rate, nper, pv, [fv], [type]) PMT works out the fixed payment for a loan from the interest rate, the number of payments and the amount borrowed

Information

`=ISBLANK(value)`	The IS functions test what kind of thing a cell holds and return TRUE or FALSE
`=ISNUMBER(value)`	The IS functions test what kind of thing a cell holds and return TRUE or FALSE
`=ISTEXT(value)`	The IS functions test what kind of thing a cell holds and return TRUE or FALSE

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.