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.
36 exercises — Intermediate
Work out how many days old each invoice is.
TODAY
Cycle a reference through its absolute and relative forms while editing.
Combine date arithmetic with a conditional label.
IF · TODAY
Reach the paste-special dialog to drop formulas and keep results.
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
Pick the right summary statistic for a set with one huge outlier.
AVERAGE · MEDIAN
Return one of three labels depending on where a value falls.
IF
Count rows that satisfy several criteria at once.
COUNTIFS
Mark a campaign only when it overspends by more than a tolerance.
IF
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
Return the right rate from several tiers without nesting.
IFS
Build a conditional average out of a conditional sum and a conditional count.
SUMIF · COUNTIF
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
Check whether a salary falls below or above its grade band.
OR · IF
Match a customer ID against a reference table to return their region.
XLOOKUP
Compare the second-best and second-worst weeks to get a fairer range.
LARGE · SMALL
Add up spend for a single channel out of a mixed campaign list.
SUMIF
Sum the orders that beat the average, without typing the average in yourself.
SUMIF · AVERAGE
Add up overtime for a single team out of a mixed timesheet.
SUMIF
Apply two conditions at once to a conditional sum.
SUMIFS
Use VLOOKUP with exact match to pull prices from a product table.
VLOOKUP
Read a code's prefix and translate it into a readable category name.
IFS · LEFT
Recall the argument order that differs between SUMIF and SUMIFS.
SUMIF · SUMIFS
Combine INDEX and MATCH to look up a value without a fixed column number.
INDEX · MATCH
Match a weight to a rate band, where the weight will never appear in the table.
VLOOKUP
Return the campaign name behind the highest conversion count.
INDEX · MATCH · MAX
Identify the one situation VLOOKUP genuinely cannot handle.
VLOOKUP · XLOOKUP
| # | Exercise | Functions | Difficulty | Time | Status |
|---|---|---|---|---|---|
| 34 | Calculate invoice age with TODAYHarbor Supply — open invoices | TODAY | Intermediate | 8 min | 25 pts |
| 35 | Add the dollar signs without typing themNo dataset — this one is about your hands | — | Intermediate | 4 min | 20 pts |
| 36 | Flag overdue invoices with IF and TODAYHarbor Supply — aged debt | IF · TODAY | Intermediate | 9 min | 25 pts |
| 37 | Paste the numbers, not the formulasNo dataset — this one is about your hands | — | Intermediate | 4 min | 20 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 |
| 44 | Average or median for this data?Conceptual question — no dataset | AVERAGE · MEDIAN | Intermediate | 4 min | 20 pts |
| 45 | Band orders by size with nested IFHarbor Supply — October orders | IF | Intermediate | 9 min | 25 pts |
| 46 | Count orders meeting two conditionsHarbor Supply — orders across regions | COUNTIFS | Intermediate | 8 min | 25 pts |
| 47 | Flag campaigns over budget by a marginHarbor Supply — campaign budget versus actual | IF | 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 |
| 50 | Apply commission tiers with IFSHarbor Supply — Q3 sales by rep | IFS | Intermediate | 10 min | 25 pts |
| 51 | Average one region without AVERAGEIFHarbor Supply — orders by region | SUMIF · COUNTIF | Intermediate | 9 min | 30 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 |
| 56 | Flag salaries outside their bandHarbor Supply — pay band audit | OR · IF | Intermediate | 8 min | 25 pts |
| 57 | Look up a customer's region with XLOOKUPHarbor Supply — orders plus a customer reference table | XLOOKUP | Intermediate | 8 min | 25 pts |
| 58 | Measure the spread, ignoring the extremesHarbor Supply — weekly revenue, one quarter | LARGE · SMALL | Intermediate | 7 min | 25 pts |
| 59 | Total marketing spend for one channelHarbor Supply — campaign spend by channel | SUMIF | Intermediate | 7 min | 25 pts |
| 60 | Total only the orders above averageHarbor Supply — October order values | SUMIF · AVERAGE | Intermediate | 8 min | 25 pts |
| 61 | Total overtime hours for one teamHarbor Supply — overtime by team | SUMIF | Intermediate | 7 min | 25 pts |
| 62 | Total sales for a region and month with SUMIFSHarbor Supply — orders across regions and months | SUMIFS | Intermediate | 9 min | 25 pts |
| 63 | Look up a unit price with VLOOKUPHarbor Supply — quote lines and product master | VLOOKUP | Intermediate | 8 min | 25 pts |
| 64 | Turn a product code into a categoryHarbor Supply — SKU category decode | IFS · LEFT | Intermediate | 9 min | 30 pts |
| 65 | Which range does SUMIFS want first?Conceptual question — no dataset | SUMIF · SUMIFS | Intermediate | 3 min | 20 pts |
| 66 | Find a value with INDEX and MATCHHarbor Supply — commission rates by rep | INDEX · MATCH | Intermediate | 10 min | 25 pts |
| 67 | Look up a shipping band with VLOOKUPHarbor Supply — carrier rate card by weight band | VLOOKUP | Intermediate | 10 min | 30 pts |
| 68 | Name the best-performing campaignHarbor Supply — conversions by campaign, unsorted | INDEX · MATCH · MAX | Intermediate | 10 min | 30 pts |
| 69 | When can VLOOKUP not do the job?Conceptual question — no dataset | VLOOKUP · XLOOKUP | Intermediate | 3 min | 20 pts |