Excel cheat sheet

The functions you use most, on one page, with their English names. Does your computer use European regional settings? Then Excel wants a ; between the arguments instead of a ,. And Excel in another language has other function names: in Dutch, SUM is SOM. In Lurndo you pick the names and the separator you work with. Arguments in [square brackets] are optional.

Tip: print this page or save it as a PDF.

Calculating

Every formula starts with =. Excel works out powers first, then * and /, then + and -. Brackets always come first.

SUM(number1, [number2], …)

=SUM(B2:B9)

Adds up all the numbers in the range. It skips text and empty cells.

Pitfall: =B2+B3+B4 works too, but a row you insert later won't count. A range grows along with it.

AVERAGE(number1, [number2], …)

=AVERAGE(C2:C9)

The average of the numbers.

Pitfall: An empty cell doesn't count, a cell with 0 does. That makes the average lower.

MAX(number1, [number2], …)

=MAX(C2:C9)

The largest number. MIN gives the smallest.

LARGE(array, k)

=LARGE(C2:C9,2)

The second largest number, or the k-th largest. SMALL does the same from the bottom.

ROUND(number, num_digits)

=ROUND(B2*1.21,2)

Rounds to two decimal places.

Pitfall: Showing a number with fewer decimals through the number format only rounds it on screen. Excel keeps calculating with the whole number.

Counting

COUNT(value1, [value2], …)

=COUNT(B2:B20)

How many cells contain a number.

Pitfall: A number stored as text in the cell (aligned to the left) doesn't count.

COUNTA(value1, [value2], …)

=COUNTA(A2:A20)

How many cells aren't empty, with text or numbers.

COUNTBLANK(range)

=COUNTBLANK(C2:C20)

How many cells are empty.

Logic

You compare with =, <>, <, >, <= and >=. The outcome is TRUE or FALSE.

IF(logical_test, value_if_true, [value_if_false])

=IF(C2>=50,"Order","Enough")

Gives one thing or the other, depending on whether the test is true.

Pitfall: Text goes in quotation marks, numbers don't. "50" is text, and to Excel that's not the same as 50.

AND(logical1, [logical2], …)

=IF(AND(B2>=18,C2="yes"),"Admitted","No")

TRUE if all the conditions are true. OR is TRUE if at least one is.

Pitfall: =B2>=18 AND C2="yes" doesn't exist. The conditions go into the function as arguments.

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)

=IFS(B2>=90,"A",B2>=70,"B",TRUE,"C")

More than two choices without nesting IF. The first test that's true wins.

Pitfall: Without a last test that's always true (TRUE), a value that fits nowhere gives #N/A.

IFERROR(value, value_if_error)

=IFERROR(B2/C2,0)

Shows something else instead of an error value such as #DIV/0!.

Pitfall: It hides every error, a typo in your formula too. Only wrap it around the formula once the formula works.

Counting and adding up with conditions

A criterion goes in quotation marks: "York", ">100". With a cell in it, you join the pieces with &: ">"&F1.

COUNTIF(range, criteria)

=COUNTIF(C2:C20,">100")

How many cells meet the criterion.

Pitfall: ">F1" looks for the text F1. For the value in F1, you write ">"&F1.

SUMIF(range, criteria, [sum_range])

=SUMIF(A2:A20,"Chips",C2:C20)

Adds up what's in C, on the rows where A says "Chips".

SUMIFS(sum_range, criteria_range1, criteria1, …)

=SUMIFS(D2:D20,A2:A20,"Chips",B2:B20,">=2")

Adding up with several conditions that must all be true.

Pitfall: The sum range comes first here, and last in SUMIF. The classic mix-up.

COUNTIFS(criteria_range1, criteria1, …)

=COUNTIFS(A2:A20,"Chips",C2:C20,">50")

Counting with several conditions.

AVERAGEIF(range, criteria, [average_range])

=AVERAGEIF(B2:B20,"York",C2:C20)

The average of the rows that meet the criterion.

Lookups

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

=VLOOKUP(F2,$A$2:$C$20,3,FALSE)

Looks up F2 in the first column of the table and gives what's in the third column.

Pitfall: Forget the fourth argument FALSE and Excel looks for an approximate match, so you sometimes get the wrong row without an error. And lock the table with $ before you fill down.

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

=XLOOKUP(F2,A2:A20,C2:C20,"Unknown")

Looks up in any direction, to the left too, and looks for an exact match by default.

Pitfall: Only exists in Microsoft 365 and Excel 2021 or later.

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

=HLOOKUP("March",B1:M5,3,FALSE)

Like VLOOKUP, but looks in the top row and gives a value below it.

MATCH(lookup_value, lookup_array, [match_type])

=MATCH(F2,A2:A20,0)

The position of F2 in the list.

Pitfall: Without the 0, it looks for an approximate match, just like VLOOKUP without FALSE.

INDEX(array, row_num, [column_num])

=INDEX(C2:C20,MATCH(F2,A2:A20,0))

The value at a position. Together with MATCH you can look up in any direction.

Text

You join text together with &, for example =A2&" "&B2.

LEFT(text, [num_chars])

=LEFT(A2,3)

The first three characters. RIGHT takes the last ones.

LEN(text)

=LEN(A2)

How many characters are in the cell, spaces included.

MID(text, start_num, num_chars)

=MID(A2,SEARCH(" ",A2)+1,20)

A piece from the middle, here everything after the first space.

SEARCH(find_text, within_text, [start_num])

=SEARCH("@",B2)

The position of a character in the text.

Pitfall: If the character isn't there, it gives #VALUE!.

TEXTBEFORE(text, delimiter)

=TEXTBEFORE(B2,"@")

Everything before the first @. TEXTAFTER gives everything after it.

TEXTJOIN(delimiter, ignore_empty, text1, …)

=TEXTJOIN(", ",TRUE,A2:A6)

Joins a whole range together, with a comma between the values.

TRIM(text)

=TRIM(A2)

Removes extra spaces, at the start, at the end and double ones.

SUBSTITUTE(text, old_text, new_text)

=SUBSTITUTE(A2,".",",")

Replaces every full stop with a comma.

Dates

A date is a number: the number of days since 1 January 1900. That's why you can subtract one date from another.

TODAY()

=TODAY()-B2

How many days ago the date in B2 was.

DATE(year, month, day)

=DATE(2026,12,31)

Makes a date out of three numbers.

Pitfall: ="31/12/2026" is text, not a date. You can't calculate with it.

YEAR(serial_number)

=YEAR(B2)

The year of a date. MONTH and DAY give the month and the day.

DATEDIF(start_date, end_date, unit)

=DATEDIF(B2,TODAY(),"Y")

How many whole years lie in between, an age for example. "M" gives months, "D" days.

Pitfall: Excel doesn't suggest this function while you type. You have to write it out in full yourself.

WEEKDAY(serial_number, [return_type])

=WEEKDAY(B2,2)

With type 2, Monday is 1 and Sunday is 7.

Pitfall: Without the type, the week starts on Sunday.

Dynamic arrays

These functions fill several cells at once: they spill. If something is already in the way, you get #SPILL!.

UNIQUE(array): =UNIQUE(A2:A50)
Every value once.
FILTER(array, include, [if_empty]): =FILTER(A2:C20,C2:C20>100,"None")
Only the rows that meet the condition.
SORT(array, [sort_index], [sort_order]): =SORT(A2:C20,3,-1)
Sorts by the third column, from largest to smallest.
SORTBY(array, by_array1, [sort_order1], …): =SORTBY(A2:A20,C2:C20,-1)
Sorts the names by another column, one you don't have to show.
LET(name1, name_value1, calculation): =LET(net,B2*C2,net*1.21)
Gives an intermediate result a name, so you don't have to work it out twice.