Excel Supreme
Search

Excel formula exercises

86 real spreadsheet tasks. Type the formula or the keys, and get told exactly what is wrong.

Start exercise 1 Your progress

SUM: total a column Exercise 01 · B5
ABC
1RegionQ1Q2
2North1200900
3South14501100
4West930700
5Total Q1

B5 =SUM(B2:B4) Accepted

Type =SUM(1200,1450,930) and the grader answers with the rule it broke:

types the numbers into SUM instead of pointing at B2:B4

How an exercise works

  1. A small spreadsheet is drawn in the page. Nothing is uploaded, and there is no file to open.
  2. The task names the cell, and you type one formula in the formula bar under the sheet.
  3. Check it. The grader runs in this browser and names the rule the formula broke: the wrong function, the wrong range, or a typed number where a reference belongs.

Browse the exercises

10 of the 86 are free, with no account and no card. The other 76 come with a paid plan.

Beginner

9 exercises
  1. SUM: total a column Total a column with one function instead of typing every cell.
  2. AVERAGE: mean of a score list Turn five scores into one class average that updates itself.
  3. MIN and MAX: fastest and slowest Paid Return the smallest or largest number in a range without sorting the sheet first.
  4. COUNTIF: count one condition Count the rows that meet one condition, and get the edges of the range right.
  5. COUNT: numbers only, and what it skips Paid Count the cells that hold real numbers, and read a short count as evidence of text.
  6. COUNTA: count the non-empty cells Paid Count filled cells rather than numbers, so a checklist reports real progress.
  7. IF: one test, two answers Write a decision that returns one of two values, chosen by a single test.
  8. LEN, LEFT and RIGHT: count and cut Measure a string, then cut fixed pieces from either end.
  9. Select to the edge of a block Paid Select a whole column of data with two keys instead of the mouse.

Intermediate

38 exercises
  1. SUMIF: a sum range on another column Paid Total one column while testing a different column for the condition.
  2. Add only the positives Paid Total the positive and negative rows separately instead of netting a column.
  3. SUM across several blocks Paid Add several separate blocks, or one block with gaps, in a single SUM call.
  4. LARGE and SMALL: the nth highest Paid Pick the nth value in an unsorted list instead of sorting it to read one number.
  5. MEDIAN: the middle value Paid Summarise a list with a middle value that an outlier cannot drag.
  6. MODE: the most common value Paid Find the value that repeats most often, which is neither the biggest nor the middle.
  7. Rank a list without sorting it Paid Put a place number on every row of an unsorted list, in either direction.
  8. Average functions summary Paid Join two summary functions into one answer, reading the nesting inside-out.
  9. TRUE and FALSE: what a comparison returns Paid Read a comparison as a logical value that other formulas can act on.
  10. SUMIF: total one region Total only the rows that match a region.
  11. ROUND: money to two decimals Round a marked-up total once, and know that the stored value changed.
  12. ROUNDUP and ROUNDDOWN: one direction only Paid Choose the direction on purpose, so the count follows the job.
  13. MROUND and FLOOR: round to a multiple Paid Round to a pack size or a price step instead of to a number of decimals.
  14. VLOOKUP: exact match Pull a salary out of a table by its ID, with the exact-match flag written out.
  15. VLOOKUP: approximate match needs a sorted column Paid Read a rate off a band table with approximate match, on a column that rises.
  16. HLOOKUP: look across a row Paid Pull a price out of a row table by naming the row it sits on.
  17. XLOOKUP: exact match, no column counting Paid Return a value from a column anywhere in the table, with no column number to count.
  18. XLOOKUP with a fallback when nothing matches Paid Answer a missing SKU with a sentence instead of an error.
  19. VLOOKUP and XLOOKUP on the same table Paid Read a price with either lookup and know which argument does which job.
  20. Lookup functions summary Paid Pick the lookup that can return a column to the left of its key.
  21. MATCH: find a value's position Paid Find where a value sits in a list, counting the first cell as 1.
  22. INDEX: a value at a row and column Paid Read any single cell of a grid from a row number and a column number.
  23. INDEX MATCH: look left of the key Paid Return a value from the left of the key with INDEX and MATCH.
  24. Two-way INDEX MATCH Paid Read one cell out of a grid by matching its row and column labels.
  25. INDEX and MATCH summary Paid Read a rate from a sorted band table with INDEX and an approximate MATCH.
  26. MID: characters from the middle Paid Read a fixed slice from the middle of a code, from a position you worked out.
  27. FIND and SEARCH: case-sensitive or not Paid Choose FIND for a case-sensitive position, SEARCH when case should not matter.
  28. Wildcards in text criteria Paid Write a wildcard criterion in COUNTIF and reuse the same pattern in SUMIF.
  29. Text search summary Paid Combine FIND with MID for a slice, and a wildcard criterion for a filtered average.
  30. CONCAT and the & operator Join two cells into one string, with the space passed in as its own piece.
  31. TEXTJOIN: join a range and skip blanks Paid Join a column into one line and decide what happens to the blanks.
  32. UPPER, LOWER and PROPER Paid Normalise capitalisation with UPPER, LOWER and PROPER, and know which one a code needs.
  33. SUBSTITUTE: replace one piece of text Paid Replace a separator everywhere, then name a single occurrence with the fourth argument.
  34. IFERROR: catch the error, show your text Paid Replace a failed lookup with words you chose, and know which faults those words will hide.
  35. TODAY and NOW: the volatile clock Paid Tell today's date from this moment, and read the clock out of a date number.
  36. DATE: build a date from parts Paid Build a real date from three numbers and let DATE roll the overflow.
  37. DAYS and simple date maths Paid Measure the distance between two dates in days, then treat the answer as the number it is.
  38. Date functions summary Paid Combine today's date, a distance and a month edge in formulas that still work tomorrow.

Advanced

39 exercises
  1. SUMIFS: two conditions at once Total the rows that meet two conditions at once instead of filtering the table first.
  2. Debug a total that skips a row Paid Find the row a broken total skips and cover it without double-counting a subtotal.
  3. Running total down a column Paid Build a running total that can be filled down without editing each row.
  4. Sum functions summary Paid Combine SUM and SUMIF to total a column, a subset of it, and the remainder.
  5. COUNTIFS: several conditions Paid Count the rows that satisfy two conditions at once instead of filtering the table first.
  6. COUNTIF with wildcards Paid Count partial matches on text instead of listing every exact value that qualifies.
  7. Count functions summary Paid Tell counting values from counting rows, then use both together to find gaps.
  8. AND, OR and NOT: combining tests Paid Combine several tests into one answer, and choose AND or OR to match the question.
  9. IFS: more than two outcomes Paid Read a score into one of several bands with a single ordered function.
  10. SWITCH: match a list of values Paid Translate a short fixed list of codes into labels in one formula.
  11. ISBLANK, ISNUMBER, ISTEXT: reading a cell's type Paid Tell an empty cell from a text cell and a number, and read the answer as a logical value.
  12. Logical functions: putting the pieces together Paid Combine a logical test, a combined test and a rounded calculation in one formula.
  13. VLOOKUP with a wildcard Paid Match a name you half know, and keep the pattern in a cell so it can change.
  14. Two-way lookup with VLOOKUP and MATCH Paid Return one cell of a grid from a row key and a column key.
  15. XLOOKUP: search from the bottom Paid Return the latest entry for a repeated key without sorting the log first.
  16. INDEX + MATCH: lookup either way Paid Return a value from any column, whichever side of the key it sits on.
  17. LEFT + FIND: pull out a prefix Paid Pull a prefix of any length out of a code with LEFT and FIND.
  18. TEXTBEFORE and TEXTAFTER: split on a delimiter Paid Split a code at its delimiter, taking either the part before or the part after.
  19. Capstone: North gross margin Paid Total by region, divide to a margin percentage, and leave a blank where revenue is missing.
  20. Sales summary end to end Paid Total a region with SUMIFS, then answer which region leads with one IF around two totals.
  21. Lookup dashboard from three tables Paid Read a keyed value with INDEX and MATCH, then chain the pair to reach a third table.
  22. Clean up an imported list Paid Tidy spacing and case with TRIM and PROPER, then build a lower-case key from the same text.
  23. Budget versus actual with a variance flag Paid Size a variance as a share of budget, then flag it with one threshold both ways.
  24. Formula audit: fix five broken sheets Paid Repair five broken cells, one per error code, by choosing the test each code implies.
  25. IFNA: catch only a missing match Paid Replace a missing lookup match and leave every other error visible.
  26. Read #N/A, #VALUE! and #REF! Paid Tell a missing match apart from a broken reference, and print that difference as words.
  27. Error handling: which net catches what Paid Choose the wide net or the narrow one on purpose, and wrap the whole calculation in it.
  28. DATEDIF: days, months or years between Paid Ask for the gap between two dates in whole years, months or days, with the leftovers when needed.
  29. SUMPRODUCT: multiply the pair, then add them up Paid Value a table and take a weighted average from the same two columns.
  30. SUBTOTAL: one function number, six answers Paid Read the function number first, so one range can be added or searched for its largest value.
  31. AGGREGATE: a total that says what to ignore Paid Write a three-argument aggregate and know which cells the options number leaves out.
  32. Totals that multiply, skip and reconcile Paid Reconcile two routes to the same total and read the ratio as a pass or a fail.
  33. UNIQUE: one row per distinct value Paid Count the distinct couriers in a column without retyping the list.
  34. FILTER: the rows that meet a condition Paid Add the rows that meet a condition without filtering the sheet first.
  35. SORT and spilled ranges Paid Rank a table without touching it, then read one cell out of the ranked block.
  36. A commission sheet that updates itself Paid Read a rate from a banded table, then multiply it by the sales figure.
  37. A headcount and payroll summary Paid Total a payroll across two columns, then divide it by the headcount.
  38. An inventory reorder report Paid Floor a row's shortfall at zero, then net the whole sheet the same way.
  39. A monthly close checklist in formulas Paid Flag a reconciliation from its variance, then count the rows still out.