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.
84 exercises
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
Work out what share of one funnel stage reached the next.
Express clicks as a percentage of the impressions that earned them.
Multiply quantity by unit price to get a line total.
Divide leavers by average headcount to get a turnover percentage.
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.
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
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 cost from revenue to find what each order actually earned.
Subtract leave taken from entitlement to see what each person has left.
Work out what a customer pays after their contracted discount.
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
Locate a keyword inside a longer description without worrying about capitals.
SEARCH
Take the trailing two-letter state abbreviation off the end of a territory code.
RIGHT
Divide campaign spend by the leads it generated.
Count how many rows meet a numeric condition.
COUNTIF
Identify the extremes in a column of values.
MAX · MIN
Use the median to describe a set of orders that one huge deal has skewed.
MEDIAN
Use IF to label each rep as meeting or missing their target.
IF
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
Add up only the rows matching a text condition.
SUMIF
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
Pull a fixed-length value out of the middle of a code that varies in shape.
MID · FIND
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
Anchor one end of a range so it grows as the formula moves down the column.
SUM
Look up a value that only exists two tables away from what you started with.
XLOOKUP
Write one formula that stays correct filled both across and down a grid.
SUMIFS
Use COUNTIF to reconcile two lists and spot what is absent.
COUNTIF · IF
Repair a formula written in SUMIF's argument order instead of SUMIFS'.
SUMIFS
Combine ranking with a lookup to return a name, not just a number.
LARGE · INDEX · MATCH
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
Add up the top three values without sorting the data or using a helper column.
LARGE
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 |
| 3 | Calculate a funnel conversion rateHarbor Supply — distributor sign-up funnel | — | Beginner | 5 min | 15 pts |
| 4 | Calculate click-through rateHarbor Supply — campaign performance export | — | Beginner | 4 min | 10 pts |
| 5 | Calculate line totals with multiplicationHarbor Supply — order ORD-1046 lines | — | Beginner | 3 min | 10 pts |
| 6 | Calculate the annual turnover rateHarbor Supply — annual turnover summary | — | 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 |
| 12 | Split a full name with LEFT and FINDHarbor Supply — CRM contact export | LEFT · FIND | Beginner | 7 min | 15 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 |
| 17 | Work out gross margin per orderHarbor Supply — order profitability | — | Beginner | 3 min | 10 pts |
| 18 | Work out remaining leave daysHarbor Supply — annual leave tracker | — | Beginner | 3 min | 10 pts |
| 19 | Apply a percentage discount to a list priceHarbor Supply — contracted distributor pricing | — | Beginner | 5 min | 15 pts |
| 20 | Build a mailing address with TEXTJOINHarbor Supply — customer addresses | TEXTJOIN | Beginner | 6 min | 15 pts |
| 21 | Calculate achievement against targetHarbor Supply — Q3 rep performance | — | Beginner | 3 min | 10 pts |
| 22 | Count how many orders shipped with COUNTHarbor Supply — shipment status | COUNT | Beginner | 4 min | 10 pts |
| 23 | Find the average order value with AVERAGEHarbor Supply — October orders | AVERAGE | Beginner | 3 min | 10 pts |
| 24 | Find where a word starts with SEARCHHarbor Supply — supplier product descriptions | SEARCH | Beginner | 5 min | 15 pts |
| 25 | Pull a state code out of a territory with RIGHTHarbor Supply — CRM territory codes | RIGHT | Beginner | 4 min | 10 pts |
| 26 | Work out cost per leadHarbor Supply — spend and leads by campaign | — | Beginner | 4 min | 10 pts |
| 27 | Count orders above a threshold with COUNTIFHarbor Supply — October orders | COUNTIF | Beginner | 5 min | 15 pts |
| 28 | Find the largest and smallest orderHarbor Supply — October orders | MAX · MIN | Beginner | 4 min | 10 pts |
| 29 | Find the typical order size with MEDIANHarbor Supply — October orders, one outlier | MEDIAN | Beginner | 5 min | 15 pts |
| 30 | Flag reps below target with IFHarbor Supply — Q3 rep performance with achievement | IF | 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 |
| 33 | Total sales for one region with SUMIFHarbor Supply — October orders by region | SUMIF | Beginner | 6 min | 15 pts |
| 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 |
| 70 | Extract a lot number from a messy codeHarbor Supply — warehouse barcode strings | MID · FIND | Advanced | 12 min | 35 pts |
| 71 | Build a regional summary from raw ordersHarbor Supply — raw October order export | COUNTIF · SUMIF | Advanced | 16 min | 40 pts |
| 72 | Fix a broken commission formulaHarbor Supply — commission run with a bug | IF | Advanced | 14 min | 40 pts |
| 73 | Handle missing lookups gracefullyHarbor Supply — orders including a new customer | XLOOKUP | Advanced | 10 min | 40 pts |
| 74 | Lock ranges so a lookup survives fill-downHarbor Supply — orders and customer reference | XLOOKUP | Advanced | 12 min | 40 pts |
| 75 | Build a running total that fills downHarbor Supply — monthly sales, cumulative column | SUM | Advanced | 12 min | 35 pts |
| 76 | Chain two lookups with XLOOKUPHarbor Supply — orders, customers and credit terms | XLOOKUP | Advanced | 13 min | 40 pts |
| 77 | Fill a two-way summary with SUMIFSHarbor Supply — order log, two months | SUMIFS | Advanced | 14 min | 40 pts |
| 78 | Find which customers are missing from a listHarbor Supply — CRM customers versus billing records | COUNTIF · IF | Advanced | 14 min | 40 pts |
| 79 | Fix a SUMIFS with its arguments swappedHarbor Supply — regional totals, formula under repair | SUMIFS | Advanced | 11 min | 35 pts |
| 80 | Name the second-best rep with LARGE and INDEXHarbor Supply — Q3 sales by rep, unsorted | LARGE · INDEX · MATCH | Advanced | 14 min | 40 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 |
| 83 | Total the three biggest ordersHarbor Supply — order values, unsorted export | LARGE | Advanced | 10 min | 35 pts |
| 84 | Why does a lookup fail on clean data?Conceptual question — no dataset | XLOOKUP | Advanced | 4 min | 30 pts |