Formula cheat sheet

Every function in the course, with its syntax, what it does and an example. Works for Excel and Google Sheets (each lesson notes the differences).

Unit 2 · Ranges and functions

SUM(range)
Adds numbers, cells and ranges. =SUM(D2:D9) Practise
AVERAGE(range)
The average (arithmetic mean) of the numbers. =AVERAGE(D2:D9) Practise
MAX(range)
The largest number. =MAX(G2:G13) Practise
MIN(range)
The smallest number. =MIN(F2:F13) Practise
COUNT(range)
Counts the cells that contain numbers. =COUNT(F2:F13) Practise
COUNTA(range)
Counts the cells that aren't empty. =COUNTA(D2:D13) Practise
ROUND(number, digits)
Rounds a number to a number of digits. =ROUND(D2/C2,2) Practise

Unit 3 · Decisions

IF(condition, value_if_true, value_if_false)
One value if a condition is TRUE, another if it's FALSE. =IF(D2>C2,D2-C2,0) Practise
AND(condition1, condition2, …)
TRUE if every condition is TRUE. =AND(G2>=F2,E2>C2) Practise
OR(condition1, condition2, …)
TRUE if any condition is TRUE. =OR(H2="Pending",H2="Returned") Practise
IFS(test1, result1, test2, result2, …)
The value for the first condition that's TRUE. =IFS(G2>=40000,"Gold",G2>=35000,"Silver",TRUE,"Bronze") Practise
SWITCH(value, match1, result1, …, default)
Compares a value with a list of cases and returns the match. =SWITCH(D2,"Basic","B","Pro","P","Team","T","?") Practise
IFERROR(value, fallback)
A fallback value if the formula gives an error. =IFERROR(C2/B2,0) Practise
ISBLANK(cell)
TRUE if the cell is empty. =ISBLANK(E2) Practise

Unit 4 · Lookups

VLOOKUP(key, table, column, FALSE)
Looks down the first column and returns a value from that row. =VLOOKUP(B2,$G$2:$J$8,4,FALSE) Practise
MATCH(key, range, 0)
The position of a value in a row or column. =MATCH(M1,H2:H8,0) Practise
INDEX(range, n)
The value at a row and column position in a range. =INDEX(I2:I8,M1) Practise
XLOOKUP(key, lookup_range, return_range)
Finds a value in one range and returns the match from another. =XLOOKUP(B2,$G$2:$G$8,$I$2:$I$8) Practise

Unit 5 · Conditional totals

SUMIF(range, criteria, sum_range)
Adds the cells that meet one condition. =SUMIF(E2:E13,"Plants",I2:I13) Practise
SUMIFS(sum_range, range1, criteria1, range2, criteria2, …)
Adds the cells that meet every condition. =SUMIFS(F2:F13,E2:E13,"Plants",H2:H13,"Shipped") Practise
COUNTIF(range, criteria)
Counts the cells that meet one condition. =COUNTIF(B2:B9,"North") Practise
COUNTIFS(range1, criteria1, range2, criteria2, …)
Counts the rows that meet every condition. =COUNTIFS(B2:B9,"South",C2:C9,">10000") Practise
AVERAGEIFS(average_range, range1, criteria1, …)
The average of the cells that meet every condition. =AVERAGEIFS(C2:C9,B2:B9,"East") Practise
MAXIFS(max_range, range1, criteria1, …)
The largest value among the cells that meet every condition. =MAXIFS(E2:E9,B2:B9,"North") Practise

Unit 6 · Text and dates

TRIM(text)
Removes extra spaces from text. =TRIM(A2) Practise
PROPER(text)
Capitalises The First Letter Of Each Word. =PROPER(A3) Practise
LEFT(text, n)
Characters from the start of the text. =LEFT(B2,4) Practise
RIGHT(text, n)
Characters from the end of the text. =RIGHT(C2,4) Practise
MID(text, start, n)
Characters from the middle of the text. =MID(C2,4,2) Practise
FIND(what, text)
Where one text starts inside another (case-sensitive). =FIND("@",B2) Practise
LEN(text)
How many characters the text has. =LEN(A2) Practise
TEXT(value, format)
Formats a number or date as text, like "$#,##0.00". =TEXT(G2,"$#,##0.00") Practise
TEXTJOIN(separator, ignore_empty, range)
Joins text with a delimiter between the pieces. =TEXTJOIN(" / ",TRUE,C2:C9) Practise
DATE(year, month, day)
Builds a date from a year, month and day. =DATE(B2,C2,D2) Practise
MONTH(date)
The month (1 to 12) of a date. =MONTH(D2) Practise
EOMONTH(date, months)
The last day of the month, a number of months away. =EOMONTH(F2,0) Practise

Unit 7 · Dates and time maths

DATEDIF(start, end, unit)
Whole years, months or days between two dates. =DATEDIF(C2,$H$2,"Y") Practise
YEARFRAC(start, end, basis)
The fraction of a year between two dates. =YEARFRAC(D2,$H$2,1) Practise
EDATE(date, months)
The date a number of months before or after a date. =EDATE(C2,3) Practise
NETWORKDAYS(start, end, holidays)
Working days between two dates (Monday to Friday). =NETWORKDAYS(C2,D2,$H$2:$H$4) Practise
WEEKDAY(date, 2)
The day of the week of a date, as a number. =WEEKDAY(D2,2) Practise
WORKDAY(start, days, holidays)
The date a number of working days away. =WORKDAY(C2,5,$H$2:$H$4) Practise
ISOWEEKNUM(date)
The ISO week number of a date. =ISOWEEKNUM(D2) Practise
NETWORKDAYS.INTL(start, end, weekend, holidays)
Working days between two dates, with your own weekend. =NETWORKDAYS.INTL(C2,D2,11,$H$2:$H$4) Practise
MOD(number, divisor)
The remainder after dividing. =MOD(D2-C2,1) Practise

Unit 8 · Cleaning text at scale

SUBSTITUTE(text, old, new)
Replaces one piece of text with another. =SUBSTITUTE(B2," ","") Practise
CLEAN(text)
Removes non-printing characters from text. =CLEAN(E5) Practise
CHAR(code)
The character with this code number (10 is a line break). =SUBSTITUTE(E2,CHAR(160)," ") Practise
TEXTAFTER(text, delimiter)
The text after a delimiter. =TEXTAFTER(B2," ") Practise
VALUE(text)
Turns text that looks like a number into a number. =VALUE(D2) Practise
SUMPRODUCT(range1, range2)
Multiplies ranges item by item, then adds the results. =SUMPRODUCT(F2:F13,G2:G13) Practise

Unit 9 · Data quality

ISNUMBER(value)
TRUE if the value is a number. =ISNUMBER(C2) Practise
COUNTBLANK(range)
Counts the empty cells in a range. =COUNTBLANK(C2:C11) Practise

Unit 11 · Dynamic arrays

FILTER(array, include)
Keeps the rows that meet a condition (spills). =FILTER(A2:A9,B2:B9="North") Practise
ROWS(range)
How many rows a range or array has. =ROWS(A2:A9) Practise
UNIQUE(range)
The distinct values of a range (spills). =UNIQUE(B2:B17) Practise
SORT(array, column, order)
Sorts a range (spills). =SORT(C2:C9,1,-1) Practise
SORTBY(array, by_range, order)
Sorts a range by another range (spills). =SORTBY(A2:A9,C2:C9,-1) Practise
TAKE(array, n)
Keeps rows or columns from the start or end of an array. =TAKE(SORTBY(A2:A9,C2:C9,-1),3) Practise
SEQUENCE(rows)
A list of numbers in rows and columns (spills). =DATE(B1,SEQUENCE(12),1) Practise

Unit 12 · LET and LAMBDA

LET(name, value, …, calculation)
Names values inside a formula, then calculates with them. =LET(total,F2*G2,tax,total*$L$1,total+tax) Practise
LAMBDA(parameter, …, calculation)(value, …)
Makes your own function from parameters and a calculation. =LAMBDA(q,p,q*p)(B2,C2) Practise
MAP(range1, range2, LAMBDA(a, b, calculation))
Runs a LAMBDA on every value of an array. =MAP(B2:B6,C2:C6,LAMBDA(q,p,q*p)) Practise
BYROW(range, LAMBDA(row, calculation))
Runs a LAMBDA on each row and returns one result per row. =BYROW(C2:E9,LAMBDA(r,SUM(r))) Practise
SCAN(start, range, LAMBDA(total, value, calculation))
Like REDUCE, but returns every step (spills). =SCAN(0,B2:B7,LAMBDA(t,v,t+v)) Practise

Unit 13 · Statistics

MEDIAN(range)
The middle number. =MEDIAN(D2:D13) Practise
MODE.SNGL(range)
The most common number. =MODE.SNGL(C2:C13) Practise
STDEV.S(range)
Standard deviation of a sample. =STDEV.S(C2:C13) Practise
CORREL(range1, range2)
The correlation coefficient of two ranges. =CORREL(C2:C9,E2:E9) Practise
RANK.EQ(value, range)
The rank of a number in a list (ties share a rank). =RANK.EQ(C2,$C$2:$C$13) Practise
PERCENTILE.INC(range, k)
The k-th percentile of a range (k from 0 to 1). =PERCENTILE.INC(D2:D13,0.9) Practise
QUARTILE.INC(range, quart)
A quartile of a range (0 to 4). =QUARTILE.INC(D2:D13,1) Practise

Unit 14 · Money and finance

FV(rate, periods, payment, present_value)
The future value of an investment. =FV(B3,B4,0,-B2) Practise
PMT(rate, periods, amount)
The payment per period of a loan. =PMT(B3/12,B4*12,B2) Practise
NPER(rate, payment, amount)
The number of payment periods of a loan or investment. =NPER(B3/12,-400,B2) Practise
IPMT(rate, period, periods, amount)
The interest part of one loan payment. =-IPMT($B$3/12,G2,$B$4*12,$B$2) Practise
PPMT(rate, period, periods, amount)
The principal part of one loan payment. =-PPMT(B3/12,1,B4*12,B2) Practise
NPV(rate, cash_flows)
The net present value of future cash flows. =NPV(10%,B12:B16)+B11 Practise
IRR(cash_flows)
The internal rate of return of cash flows. =IRR(B11:B16) Practise

Unit 15 · Reporting patterns

LARGE(range, k)
The k-th largest number in a range. =LARGE($C$2:$C$9,G2) Practise
SMALL(range, k)
The k-th smallest number in a range. =SMALL($E$2:$E$9,G2) Practise

Practise this in 5 minutes

A cheat sheet reminds you; practice makes it stick. Lesson 1 has you typing real formulas on a live sheet, with instant feedback.

Start the first lesson

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.