Excel functions list, with a worked example for every one

SUM(number1, [number2], …)

Adds up numbers and ranges.

A blank cell inside the range is skipped, not read as a zero.

=SUM(C2:C7)→returns57

AVERAGE(number1, [number2], …)

The mean of the numbers, ignoring text and blanks.

Divides by five, not six: the blank in C7 is not an entry. A zero would have been.

=AVERAGE(C2:C7)→returns11.4

COUNT(value1, [value2], …)

How many cells hold a number.

The heading in C1 is text, so COUNT ignores it. Useful as a free check on a column you do not trust.

=COUNT(C1:C7)→returns5

COUNTA(value1, [value2], …)

How many cells are not empty.

Counts anything that is not empty, heading included. The gap between this and COUNT is how many cells hold something that is not a number.

=COUNTA(C1:C7)→returns6

COUNTBLANK(range)

How many cells in a range are empty.

=COUNTBLANK(C2:C7)→returns1

MIN(number1, [number2], …)

The smallest number.

=MIN(C2:C7)→returns4

MAX(number1, [number2], …)

The largest number.

=MAX(C2:C7)→returns19

MEDIAN(number1, [number2], …)

The middle value once the numbers are in order.

The middle value rather than the mean, which is what to reach for when one enormous row is dragging an average around.

=MEDIAN(C2:C7)→returns12

MODE.SNGL(number1, [number2], …)

The most frequent number.

=MODE.SNGL(4, 7, 7, 12)→returns7

STDEV.S(number1, [number2], …)

Spread of a sample.

For a sample, which is nearly always what a spreadsheet column is.

=ROUND(STDEV.S(C2:C7), 2)→returns6.02

STDEV.P(number1, [number2], …)

Spread of a whole population.

For a whole population. Use it only when the rows really are everybody.

=ROUND(STDEV.P(C2:C7), 2)→returns5.39

VAR.S(number1, [number2], …)

Variance of a sample.

=VAR.S(C2:C7)→returns36.3

QUARTILE.INC(array, quart)

The value a quarter of the way through the data.

=QUARTILE.INC(C2:C7, 1)→returns7

PERCENTILE.INC(array, k)

The value at a given percentile.

=PERCENTILE.INC(C2:C7, 0.9)→returns17.4

LARGE(array, k)

The nth largest number.

The second largest. LARGE with k of 1 is MAX.

=LARGE(C2:C7, 2)→returns15

SMALL(array, k)

The nth smallest number.

=SMALL(C2:C7, 2)→returns7

CORREL(array1, array2)

How strongly two columns move together, from -1 to 1.

Near 1 means the two columns rise together. It is not evidence that one causes the other.

=ROUND(CORREL(C2:C6, D2:D6), 2)→returns0.99

SUMPRODUCT(array1, [array2], …)

Multiplies ranges row by row, then adds the results.

Multiplies each row's pair and totals the lot, which is how a weighted average gets its numerator.

=SUMPRODUCT(C2:C6, D2:D6)→returns300250

PRODUCT(number1, [number2], …)

Multiplies numbers together.

=PRODUCT(4, 5, 3)→returns60

ROUND(number, num_digits)

Rounds to a number of decimal places.

=ROUND(3.14159, 2)→returns3.14

ROUNDUP(number, num_digits)

Rounds away from zero.

Away from zero, so −4.21 rounds up to −4.3.

=ROUNDUP(4.21, 1)→returns4.3

ROUNDDOWN(number, num_digits)

Rounds towards zero.

=ROUNDDOWN(4.29, 1)→returns4.2

ABS(number)

Strips the minus sign.

=ABS(-250)→returns250

SQRT(number)

Square root.

=SQRT(144)→returns12

POWER(number, power)

Raises a number to a power.

=POWER(2, 10)→returns1024

INT(number)

Rounds down to a whole number.

Always downwards, so INT of −7.9 is −8, not −7.

=INT(7.9)→returns7

MOD(number, divisor)

The remainder after a division.

The remainder. MOD(row, 2) is the usual way to ask whether a row is odd or even.

=MOD(17, 5)→returns2

RANK.EQ(number, ref, [order])

Where a number sits in a list.

Largest first by default. Add a third argument of 1 to rank smallest first.

=RANK.EQ(C2, C2:C7)→returns3

IF(logical_test, value_if_true, [value_if_false])

Picks one of two answers based on a test.

=IF(C2>10, "Target met", "Below")→returnsTarget met

IFS(test1, value1, [test2, value2], …)

Tests several conditions in order and returns the first match.

Stops at the first test that passes, so the order of the tests is the whole formula. A final TRUE acts as the catch-all.

=IFS(C5>=15, "High", C5>=8, "Mid", TRUE, "Low")→returnsLow

AND(logical1, [logical2], …)

True only when everything is true.

=AND(C2>10, B2="North")→returnsTRUE

OR(logical1, [logical2], …)

True when at least one thing is true.

=OR(C5>10, B5="East")→returnsTRUE

NOT(logical)

Flips true and false.

=NOT(C5>10)→returnsTRUE

XOR(logical1, [logical2], …)

True when an odd number of the tests are true.

True when an odd number of the tests pass, which for two tests means exactly one of them.

=XOR(C2>10, C5>10)→returnsTRUE

TRUE

The value TRUE.

A value, not a function call: written bare, with no brackets.

=TRUE→returnsTRUE

FALSE

The value FALSE.

=FALSE→returnsFALSE

IFERROR(value, value_if_error)

Replaces an error with something readable.

Catches every error, which is also the risk: it will hide a genuine #REF! as readily as the #N/A you meant to tidy.

=IFERROR(D2/0, "No figure")→returnsNo figure

IFNA(value, value_if_na)

Replaces #N/A only.

Catches only #N/A, so a broken reference still shows up as broken. Safer than IFERROR around a lookup.

=IFNA(VLOOKUP("Gus", A2:D7, 4, FALSE), "Not on the list")→returnsNot on the list

ISERROR(value)

True when the value is any error.

=ISERROR(D2/0)→returnsTRUE

ISNA(value)

True when the value is #N/A.

=ISNA(MATCH("Gus", A2:A7, 0))→returnsTRUE

ISBLANK(value)

True when the cell is empty.

True only for a genuinely empty cell. A cell holding "" looks empty and is not.

=ISBLANK(C7)→returnsTRUE

ISNUMBER(value)

True when the cell really holds a number.

The quickest answer to why won't this column add up: run it down the column and look for FALSE.

=ISNUMBER(C2)→returnsTRUE

ISTEXT(value)

True when the cell holds text.

=ISTEXT(A2)→returnsTRUE

COUNTIF(range, criteria)

Counts the cells that meet one condition.

=COUNTIF(B2:B7, "North")→returns2

COUNTIFS(range1, criteria1, [range2, criteria2], …)

Counts the cells that meet every condition.

Every condition has to hold on the same row. The ranges must be the same shape.

=COUNTIFS(B2:B7, "South", C2:C7, ">10")→returns1

SUMIF(range, criteria, [sum_range])

Adds up only the cells that meet a condition.

The range tested and the range added are different arguments, which is the argument order people get wrong.

=SUMIF(B2:B7, "North", D2:D7)→returns12400

SUMIFS(sum_range, range1, criteria1, …)

Adds up the cells that meet every condition.

Reverse of SUMIF: here the range being added comes first.

=SUMIFS(D2:D7, B2:B7, "East", C2:C7, ">3")→returns1200

AVERAGEIF(range, criteria, [average_range])

Averages only the cells that meet a condition.

=AVERAGEIF(B2:B7, "South", D2:D7)→returns3675

AVERAGEIFS(average_range, range1, criteria1, …)

Averages the cells that meet every condition.

=AVERAGEIFS(D2:D7, F2:F7, "A")→returns6200

MAXIFS(max_range, range1, criteria1, …)

The largest value among the cells that match.

=MAXIFS(D2:D7, B2:B7, "North")→returns7600

MINIFS(min_range, range1, criteria1, …)

The smallest value among the cells that match.

=MINIFS(D2:D7, B2:B7, "South")→returns2100

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Finds a row by its first column and reads across.

The last argument is not optional in practice. Leave it off and Excel looks for the nearest match, which on unsorted data returns a confidently wrong row.

=VLOOKUP("Chen", A2:D7, 4, FALSE)→returns7600

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Finds a column by its first row and reads down.

VLOOKUP turned on its side, for a table laid out across rather than down.

=HLOOKUP("Revenue", A1:D7, 3, FALSE)→returns2100

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])

Finds a value in one range and returns the matching item from another.

Two separate ranges instead of a column number, so inserting a column cannot silently break it. Exact match is the default.

=XLOOKUP("Dara", A2:A7, D2:D7)→returns1200

INDEX(array, row_num, [column_num])

Reads the cell at a row and column position in a range.

Position in, value out. On its own it is rarely useful; with MATCH supplying the row number it replaces VLOOKUP.

=INDEX(A2:D7, 3, 1)→returnsChen

MATCH(lookup_value, lookup_array, [match_type])

Returns the position of a value in a range.

The third argument of 0 means exact. Without it MATCH assumes the list is sorted.

=MATCH("Eli", A2:A7, 0)→returns5

CHOOSE(index_num, value1, [value2], …)

Picks the nth of the values that follow.

=CHOOSE(2, "Gold", "Silver", "Bronze")→returnsSilver

ROWS(array)

How many rows a range covers.

=ROWS(A2:D7)→returns6

COLUMNS(array)

How many columns a range covers.

=COLUMNS(A1:F1)→returns6

LEN(text)

How many characters, spaces included.

Spaces count, which is how a stray trailing space gets caught.

=LEN("Spreadsheet")→returns11

LEFT(text, [num_chars])

The first few characters.

=LEFT("Spreadsheet", 6)→returnsSpread

RIGHT(text, [num_chars])

The last few characters.

=RIGHT("Spreadsheet", 5)→returnssheet

MID(text, start_num, num_chars)

Characters from the middle, by start position and length.

Counting starts at 1, not 0.

=MID("North-2024-Q1", 7, 4)→returns2024

TRIM(text)

Removes stray spaces from both ends and collapses runs inside.

Both ends stripped and runs inside collapsed to one space. The usual fix for a lookup that matches nothing for no visible reason.

=TRIM(" North Star ")→returnsNorth Star

UPPER(text)

ALL CAPITALS.

=UPPER("north")→returnsNORTH

LOWER(text)

all lower case.

=LOWER("North")→returnsnorth

PROPER(text)

Capitalises The First Letter Of Each Word.

=PROPER("ada north")→returnsAda North

CONCAT(text1, [text2], …)

Joins values into one piece of text.

The & operator does the same job in less space: A2 & " — " & B2.

=CONCAT(A2, " — ", B2)→returnsAda — North

CONCATENATE(text1, [text2], …)

Joins values into one piece of text.

The older name for CONCAT, kept because older workbooks are full of it.

=CONCATENATE(A2, " — ", B2)→returnsAda — North

TEXTJOIN(delimiter, ignore_empty, text1, …)

Joins values with a separator, optionally skipping blanks.

The one that takes a range, so a whole column becomes one comma-separated line.

=TEXTJOIN(", ", TRUE, A2:A4)→returnsAda, Blake, Chen

SUBSTITUTE(text, old_text, new_text, [instance_num])

Replaces every copy of one piece of text with another.

Matches on content. REPLACE matches on position.

=SUBSTITUTE("2024-Q1", "-", " ")→returns2024 Q1

REPLACE(old_text, start_num, num_chars, new_text)

Replaces characters by position.

=REPLACE("2024-Q1", 1, 4, "2025")→returns2025-Q1

FIND(find_text, within_text, [start_num])

Where one piece of text starts inside another. Case-sensitive.

Case-sensitive, and returns #VALUE! rather than 0 when the text is not there.

=FIND("Q", "2024-Q1")→returns6

SEARCH(find_text, within_text, [start_num])

Like FIND, but ignores capitals and allows wildcards.

Ignores capitals and takes wildcards, which is why it is the safer default of the two.

=SEARCH("q", "2024-Q1")→returns6

VALUE(text)

Turns text that looks like a number into a real number.

Turns a number stored as text into a real one, the repair for a column that will not total.

=VALUE("1200")→returns1200

REPT(text, number_times)

Repeats text a number of times.

Makes an in-cell bar chart when fed a count.

=REPT("*", 5)→returns*****

EXACT(text1, text2)

True only when two pieces of text match capital for capital.

The = operator ignores capitals. EXACT does not.

=EXACT("North", "north")→returnsFALSE

TEXT(value, format_text)

Formats a number as text.

Returns text, so the result can be joined to a sentence but can no longer be added up.

=TEXT(0.812, "0%")→returns81%

DATE(year, month, day)

Builds a date from a year, month and day.

The way to build a date from three numbers. Typing 02/03/2024 into a cell here leaves you with text.

=DATE(2024, 3, 2)→returns2 Mar 2024

YEAR(serial_number)

The year of a date.

=YEAR(E4)→returns2023

MONTH(serial_number)

The month of a date, 1 to 12.

A number from 1 to 12, not a month name. TEXT turns it into one.

=MONTH(E2)→returns1

DAY(serial_number)

The day of the month.

=DAY(E6)→returns30

WEEKDAY(serial_number, [return_type])

The day of the week, 1 = Sunday.

1 is Sunday by default, which catches people out every time: a Monday comes back as 2.

=WEEKDAY(E2)→returns2

DAYS(end_date, start_date)

How many days between two dates.

Plain subtraction, E5 − E2, gives the same number, because a date is a day count.

=DAYS(E5, E2)→returns145