Excel functions, explained and practiced

129 functions, each with plain-English syntax, worked examples you can edit, the mistakes people actually make, and a graded exercise. Search by name, or describe what you're trying to do.

XLOOKUP VLOOKUP SUMIFS COUNTIFS IF IFERROR INDEX MATCH FILTER UNIQUE TEXTJOIN ROUND

Start from the task

Lookup functions

Return a value from a table by ID or name XLOOKUP
Do the same in Excel 2019 or earlier INDEX
Find the position of a value in a list MATCH
Find the last match instead of the first XMATCH

Logical functions

Financial functions

Information functions

Flag rows with a missing value ISBLANK
Find numbers stored as text ISNUMBER
Check whether a cell contains some text ISNUMBER
Tell "not found" apart from a real error ISNA

All functions

Search functions or describe a task

Only functions added in Excel 2019 or later

129 functions.

Lookup functions

`COLUMN` function	COLUMN returns the column number of a cell: COLUMN(D1) is 4, and COLUMN() with nothing inside returns the column of the formula's own cell.
`COLUMNS` function	COLUMNS counts how many columns a range or array has, whether or not the cells contain anything.
`HLOOKUP` function	HLOOKUP is VLOOKUP turned sideways: it searches the top row of a table for a value and returns a value from a row below it, in the same column.
`INDEX` function	INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.
`MATCH` function	MATCH returns the position of a value in a row or column: 3 means the third cell.
`ROW` function	ROW returns the row number of a cell: ROW(C7) is 7, and ROW() with nothing inside returns the row of the cell the formula sits in.
`ROWS` function	ROWS counts how many rows a range or array has, whether or not the cells contain anything.
`VLOOKUP` function	VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row.
`XLOOKUP` function	XLOOKUP searches one column or row for a value and returns the matching item from another.	Newer
`XMATCH` function	XMATCH is the modern MATCH: it returns the position of a value in a list, but it defaults to an exact match, can search from the bottom up, and handles wildcards and next-larger or next-smaller matches without sorted data.	Newer

Logical functions

`AND` function	AND returns TRUE only when every condition you give it is TRUE.
`CHOOSE` function	CHOOSE returns the item at a given position from a list you type into the formula: 1 returns the first item, 2 the second, and so on.
`IF` function	IF checks a condition and returns one value when it is true and another when it is false.
`IFERROR` function	IFERROR returns a value you choose when a formula results in an error, and the formula's normal result otherwise.
`IFNA` function	IFNA returns a fallback only when a formula results in #N/A, the error lookups give when a value is missing.
`IFS` function	IFS checks a list of conditions in order and returns the value paired with the first one that is TRUE.	Newer
`LET` function	LET gives names to values or calculations inside a single formula, then uses those names in a final calculation.	Newer
`NOT` function	NOT flips a logical value: TRUE becomes FALSE and FALSE becomes TRUE.
`OR` function	OR returns TRUE when at least one of its conditions is TRUE.
`SWITCH` function	SWITCH compares one value against a list of exact matches and returns the result paired with the first match, with an optional default at the end.	Newer
`XOR` function	XOR returns TRUE when an odd number of its conditions are TRUE.

Conditional sum and count functions

`AVERAGEIF` function	AVERAGEIF averages only the values that meet one condition: the average order from one region, response times for chat tickets, scores above zero.
`AVERAGEIFS` function	AVERAGEIFS averages the values where every condition is true, such as Algebra scores from the fall term or passing scores only.
`COUNTIF` function	COUNTIF counts the cells in a range that meet one condition: tickets marked "Open", scores of 70 or more, product names containing "lamp".
`COUNTIFS` function	COUNTIFS counts the rows where every condition is true: orders that are from the East region and shipped, deals between $500 and $1,000.
`MAXIFS` function	MAXIFS returns the largest value among the rows that meet one or more conditions: the biggest deal in a region, the latest order date for a customer.	Newer
`MINIFS` function	MINIFS returns the smallest value among the rows that meet one or more conditions: the cheapest quote for a part, the earliest due date in a project, the lowest score above zero.	Newer
`SUMIF` function	SUMIF adds up values for the rows that meet one condition: total travel expenses, revenue for one product, every invoice over $500.
`SUMIFS` function	SUMIFS adds the values where every condition is true: East region widget sales, February revenue, large orders from one customer.

Math functions

`ABS` function	ABS returns a number without its sign, so -250 becomes 250 and 250 stays 250.
`CEILING` function	CEILING rounds a number up to the next multiple you choose: the next 5, the next dozen, the next quarter hour.
`EXP` function	EXP returns e (about 2.71828) raised to a power.
`FLOOR` function	FLOOR rounds a number down to the previous multiple you choose: down to the nearest 10, the nearest quarter, or the start of a price band.
`INT` function	INT rounds a number down to the nearest whole number.
`LN` function	LN returns the natural logarithm of a number: the power you would raise e to in order to get it.
`LOG` function	LOG returns the logarithm of a number in the base you choose: the power the base must be raised to in order to reach the number.
`LOG10` function	LOG10 returns the base-10 logarithm of a number: how many times 10 is multiplied to reach it.
`MOD` function	MOD returns the remainder after dividing one number by another.
`MROUND` function	MROUND rounds a number to the nearest multiple of another number.
`PI` function	PI returns the constant π, accurate to 15 digits (3.14159265358979).
`POWER` function	POWER raises a number to a power, the same as the ^ operator.
`PRODUCT` function	PRODUCT multiplies all of its arguments together.
`ROUND` function	ROUND rounds a number to the number of digits you choose.
`ROUNDDOWN` function	ROUNDDOWN always rounds toward zero, dropping the extra digits whatever they are.
`ROUNDUP` function	ROUNDUP always rounds away from zero, whatever the next digit is.
`SIGN` function	SIGN tells you which side of zero a number is on: 1 for positive, -1 for negative and 0 for zero.
`SQRT` function	SQRT returns the square root of a number: the value that, multiplied by itself, gives the number.
`SUM` function	SUM adds numbers.
`SUMPRODUCT` function	SUMPRODUCT multiplies matching items in two or more ranges and adds the results.
`SUMSQ` function	SUMSQ squares each number and adds the squares.
`TRUNC` function	TRUNC cuts a number off at a set number of decimals without rounding.

Statistical functions

`AVERAGE` function	AVERAGE adds up the numbers you give it and divides by how many numbers there are.
`COUNT` function	COUNT tells you how many cells contain numbers.
`COUNTA` function	COUNTA counts every cell that isn't empty: text, numbers, dates, errors and even formulas that return empty text.
`COUNTBLANK` function	COUNTBLANK counts the empty cells in a range: unassigned tasks, unanswered questions, missing dates.
`LARGE` function	LARGE returns the k-th largest value in a range: k = 1 is the largest, 2 the second largest, and so on.
`MAX` function	MAX returns the largest number from a list of values or ranges: the top sale, the latest date, the highest temperature.
`MEDIAN` function	MEDIAN returns the middle value once the numbers are sorted.
`MIN` function	MIN returns the smallest number from a list of values or ranges.
`MODE` function	MODE returns the number that appears most often in a list, such as the most common shoe size or order quantity.
`RANK.EQ` function	RANK.EQ tells you where a number places within a list: 1 for the largest by default, or 1 for the smallest if you set the order to 1.
`SMALL` function	SMALL returns the k-th smallest value in a range: k = 1 is the smallest, 2 the second smallest, and so on.
`STDEV` function	STDEV measures how spread out a set of numbers is, treating them as a sample of a larger group.
`STDEV.P` function	STDEV.P measures how spread out a set of numbers is when those numbers are the entire population: every employee, every shift, every student in the class.
`VAR` function	VAR returns the variance of a sample: the average squared distance of each value from the mean.

Text functions

`CHAR` function	CHAR returns the character for a code number.
`CLEAN` function	CLEAN removes non-printing characters, such as line breaks and tabs, from text.
`CODE` function	CODE returns the numeric code of the first character in a text string.
`CONCAT` function	CONCAT joins text from several cells or whole ranges into one string.	Newer
`CONCATENATE` function	CONCATENATE joins up to 255 text items into one string.
`EXACT` function	EXACT compares two text values and returns TRUE only if they match exactly, including upper and lower case.
`FIND` function	FIND returns the position of one piece of text inside another, counting from 1.
`LEFT` function	LEFT returns a set number of characters from the start of a text string.
`LEN` function	LEN counts the characters in a text string, including spaces and punctuation.
`LOWER` function	LOWER converts every letter in a text string to lowercase and leaves numbers and symbols alone.
`MID` function	MID returns characters from the middle of a text string, starting at a position you choose.
`PROPER` function	PROPER capitalizes the first letter of each word and lowercases the rest.
`REPLACE` function	REPLACE swaps out a set number of characters at a given position, whatever those characters are.
`REPT` function	REPT repeats a piece of text a given number of times.
`RIGHT` function	RIGHT returns a set number of characters from the end of a text string.
`SEARCH` function	SEARCH returns the position of one piece of text inside another, ignoring upper and lower case, and it understands the ? and * wildcards.
`SUBSTITUTE` function	SUBSTITUTE replaces specific text with new text everywhere it appears, or only at the occurrence you choose.
`TEXT` function	TEXT turns a number or date into text in the format you choose, such as "$1,250.00" or "March 2026".
`TEXTAFTER` function	TEXTAFTER returns everything after a delimiter, such as the domain after the @ or the last part of a file path.	Newer
`TEXTBEFORE` function	TEXTBEFORE returns everything before a delimiter you name, such as the username before the @ in an email or the first name before a space.	Newer
`TEXTJOIN` function	TEXTJOIN joins a list of values with the same delimiter between each one, and can skip blanks.	Newer
`TRIM` function	TRIM removes spaces from the start and end of text and shrinks runs of spaces between words to one.
`UPPER` function	UPPER converts every letter in a text string to capitals.
`VALUE` function	VALUE converts text that looks like a number into a real number Excel can add, average and compare.

Date functions

`DATE` function	DATE builds a real date from a year, month and day.
`DATEDIF` function	DATEDIF counts the complete years, months or days between two dates.
`DATEVALUE` function	DATEVALUE converts a date that is stored as text into a real date that Excel can sort, subtract and format.
`DAY` function	DAY returns the day of the month from a date, as a number from 1 to 31.
`DAYS` function	DAYS returns the number of days between two dates.
`EDATE` function	EDATE returns the date a given number of months before or after a start date, keeping the same day of the month.
`EOMONTH` function	EOMONTH returns the last day of the month a number of months before or after a date.
`MONTH` function	MONTH returns the month of a date as a number from 1 to 12.
`NETWORKDAYS` function	NETWORKDAYS counts the working days between two dates, Monday to Friday, including both the start and end date.
`TODAY` function	TODAY returns the current date, with no time attached, and updates every time the workbook recalculates.
`WEEKDAY` function	WEEKDAY returns the day of the week for a date as a number.
`WORKDAY` function	WORKDAY returns the date a given number of working days before or after a start date, skipping weekends and any holidays you list.
`YEAR` function	YEAR returns the four-digit year from a date.

Dynamic array functions

`DROP` function	DROP removes rows or columns from the start or end of a range and returns what is left.	Newer
`FILTER` function	FILTER returns only the rows of a range that pass a test, such as every order from the West region.	Newer
`HSTACK` function	HSTACK places ranges side by side to build one table, so you can pick columns that aren't next to each other, put them in a new order, or add a calculated column, all in one formula that updates with the source.	Newer
`SEQUENCE` function	SEQUENCE generates a list or grid of numbers from one formula: 1 to 10, every fifth number, or a countdown.	Newer
`SORT` function	SORT returns a sorted copy of a range, ordered by any column you choose, smallest to largest or the reverse.	Newer
`SORTBY` function	SORTBY sorts one range by the values in another.	Newer
`TAKE` function	TAKE keeps the first or last rows (and columns) of a range and drops the rest.	Newer
`TRANSPOSE` function	TRANSPOSE flips a range so rows become columns and columns become rows.
`UNIQUE` function	UNIQUE returns each distinct value in a range once, in the order it first appears.	Newer
`VSTACK` function	VSTACK stacks ranges on top of each other into one list, such as this month's orders under last month's or one region's reps under another's.	Newer

Financial functions

`FV` function	FV calculates what an investment will be worth in the future, given regular deposits, a starting balance and a constant rate of return.
`IRR` function	IRR calculates the internal rate of return: the discount rate at which a series of cash flows has a net present value of zero.
`NPER` function	NPER calculates how many payment periods it takes to pay off a loan or reach a savings goal, at a fixed payment and interest rate.
`NPV` function	NPV discounts a series of future cash flows back to today at a given rate, so you can judge whether an investment beats your cost of capital.
`PMT` function	PMT calculates the fixed payment for a loan or a savings plan, based on a constant interest rate and number of payments.
`PV` function	PV calculates what a series of future payments, or a single future amount, is worth today at a given interest rate.
`RATE` function	RATE works out the interest rate per period from the number of payments, the payment and the amount borrowed or invested.

Information functions

`ISBLANK` function	ISBLANK returns TRUE when a cell is completely empty.
`ISERR` function	ISERR returns TRUE for any error except #N/A.
`ISERROR` function	ISERROR returns TRUE for any error value, such as #N/A, #VALUE!, #REF!, #DIV/0! or #NAME?.
`ISEVEN` function	ISEVEN returns TRUE when a number is even.
`ISLOGICAL` function	ISLOGICAL returns TRUE when a value is one of the logical values, TRUE or FALSE.
`ISNA` function	ISNA returns TRUE only for the #N/A error, the one MATCH, VLOOKUP and XLOOKUP return when a value isn't found.
`ISNUMBER` function	ISNUMBER returns TRUE when a value is a real number, which includes dates and times.
`ISODD` function	ISODD returns TRUE when a number is odd.
`ISTEXT` function	ISTEXT returns TRUE when a value is text, including numbers that were stored as text and the empty text "" that some formulas return.
`NA` function	NA() returns the #N/A error on purpose, to mark a value as not available.

Which one should I use?

Functions that look alike but behave differently, run side by side on the same data.

VLOOKUP vs XLOOKUP XLOOKUP vs INDEX and MATCH COUNTIF vs COUNTIFS SUMIF vs SUMIFS FIND vs SEARCH IFERROR vs IFNA CONCAT vs TEXTJOIN vs &ROUND vs ROUNDUP vs ROUNDDOWN