Unit 1

Cells and formulas

Write your first formulas, then copy them down a column.

  1. 01Your first formulaMake a cell add, subtract and multiply for you.Start
  2. 02Divide, brackets and percentagesSplit values with /, control the order with brackets, and work with %.Open
  3. 03Copy a formula downOne formula, every row.Open
  4. 04Lock it with $Absolute and mixed references.Open

Unit 2

Ranges and functions

Ranges, then SUM, AVERAGE, MAX, MIN, COUNT and ROUND.

  1. 05Ranges and SUMPoint at many cells at once, then add them all up.Open
  2. 06AVERAGE, MAX and MINThe typical value and the extremes of a range.Open
  3. 07COUNT and COUNTAHow many cells hold numbers, and how many aren't empty.Open
  4. 08Shares and percent changePart of a whole, and how much something grew.Open
  5. 09ROUND, and functions inside functionsChange the value itself, and combine two functions in one formula.Open

Unit 3

Decisions

Comparisons, IF, nested IFs, AND, OR, IFS and IFERROR: formulas that choose.

Pro
  1. TRUE, FALSE and IFAsk a yes/no question, then choose a result.Pro
  2. More than two outcomesPut an IF inside another IF.Pro
  3. AND and ORCombine conditions.Pro
  4. IFS and SWITCHMany outcomes without the nesting.Pro
  5. Errors and blank cellsCatch errors and empty cells before they spread.Pro

Unit 4

Lookups

VLOOKUP, INDEX/MATCH and XLOOKUP: pull data from another table.

Pro
  1. VLOOKUPFind a row by its key and return a column.Pro
  2. INDEX and MATCHLookups in any direction.Pro
  3. XLOOKUPThe modern lookup, with a built-in fallback.Pro

Unit 5

Conditional totals

SUMIF, SUMIFS, COUNTIFS and AVERAGEIFS: totals for just the rows you want.

Pro
  1. SUMIF and criteriaAdd up only the rows that match.Pro
  2. SUMIFSSeveral conditions at once.Pro
  3. COUNTIF, AVERAGEIFS and MAXIFSCount, average and find the largest with conditions.Pro
  4. WildcardsMatch part of a text.Pro

Unit 6

Text and dates

Clean up names, cut and join text, and calculate with dates.

Pro
  1. Tidy text: TRIM and PROPERExtra spaces out, capitals fixed.Pro
  2. Cut text: LEFT, RIGHT and MIDTake characters from the start, the end or the middle.Pro
  3. Find a position: FIND and LENWhere a character is, and how long a text is.Pro
  4. Join text and TEXT()Build labels with &, TEXT and TEXTJOIN.Pro
  5. DatesDate maths, DATE, MONTH and EOMONTH.Pro

Unit 7

Dates and time maths

Ages, tenure, working days and hours worked.

Pro
  1. Years, months and tenureDATEDIF, YEARFRAC and EDATE.Pro
  2. Weekdays and working daysNETWORKDAYS, WEEKDAY, WORKDAY and week numbers.Pro
  3. Hours and timesTime values, hours worked and night shifts.Pro

Unit 8

Cleaning text at scale

Fix messy exports: stray characters, split names, numbers stored as text.

Pro
  1. SUBSTITUTE, CLEAN and invisible spacesRemove characters TRIM can't see.Pro
  2. Split names and codesFIND inside LEFT and MID, and TEXTAFTER.Pro
  3. Numbers stored as textVALUE, SUMPRODUCT, and why SUM shows 0.Pro

Unit 9

Data quality

Find duplicates and check types before you trust the data.

Pro
  1. Find duplicatesCOUNTIF checks and counting distinct values.Pro
  2. Check and convert typesISNUMBER, COUNTBLANK and allowed values.Pro

Unit 10

Advanced lookups

Two-way lookups, tiers and brackets, and lookups on several conditions.

Pro
  1. Two-way lookupsFind a value by its row and column labels.Pro
  2. Tiers and bracketsApproximate match for grades, rates and price bands.Pro
  3. Lookups on several conditionsMatch a row by month, region and product at once.Pro

Unit 11

Dynamic arrays

FILTER, UNIQUE, SORT and SEQUENCE: one formula, a whole spilled result.

Pro
  1. FILTERReturn every row that matches, as a spilled list.Pro
  2. UNIQUE and SORTDistinct lists, sorted results, top N.Pro
  3. SEQUENCE and spill rangesGenerate lists, point at spills with #, read #SPILL!.Pro

Unit 12

LET and LAMBDA

Name parts of a formula, and build your own functions.

Pro
  1. LETName a value once, use it many times.Pro
  2. LAMBDAYour own functions, and MAP, BYROW and SCAN.Pro

Unit 13

Statistics

Typical values, spread, ranks and percentiles.

Pro
  1. Typical values and spreadMEDIAN, MODE, STDEV.S and CORREL.Pro
  2. Ranks, percentiles and bandsRANK.EQ, PERCENTILE, QUARTILE and bucket counts.Pro

Unit 14

Money and finance

Interest, loan payments, savings and project returns.

Pro
  1. Simple and compound interestGrowth over time, and FV.Pro
  2. Loan paymentsPMT, total interest and NPER.Pro
  3. Amortization scheduleInterest, principal and balance, month by month.Pro
  4. Savings, NPV and IRRRegular saving, and whether a project pays off.Pro

Unit 15

Reporting patterns

Summary grids, shares, running totals, growth and top-N.

Pro
  1. Summary grids and SUMPRODUCTCross-tabs from one formula, and percent of total.Pro
  2. Running totals and growthYear to date, month on month, year on year.Pro
  3. Top N and rankingsLARGE, SMALL and who's behind each number.Pro

Unit 16

Job test pack

Original, timed problems like the spreadsheet tests in job interviews.

Pro
  1. Warm-up roundFour quick tasks on a support-ticket export.Pro
  2. Core roundFive tasks mixing lookups and conditional totals.Pro
  3. Final roundFour harder tasks with dynamic arrays.Pro
  4. Case studyBuild a per-agent summary that ties out, then make the call.Pro