Excel functions, explained and practiced
167 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.
Start from the task
All functions
167 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. | |
INDIRECT function | INDIRECT turns text into a reference: "B3" becomes the cell B3, and "A2:A"&D1 becomes a range whose last row comes from a cell. | |
LOOKUP function | LOOKUP finds a value in a sorted row or column and returns the value in the same position of another row or column. | |
MATCH function | MATCH returns the position of a value in a row or column: 3 means the third cell. | |
OFFSET function | OFFSET returns a reference that starts a given number of rows and columns away from a starting cell, optionally resized to a new height and width. | |
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 |
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. |
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. |
ABS function | ABS returns a number without its sign, so -250 becomes 250 and 250 stays 250. | |
|---|---|---|
AGGREGATE function | AGGREGATE runs one of 19 calculations, from SUM and AVERAGE to LARGE and PERCENTILE, and can skip error values, hidden rows and other subtotals while it does. | |
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. | |
QUOTIENT function | QUOTIENT divides one number by another and returns only the whole-number part, dropping the remainder. | |
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. | |
SUBTOTAL function | SUBTOTAL calculates a sum, average, count or other total for a range while skipping any other SUBTOTAL formulas inside it, so section subtotals and a grand total never double count. | |
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. |
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. | |
PERCENTILE.EXC function | PERCENTILE.EXC returns the value at a given percentile using the exclusive method, which leaves out the very bottom and top of the range: k must sit strictly between 0 and 1, and close to the ends of a short list it returns #NUM!. | |
PERCENTILE.INC function | PERCENTILE.INC returns the value below which a given share of the data falls: k = 0.9 gives the 90th percentile. | |
PERCENTRANK.INC function | PERCENTRANK.INC tells you where a value stands in a list, from 0 for the lowest to 1 for the highest: 0.75 means it is above 75% of the other values. | |
QUARTILE.EXC function | QUARTILE.EXC returns the first, second or third quartile using the exclusive method, which some statistics tools and textbooks use. | |
QUARTILE.INC function | QUARTILE.INC returns a quartile of a set of numbers: 1 for the first quartile (the 25th percentile), 2 for the median, 3 for the third quartile, and 0 and 4 for the minimum and maximum. | |
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. |
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. | |
NUMBERVALUE function | NUMBERVALUE converts text that looks like a number into a real number, using the decimal and thousands separators you name rather than your computer's settings. | |
PROPER function | PROPER capitalizes the first letter of each word and lowercases the rest. | |
REGEXEXTRACT function | REGEXEXTRACT pulls out the part of a text that matches a regular expression: an order number, an email domain, the digits in a messy code. | Newer |
REGEXREPLACE function | REGEXREPLACE replaces the parts of a text that match a regular expression. | Newer |
REGEXTEST function | REGEXTEST checks whether text contains a match for a regular expression and returns TRUE or FALSE. | Newer |
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 |
TEXTSPLIT function | TEXTSPLIT splits text into separate cells at a delimiter: across columns, down rows, or both. | 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 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. | |
HOUR function | HOUR returns the hour of a time as a number from 0 to 23, so 1:45 PM gives 13. | |
ISOWEEKNUM function | ISOWEEKNUM returns the ISO 8601 week number of a date: weeks start on Monday, and week 1 is the week that contains the year's first Thursday. | |
MINUTE function | MINUTE returns the minutes part of a time as a number from 0 to 59, so 1:45 PM gives 45. | |
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. | |
NETWORKDAYS.INTL function | NETWORKDAYS.INTL counts the working days between two dates, like NETWORKDAYS, but lets you say which days are the weekend: Friday and Saturday, Sunday only, or any pattern written as a seven-character string such as "0000011". | |
NOW function | NOW returns the current date and time, and updates whenever the workbook recalculates. | |
SECOND function | SECOND returns the seconds part of a time as a number from 0 to 59. | |
TIME function | TIME builds a time from hours, minutes and seconds, such as TIME(13,30,0) for 1:30 PM. | |
TIMEVALUE function | TIMEVALUE converts a time written as text, such as "1:30 PM" or "13:30", into a real time you can add, subtract and format. | |
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. | |
WEEKNUM function | WEEKNUM returns the week number of a date in its year, 1 to 54. | |
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. | |
WORKDAY.INTL function | WORKDAY.INTL returns the date a number of working days before or after a start date, skipping the weekend days you choose and any holidays. | |
YEAR function | YEAR returns the four-digit year from a date. | |
YEARFRAC function | YEARFRAC returns the fraction of a year between two dates, such as 0.5 for six months. |
BYCOL function | BYCOL runs a LAMBDA on each column of a range and returns one result per column, in a single formula that spills across. | Newer |
|---|---|---|
BYROW function | BYROW runs a LAMBDA on each row of a range and returns one result per row, all from one spilling formula. | Newer |
CHOOSECOLS function | CHOOSECOLS returns only the columns you name from a range or array, in the order you name them, so 3,1 puts the third column first. | Newer |
CHOOSEROWS function | CHOOSEROWS returns the rows you name from a range or array: 1 is the first row and -1 the last, so CHOOSEROWS(range,-1) always gives the latest entry. | Newer |
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 |
LAMBDA function | LAMBDA creates your own function from a formula: name the inputs, then write the calculation, as in LAMBDA(price, rate, price*(1+rate)). | Newer |
MAKEARRAY function | MAKEARRAY builds an array of a given number of rows and columns by calling a LAMBDA with each row and column number. | Newer |
MAP function | MAP runs a LAMBDA on each value of an array, or on matching values from several arrays, and returns an array of the same size. | Newer |
REDUCE function | REDUCE walks through every value in an array, passing a running result and the next value to a LAMBDA, and returns the final running result. | Newer |
SCAN function | SCAN works like REDUCE, passing a running result and each value to a LAMBDA, but it returns the running result at every step instead of just the last one. | 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 |
TOCOL function | TOCOL turns a range or array into a single column, reading across each row unless you ask for column by column. | Newer |
TOROW function | TOROW turns a range or array into a single row, reading across each row unless you ask for column by column. | 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 |
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. |
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.