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.
33 exercises — Beginner
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
| # | 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 |