Real situations from working spreadsheets — sales targets, commission tiers, messy exports, aged invoices. Numbered in recommended order: start at 1 and work down, or filter to what you need.
30 exercises
Multiply quantity by unit price to get a line total.
Use LEN to validate that product codes are the right length.
LEN
Pull the first name out of a single full-name column.
LEFT · FIND
Add up a column of daily sales figures to get the monthly total.
SUM
Combine separate address fields into one line, skipping blanks.
TEXTJOIN
Divide actual sales by target to get an achievement percentage.
Use COUNT to find how many rows contain a numeric value.
COUNT
Calculate the mean value of a set of customer orders.
AVERAGE
Count how many rows meet a numeric condition.
COUNTIF
Identify the extremes in a column of values.
MAX · MIN
Use IF to label each rep as meeting or missing their target.
IF
Add up only the rows matching a text condition.
SUMIF
Work out how many days old each invoice is.
TODAY
Combine date arithmetic with a conditional label.
IF · TODAY
Extract characters from the middle of a text value.
MID
Clean inconsistent spacing and casing so values can be matched.
Require two conditions to be true at once.
IF · AND
Return one of three labels depending on where a value falls.
IF
Count rows that satisfy several criteria at once.
COUNTIFS
Return the right rate from several tiers without nesting.
IFS
Match a customer ID against a reference table to return their region.
XLOOKUP
Apply two conditions at once to a conditional sum.
SUMIFS
Use VLOOKUP with exact match to pull prices from a product table.
VLOOKUP
Combine INDEX and MATCH to look up a value without a fixed column number.
INDEX · MATCH
Produce a summary line combining count, total and average for one region.
COUNTIF · SUMIF
Diagnose and correct a formula that returns the wrong answer.
IF
Return a readable message instead of #N/A when a lookup finds nothing.
XLOOKUP
Use absolute references so a lookup does not break when copied down a column.
XLOOKUP
Use COUNTIF to reconcile two lists and spot what is absent.
COUNTIF · IF
Combine ranking with a lookup to return a name, not just a number.
LARGE · INDEX · MATCH
| # | Exercise | Functions | Difficulty | Time | Status |
|---|---|---|---|---|---|
| 1 | Calculate line totals with multiplicationHarbor Supply — order ORD-1046 lines | — | Beginner | 3 min | 10 pts |
| 2 | Check code length with LENHarbor Supply — product code import | LEN | Beginner | 3 min | 10 pts |
| 3 | Split a full name with LEFT and FINDHarbor Supply — CRM contact export | LEFT · FIND | Beginner | 7 min | 15 pts |
| 4 | Total a month of sales with SUMHarbor Supply — daily sales, first week of October | SUM | Beginner | 3 min | 10 pts |
| 5 | Build a mailing address with TEXTJOINHarbor Supply — customer addresses | TEXTJOIN | Beginner | 6 min | 15 pts |
| 6 | Calculate achievement against targetHarbor Supply — Q3 rep performance | — | Beginner | 3 min | 10 pts |
| 7 | Count how many orders shipped with COUNTHarbor Supply — shipment status | COUNT | Beginner | 4 min | 10 pts |
| 8 | Find the average order value with AVERAGEHarbor Supply — October orders | AVERAGE | Beginner | 3 min | 10 pts |
| 9 | Count orders above a threshold with COUNTIFHarbor Supply — October orders | COUNTIF | Beginner | 5 min | 15 pts |
| 10 | Find the largest and smallest orderHarbor Supply — October orders | MAX · MIN | Beginner | 4 min | 10 pts |
| 11 | Flag reps below target with IFHarbor Supply — Q3 rep performance with achievement | IF | Beginner | 5 min | 15 pts |
| 12 | Total sales for one region with SUMIFHarbor Supply — October orders by region | SUMIF | Beginner | 6 min | 15 pts |
| 13 | Calculate invoice age with TODAYHarbor Supply — open invoices | TODAY | Intermediate | 8 min | 25 pts |
| 14 | Flag overdue invoices with IF and TODAYHarbor Supply — aged debt | IF · TODAY | Intermediate | 9 min | 25 pts |
| 15 | Pull the year out of an order code with MIDHarbor Supply — order codes | MID | Intermediate | 7 min | 25 pts |
| 16 | Standardise messy codes with TRIM and UPPERHarbor Supply — supplier feed, uncleaned | — | Intermediate | 7 min | 25 pts |
| 17 | Approve orders with ANDHarbor Supply — order approval queue | IF · AND | Intermediate | 8 min | 25 pts |
| 18 | Band orders by size with nested IFHarbor Supply — October orders | IF | Intermediate | 9 min | 25 pts |
| 19 | Count orders meeting two conditionsHarbor Supply — orders across regions | COUNTIFS | Intermediate | 8 min | 25 pts |
| 20 | Apply commission tiers with IFSHarbor Supply — Q3 sales by rep | IFS | Intermediate | 10 min | 25 pts |
| 21 | Look up a customer's region with XLOOKUPHarbor Supply — orders plus a customer reference table | XLOOKUP | Intermediate | 8 min | 25 pts |
| 22 | Total sales for a region and month with SUMIFSHarbor Supply — orders across regions and months | SUMIFS | Intermediate | 9 min | 25 pts |
| 23 | Look up a unit price with VLOOKUPHarbor Supply — quote lines and product master | VLOOKUP | Intermediate | 8 min | 25 pts |
| 24 | Find a value with INDEX and MATCHHarbor Supply — commission rates by rep | INDEX · MATCH | Intermediate | 10 min | 25 pts |
| 25 | Build a regional summary from raw ordersHarbor Supply — raw October order export | COUNTIF · SUMIF | Advanced | 16 min | 40 pts |
| 26 | Fix a broken commission formulaHarbor Supply — commission run with a bug | IF | Advanced | 14 min | 40 pts |
| 27 | Handle missing lookups gracefullyHarbor Supply — orders including a new customer | XLOOKUP | Advanced | 10 min | 40 pts |
| 28 | Lock ranges so a lookup survives fill-downHarbor Supply — orders and customer reference | XLOOKUP | Advanced | 12 min | 40 pts |
| 29 | Find which customers are missing from a listHarbor Supply — CRM customers versus billing records | COUNTIF · IF | Advanced | 14 min | 40 pts |
| 30 | Name the second-best rep with LARGE and INDEXHarbor Supply — Q3 sales by rep, unsorted | LARGE · INDEX · MATCH | Advanced | 14 min | 40 pts |