Excel Function Reference

Excel functions with what they do, their syntax and a worked example. Every example runs in the browser engine used by the Excel course; press Run to see the sheet.

80 of 80

Function	Group	Description
SUM	Math	Adds the numbers in the cells or ranges.
AVERAGE	Statistics	The mean of the numbers.
MIN	Statistics	The smallest number.
MAX	Statistics	The largest number.
MEDIAN	Statistics	The middle value when the numbers are sorted.
MODE	Statistics	The value that appears most often.
STDEV.S	Statistics	Standard deviation of a sample (divides by n - 1).
STDEV.P	Statistics	Standard deviation of a whole population (divides by n).
LARGE	Statistics	The k-th largest number: 1 is the biggest, 2 the next.
SMALL	Statistics	The k-th smallest number.
RANK	Statistics	The position of a number in a list, largest first by default.
COUNT	Statistics	Counts the cells that hold numbers.
COUNTA	Statistics	Counts the cells that are not empty.
COUNTBLANK	Statistics	Counts the empty cells in a range.
SUMPRODUCT	Math	Multiplies matching items of the ranges and adds the results.
ROUND	Math	Rounds a number to a number of decimals.
ROUNDUP	Math	Rounds a number up, away from zero.
ROUNDDOWN	Math	Rounds a number down, towards zero.
ABS	Math	The size of a number without its sign.
INT	Math	Rounds down to the nearest whole number.
SQRT	Math	The square root of a number.
POWER	Math	Raises a number to a power.
MOD	Math	The remainder after dividing.
PI	Math	The number pi, 3.14159...
RAND	Math	A random decimal between 0 and 1. It changes every time the sheet recalculates.
RANDBETWEEN	Math	A random whole number between two limits, inclusive.
IF	Logical	Returns one value when a test is true and another when it is false.
IFS	Logical	Tests several conditions in order and returns the value for the first one that is true.
IFERROR	Logical	Returns a fallback value when a formula gives an error.
AND	Logical	True only when every condition is true.
OR	Logical	True when at least one condition is true.
NOT	Logical	Reverses true and false.
XOR	Logical	True when an odd number of conditions are true. With two conditions: exactly one.
ISNUMBER	Logical	True when the value is a number.
ISTEXT	Logical	True when the value is text.
ISBLANK	Logical	True when the cell is empty.
ISERROR	Logical	True when the value is an error such as #DIV/0!.
SUMIF	Conditional	Adds the cells that meet one condition.
SUMIFS	Conditional	Adds the cells that meet several conditions.
COUNTIF	Conditional	Counts the cells that meet one condition.
COUNTIFS	Conditional	Counts the cells that meet several conditions.
AVERAGEIF	Conditional	The average of the cells that meet one condition.
AVERAGEIFS	Conditional	The average of the cells that meet several conditions.
MAXIFS	Conditional	The largest value among the cells that meet the conditions.
MINIFS	Conditional	The smallest value among the cells that meet the conditions.
VLOOKUP	Lookup	Finds a value in the first column of a table and returns a value from the same row.
HLOOKUP	Lookup	Finds a value in the top row of a table and returns a value from the same column.
XLOOKUP	Lookup	Finds a value in one range and returns the matching value from another. It can look to the left and can give a fallback.
MATCH	Lookup	The position of a value in a range.
INDEX	Lookup	The value at a position in a range.
INDEX MATCH	Lookup	INDEX and MATCH together: find the position of a value, then return a cell from another column. It works in any direction.
SORT	Dynamic arrays	Returns a table sorted by a column (1 for smallest first, -1 for largest first). The result fills the empty cells below and beside the formula.
FILTER	Dynamic arrays	Returns only the rows that meet a condition.
UNIQUE	Dynamic arrays	Returns each different value once.
LEFT	Text	The first characters of a text.
RIGHT	Text	The last characters of a text.
MID	Text	Characters from the middle of a text.
LEN	Text	The number of characters in a text.
UPPER	Text	Converts text to capital letters.
LOWER	Text	Converts text to small letters.
PROPER	Text	Capitalises the first letter of each word.
TRIM	Text	Removes extra spaces from the ends and between words.
CONCAT	Text	Joins texts and ranges into one.
TEXTJOIN	Text	Joins a range of texts with a separator, optionally skipping empty cells.
SUBSTITUTE	Text	Replaces text inside a text.
FIND	Text	The position of one text inside another (case sensitive).
SEARCH	Text	The position of one text inside another (ignores capitals).
VALUE	Text	Converts text that looks like a number into a number.
REPT	Text	Repeats a text a number of times. Handy for text bars.
TEXT	Text	Turns a number or date into text with a format.
DATE	Date	Builds a date from a year, month and day.
YEAR	Date	The year of a date.
MONTH	Date	The month of a date, 1 to 12.
DAY	Date	The day of the month of a date.
TODAY	Date	Today's date. It changes every day.
DATEDIF	Date	Complete years ("Y"), months ("M") or days ("D") between two dates.
NETWORKDAYS	Date	The number of working days (Monday to Friday) between two dates, both included.
PMT	Financial	The regular payment on a loan with a fixed rate. The result is negative because it is money paid out.
FV	Financial	The future value of regular payments at a fixed rate.
NPV	Financial	The present value of future cash flows at a discount rate. Add an initial investment (paid now) separately.