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.
40 exercises — for Operations
Turn separate year, month and day columns into a real date Excel understands.
DATE
Join a category prefix and an item number into a single SKU.
CONCAT
Use LEN to validate that product codes are the right length.
LEN
Divide a unit count by the case size to see how many cases were ordered.
Count how many people sit in a single department on a staff list.
COUNTIF
Open a cell for editing without reaching for the mouse or the formula bar.
Copy the cell above into the cells you have selected, without dragging.
Add up a column of daily sales figures to get the monthly total.
SUM
Stamp the current date into a cell as a fixed value.
Pin down what $ changes about a reference, and what it does not.
Read an error code as a diagnosis rather than a failure.
Subtract leave taken from entitlement to see what each person has left.
Combine separate address fields into one line, skipping blanks.
TEXTJOIN
Use COUNT to find how many rows contain a numeric value.
COUNT
Locate a keyword inside a longer description without worrying about capitals.
SEARCH
Count how many rows meet a numeric condition.
COUNTIF
Pick out the second-cheapest supplier quote rather than simply the cheapest.
SMALL
Mark an order when either of two independent conditions is true.
OR · IF
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
Pull a fixed-length value out of the middle of a code that varies in shape.
MID · FIND
Return a name from one column based on a ranked value in another.
INDEX · MATCH · SMALL
Match two lists where one side has trailing spaces you cannot see.
COUNTIF · IF
Diagnose a match failure where both values look identical on screen.
XLOOKUP
| # | Exercise | Functions | Difficulty | Time | Status |
|---|---|---|---|---|---|
| 1 | Assemble a delivery date with DATEHarbor Supply — inbound delivery schedule | DATE | Beginner | 5 min | 15 pts |
| 2 | Build a product code with CONCATHarbor Supply — new catalogue load | CONCAT | Beginner | 4 min | 10 pts |
| 7 | Check code length with LENHarbor Supply — product code import | LEN | Beginner | 3 min | 10 pts |
| 8 | Convert loose units into full casesHarbor Supply — pick list, units to cases | — | Beginner | 3 min | 10 pts |
| 9 | Count the headcount in one departmentHarbor Supply — staff list by department | COUNTIF | Beginner | 4 min | 10 pts |
| 10 | Edit the cell you are standing onNo dataset — this one is about your hands | — | Beginner | 2 min | 10 pts |
| 11 | Fill a formula down a columnNo dataset — this one is about your hands | — | Beginner | 3 min | 10 pts |
| 13 | Total a month of sales with SUMHarbor Supply — daily sales, first week of October | SUM | Beginner | 3 min | 10 pts |
| 14 | Type today's date without typing itNo dataset — this one is about your hands | — | Beginner | 3 min | 10 pts |
| 15 | What does the dollar sign actually do?Conceptual question — no dataset | — | Beginner | 3 min | 15 pts |
| 16 | What is Excel telling you with #N/A?Conceptual question — no dataset | — | Beginner | 3 min | 15 pts |
| 18 | Work out remaining leave daysHarbor Supply — annual leave tracker | — | Beginner | 3 min | 10 pts |
| 20 | Build a mailing address with TEXTJOINHarbor Supply — customer addresses | TEXTJOIN | Beginner | 6 min | 15 pts |
| 22 | Count how many orders shipped with COUNTHarbor Supply — shipment status | COUNT | Beginner | 4 min | 10 pts |
| 24 | Find where a word starts with SEARCHHarbor Supply — supplier product descriptions | SEARCH | Beginner | 5 min | 15 pts |
| 27 | Count orders above a threshold with COUNTIFHarbor Supply — October orders | COUNTIF | Beginner | 5 min | 15 pts |
| 31 | Find the second-lowest quote with SMALLHarbor Supply — pallet contract tender | SMALL | Beginner | 4 min | 10 pts |
| 32 | Flag orders needing special handling with ORHarbor Supply — warehouse pick list | OR · IF | Beginner | 5 min | 15 pts |
| 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 |
| 70 | Extract a lot number from a messy codeHarbor Supply — warehouse barcode strings | MID · FIND | Advanced | 12 min | 35 pts |
| 81 | Name the third-fastest supplierHarbor Supply — supplier lead times | INDEX · MATCH · SMALL | Advanced | 13 min | 40 pts |
| 82 | Reconcile a list with hidden spacesHarbor Supply — export vs master customer list | COUNTIF · IF | Advanced | 12 min | 35 pts |
| 84 | Why does a lookup fail on clean data?Conceptual question — no dataset | XLOOKUP | Advanced | 4 min | 30 pts |