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.
18 exercises — Intermediate · for Operations
Work out how many days old each invoice is.
TODAY
Extract characters from the middle of a text value.
MID
Extend a selection to the last filled row in one press.
Cut a combined location field at the comma without counting characters by hand.
LEFT · FIND
Clean inconsistent spacing and casing so values can be matched.
Join address parts into one line without leaving gaps where a field is empty.
TEXTJOIN
Require two conditions to be true at once.
IF · AND
Count rows that satisfy several criteria at once.
COUNTIFS
Find rows whose order number appears more than once in the column.
COUNTIF · IF
Guard a division so cancelled lines do not fill the report with errors.
IF
Narrow a count with three criteria at once rather than filtering by hand.
COUNTIFS
Apply an upper and a lower bound to the same column at once.
COUNTIFS
Count people in one department whose last review was long enough ago.
COUNTIFS
Combine two logical tests so one condition can override the other.
AND · OR · IF
Compare the second-best and second-worst weeks to get a fairer range.
LARGE · SMALL
Add up overtime for a single team out of a mixed timesheet.
SUMIF
Read a code's prefix and translate it into a readable category name.
IFS · LEFT
Match a weight to a rate band, where the weight will never appear in the table.
VLOOKUP
| # | Exercise | Functions | Difficulty | Time | Status |
|---|---|---|---|---|---|
| 34 | Calculate invoice age with TODAYHarbor Supply — open invoices | TODAY | Intermediate | 8 min | 25 pts |
| 38 | Pull the year out of an order code with MIDHarbor Supply — order codes | MID | Intermediate | 7 min | 25 pts |
| 39 | Select to the bottom of the dataNo dataset — this one is about your hands | — | Intermediate | 4 min | 20 pts |
| 40 | Split a city away from its stateHarbor Supply — carrier destination export | LEFT · FIND | Intermediate | 8 min | 25 pts |
| 41 | Standardise messy codes with TRIM and UPPERHarbor Supply — supplier feed, uncleaned | — | Intermediate | 7 min | 25 pts |
| 42 | Skip blanks when joining an addressHarbor Supply — shipping label fields | TEXTJOIN | Intermediate | 7 min | 25 pts |
| 43 | Approve orders with ANDHarbor Supply — order approval queue | IF · AND | Intermediate | 8 min | 25 pts |
| 46 | Count orders meeting two conditionsHarbor Supply — orders across regions | COUNTIFS | Intermediate | 8 min | 25 pts |
| 48 | Spot duplicate order numbers with COUNTIFHarbor Supply — order import, run twice | COUNTIF · IF | Intermediate | 9 min | 30 pts |
| 49 | Work out fill rate without a divide errorHarbor Supply — fill rate by line, some cancelled | IF | Intermediate | 8 min | 25 pts |
| 52 | Count backorders on three conditionsHarbor Supply — open order lines by warehouse | COUNTIFS | Intermediate | 8 min | 25 pts |
| 53 | Count orders inside a value bandHarbor Supply — order values, last month | COUNTIFS | Intermediate | 7 min | 25 pts |
| 54 | Count staff overdue a reviewHarbor Supply — months since last review | COUNTIFS | Intermediate | 8 min | 25 pts |
| 55 | Flag accounts for review with AND and ORHarbor Supply — accounts receivable review | AND · OR · IF | Intermediate | 9 min | 30 pts |
| 58 | Measure the spread, ignoring the extremesHarbor Supply — weekly revenue, one quarter | LARGE · SMALL | Intermediate | 7 min | 25 pts |
| 61 | Total overtime hours for one teamHarbor Supply — overtime by team | SUMIF | Intermediate | 7 min | 25 pts |
| 64 | Turn a product code into a categoryHarbor Supply — SKU category decode | IFS · LEFT | Intermediate | 9 min | 30 pts |
| 67 | Look up a shipping band with VLOOKUPHarbor Supply — carrier rate card by weight band | VLOOKUP | Intermediate | 10 min | 30 pts |