Excel functions

Adds numbers and ranges.

Example: =SUM(B2:B10)

Multiplies numbers.

Example: =PRODUCT(B2,C2)

Rounds to the nearest value. Negative digits round to tens, hundreds.

Example: =ROUND(1234.567,2)

Always rounds up (away from zero).

Example: =ROUNDUP(B2,-2)

Always rounds down (toward zero).

Example: =ROUNDDOWN(B2,0)

Rounds to the nearest multiple: $50, 5 minutes.

Example: =MROUND(B2,50)

Rounds up to a multiple.

Example: =CEILING.MATH(17,5)

Rounds down to a multiple.

Example: =FLOOR.MATH(17,5)

Rounds up to a multiple (legacy version).

Example: =CEILING(17,5)

Rounds down to a multiple (legacy version).

Example: =FLOOR(17,5)

Integer part, rounded down: −4.5 → −5.

Example: =INT(B2/12)

Cuts off the decimal part without rounding.

Example: =TRUNC(-4.7)

Integer part of a division.

Example: =QUOTIENT(50,12)

Remainder after division.

Example: =MOD(50,12)

Absolute value of a number.

Example: =ABS(B2-C2)

Raises a number to a power, same as ^.

Example: =POWER(1.1,12)

Square root.

Example: =SQRT(B2)

Sum with one condition.

Example: =SUMIF(A2:A20,"Boston",C2:C20)

Sum with several conditions.

Example: =SUMIFS(C:C,A:A,"Boston",B:B,">100")

Sum of pairwise products.

Example: =SUMPRODUCT(B2:B6,C2:C6)

Sum of squares.

Example: =SUMSQ(A2:A6)

Total over visible rows only (respects filters). 9 is sum, 1 is average.

Example: =SUBTOTAL(9,C2:C100)

Total that can skip hidden rows and/or errors.

Example: =AGGREGATE(9,6,C2:C100)

Random integer. Changes on every recalculation.

Example: =RANDBETWEEN(1,100)

Random number between 0 and 1.

Example: =RAND()

TRUE if the number is even.

Example: =ISEVEN(A2)

TRUE if the number is odd.

Example: =ISODD(A2)

Arithmetic mean (blank cells and text are skipped).

Example: =AVERAGE(B2:B10)

Average with one condition.

Example: =AVERAGEIF(A:A,"Denver",C:C)

Average with several conditions.

Example: =AVERAGEIFS(C:C,A:A,"Denver",B:B,">0")

Smallest value.

Example: =MIN(B2:B10)

Largest value.

Example: =MAX(B2:B10)

Minimum with conditions.

Example: =MINIFS(C:C,A:A,"Tim")

Maximum with conditions.

Example: =MAXIFS(C:C,A:A,"Tim")

Counts cells containing numbers.

Example: =COUNT(B2:B10)

Counts non-empty cells.

Example: =COUNTA(A2:A10)

Counts empty cells.

Example: =COUNTBLANK(B2:B10)

Counts cells that meet a condition.

Example: =COUNTIF(B2:B10,">100")

Counts with several conditions.

Example: =COUNTIFS(A:A,"Boston",C:C,">0")

Median: the middle of a sorted list.

Example: =MEDIAN(B2:B10)

Most frequent value (see MODE.SNGL).

Example: =MODE(B2:B10)

k-th largest value.

Example: =LARGE(B2:B10,2)

k-th smallest value.

Example: =SMALL(B2:B10,1)

A number's position in a list (see RANK.EQ).

Example: =RANK(B2,$B$2:$B$10)

Standard deviation of a sample.

Example: =STDEV.S(B2:B10)

Standard deviation of an entire population.

Example: =STDEV.P(B2:B10)

Variance of a sample.

Example: =VAR.S(B2:B10)

k-th percentile, k from 0 to 1.

Example: =PERCENTILE.INC(B2:B100,0.9)

Quartile: 1 is 25%, 2 is the median, 3 is 75%.

Example: =QUARTILE.INC(B2:B100,1)

Percentile rank of a value.

Example: =PERCENTRANK.INC(B2:B100,B2)

Percentile excluding the endpoints: k strictly between 1/(n+1) and n/(n+1).

Example: =PERCENTILE.EXC(B2:B100,0.9)

Quartile excluding the endpoints (1, 2 or 3).

Example: =QUARTILE.EXC(B2:B100,1)

Z-score: how many standard deviations a value is from the mean.

Example: =STANDARDIZE(B2,$B$20,$B$21)

Standard normal distribution.

Example: =NORM.S.DIST(1.96,TRUE)

Inverse of the standard normal distribution: z from a probability.

Example: =NORM.S.INV(0.975)

Normal distribution with a given mean and standard deviation.

Example: =NORM.DIST(B2,100,15,TRUE)

Normal distribution value for a given probability.

Example: =NORM.INV(0.95,100,15)

Student's t-distribution (left tail).

Example: =T.DIST(-2,10,TRUE)

Two-tailed probability for a t statistic (x ≥ 0).

Example: =T.DIST.2T(2.1,10)

Right tail of Student's t-distribution.

Example: =T.DIST.RT(2.1,10)

t value for a left-tail probability.

Example: =T.INV(0.95,10)

Critical t value for a two-tailed test.

Example: =T.INV.2T(0.05,10)

p-value of a t-test: type 1 is paired, 2 equal variances, 3 unequal variances (Welch).

Example: =T.TEST(B2:B21,C2:C21,2,3)

Half-width of a confidence interval (normal distribution).

Example: =CONFIDENCE.NORM(0.05,B22,COUNT(B2:B21))

Half-width of a confidence interval (Student's t).

Example: =CONFIDENCE.T(0.05,B22,COUNT(B2:B21))

Right tail of the F-distribution: the p-value in ANOVA.

Example: =F.DIST.RT(E14,2,12)

Critical F value.

Example: =F.INV.RT(0.05,2,12)

Correlation coefficient from −1 to 1.

Example: =CORREL(B2:B13,C2:C13)

Slope of the trend line.

Example: =SLOPE(C2:C13,B2:B13)

Point where the trend line crosses the Y axis.

Example: =INTERCEPT(C2:C13,B2:B13)

Forecast along a linear trend.

Example: =FORECAST.LINEAR(13,C2:C13,A2:A13)

Linear trend values for new x values.

Example: =TREND(C2:C13,A2:A13,A14:A16)

Chooses between two outcomes based on a condition.

Example: =IF(B2>=60,"Pass","Fail")

Returns the result for the first condition that is true.

Example: =IFS(B2>=90,5,B2>=75,4,TRUE,3)

Picks a result by exact match.

Example: =SWITCH(A2,"Boston","East","Denver","Central","Other")

TRUE if all conditions are met.

Example: =AND(B2>0,C2>0)

TRUE if at least one condition is met.

Example: =OR(B2="Yes",C2="Yes")

Logical negation.

Example: =NOT(ISBLANK(A2))

TRUE if an odd number of conditions are met.

Example: =XOR(B2>0,C2>0)

Replaces any error.

Example: =IFERROR(B2/C2,0)

Replaces only the #N/A error.

Example: =IFNA(VLOOKUP(A2,PriceList,2,0),"none")

Returns a value by its number.

Example: =CHOOSE(2,"Q1","Q2","Q3","Q4")

Joins texts and ranges.

Example: =CONCAT(A2," ",B2)

Joins texts (legacy function).

Example: =CONCATENATE(A2," ",B2)

Joins a list with a delimiter.

Example: =TEXTJOIN(", ",TRUE,A2:A10)

Length of a text.

Example: =LEN(A2)

First n characters.

Example: =LEFT(A2,2)

Last n characters.

Example: =RIGHT(A2,5)

n characters starting at a position.

Example: =MID(A2,4,3)

Position of a text, case-sensitive.

Example: =FIND("@",A2)

Position of a text, not case-sensitive; understands * and ?.

Example: =SEARCH("coffee",A2)

Replaces text by its content.

Example: =SUBSTITUTE(A2,"-","")

Replaces text by position.

Example: =REPLACE(A2,1,1,"+1")

Character for a code number: 10 is a line break, 160 is a non-breaking space.

Example: =SUBSTITUTE(A2,CHAR(160)," ")

Code of the first character.

Example: =CODE(A2)

Unicode character for a code.

Example: =UNICHAR(8364)

Unicode code of the first character.

Example: =UNICODE(A2)

ALL CAPITALS.

Example: =UPPER(A2)

all lowercase.

Example: =LOWER(A2)

Each Word Capitalized.

Example: =PROPER(A2)

Removes extra spaces.

Example: =TRIM(A2)

Removes non-printing characters.

Example: =CLEAN(A2)

Repeats a text.

Example: =REPT("★",B2)

Case-sensitive comparison.

Example: =EXACT(A2,B2)

Number or date as text in a given format.

Example: =TEXT(B2,"$#,##0")

Converts text that looks like a number into a number.

Example: =VALUE("1250")

The part of a text before a delimiter.

Example: =TEXTBEFORE(A2,"@")

Number from text with the given separators, e.g. "1,250.75" from a CSV.

Example: =NUMBERVALUE("1,250.75",".",",")

The part of a text after a delimiter.

Example: =TEXTAFTER(A2,"@")

Splits a text across cells.

Example: =TEXTSPLIT(A2,", ")

Builds a date from numbers.

Example: =DATE(2026,3,15)

Year of a date.

Example: =YEAR(A2)

Month of a date (1–12).

Example: =MONTH(A2)

Day of the month.

Example: =DAY(A2)

Today's date. Changes every day.

Example: =TODAY()

Current date and time.

Example: =NOW()

Converts a date written as text into a date.

Example: =DATEVALUE("3/15/2026")

Difference between dates in "y", "m" or "d" (undocumented, but works).

Example: =DATEDIF(B2,TODAY(),"y")

Number of days between two dates.

Example: =DAYS(C2,B2)

Day of the week as a number. Type 2: Mon = 1.

Example: =WEEKDAY(A2,2)

Date n months away.

Example: =EDATE(A2,3)

Last day of the month.

Example: =EOMONTH(A2,0)

Working days in a period.

Example: =NETWORKDAYS(A2,B2,Holidays)

Date n working days away.

Example: =WORKDAY(A2,10,Holidays)

Builds a time.

Example: =TIME(9,30,0)

Hours.

Example: =HOUR(A2)

Minutes.

Example: =MINUTE(A2)

Looks in the first column and returns a value from a column to the right.

Example: =VLOOKUP(A2,PriceList!$A$2:$C$100,3,0)

Same, but searches the first row.

Example: =HLOOKUP("March",A1:M5,3,0)

Modern lookup in any direction.

Example: =XLOOKUP(A2,B:B,C:C,"none")

Position within a range. 0 means exact match.

Example: =MATCH(A2,B2:B100,0)

Position, with modern lookup modes.

Example: =XMATCH(A2,B2:B100)

Value at the intersection of a row and a column.

Example: =INDEX(C2:C100,MATCH(A2,B2:B100,0))

A shifted range. Volatile function.

Example: =SUM(OFFSET(B1,COUNT(B:B)-2,0,3,1))

Reference built from text. Volatile function.

Example: =INDIRECT("B"&A1)

Row number.

Example: =ROW()-1

Column number.

Example: =COLUMN(C1)

Number of rows.

Example: =ROWS(A2:A100)

Number of columns.

Example: =COLUMNS(A1:F1)

Flips a table: rows become columns.

Example: =TRANSPOSE(A1:D4)

List without duplicates.

Example: =UNIQUE(B2:B100)

Rows that meet a condition.

Example: =FILTER(A2:C100,B2:B100="Kitchen")

Sorts an array.

Example: =SORT(A2:C100,3,-1)

Sorts by other columns.

Example: =SORTBY(A2:A100,C2:C100,-1)

A sequence of numbers.

Example: =SEQUENCE(10)

Names for intermediate results inside a formula.

Example: =LET(x,A2*2,x+1)

First or last rows/columns.

Example: =TAKE(SORT(A2:C100,3,-1),5)

Array without its first/last rows or columns.

Example: =DROP(A1:C100,1)

Selected columns.

Example: =CHOOSECOLS(A2:E100,1,3)

Selected rows.

Example: =CHOOSEROWS(A2:E100,1,-1)

Arrays stacked on top of each other.

Example: =VSTACK(January!A2:C50,February!A2:C50)

Arrays placed side by side.

Example: =HSTACK(A2:A10,C2:C10)

Array into a single column.

Example: =TOCOL(A2:D10,1)

Array into a single row.

Example: =TOROW(A2:A10)

Your own function inside a formula.

Example: =MAP(A2:A6,LAMBDA(x,x*2))

Applies a LAMBDA to every element.

Example: =MAP(A2:A6,LAMBDA(x,ROUND(x,0)))

Folds an array into a single value.

Example: =REDUCE(0,A2:A6,LAMBDA(a,x,a+x))

All the intermediate totals.

Example: =SCAN(0,A2:A6,LAMBDA(a,x,a+x))

One value per row.

Example: =BYROW(B2:D10,LAMBDA(r,SUM(r)))

One value per column.

Example: =BYCOL(B2:D10,LAMBDA(c,MAX(c)))

Groups and aggregates with one formula, like a PivotTable. Pass the function without parentheses: SUM.

Example: =GROUPBY(A2:A20,C2:C20,SUM)

Cross-tab in one formula: rows × columns. In the trainer, one row field and one column field.

Example: =PIVOTBY(A2:A20,B2:B20,C2:C20,SUM)

TRUE if the text matches a regular expression. Case: 0 is case-sensitive, 1 is not.

Example: =REGEXTEST(A2,"^\d{12}$")

Extracts a match: mode 0 is the first, 1 all (array), 2 capture groups.

Example: =REGEXEXTRACT(A2,"\d+")

Replaces matches; $1 is the first group in parentheses.

Example: =REGEXREPLACE(A2,"\s+"," ")

Fixed loan payment (annuity).

Example: =PMT(24%/12,24,-2000000)

Present value.

Example: =PV(14%/12,24,0,-1000000)

Future value of a deposit.

Example: =FV(14%/12,12,-50000,-200000)

Interest rate per period.

Example: =RATE(12,-55000,600000)*12

Number of payment periods.

Example: =NPER(18%/12,-60000,1000000)

Net present value of cash flows starting at period 1.

Example: =NPV(15%,B4:B8)+B3

Internal rate of return.

Example: =IRR(B3:B8)

NPV for cash flows on arbitrary dates.

Example: =XNPV(15%,B2:B6,A2:A6)

Rate of return for cash flows on arbitrary dates.

Example: =XIRR(B2:B6,A2:A6)

TRUE if the cell is empty.

Example: =ISBLANK(A2)

TRUE if the value is a number.

Example: =ISNUMBER(A2)

TRUE if the value is text.

Example: =ISTEXT(A2)

TRUE for any error.

Example: =ISERROR(B2/C2)

TRUE if the value is #N/A.

Example: =ISNA(VLOOKUP(A2,B:C,2,0))

Returns the #N/A error (marks a missing value).

Example: =IF(B2="",NA(),B2)