Excel Lessons

Excel practice, written down

Exercises with answers and the reasoning behind them. Every one links to the lesson where you type it into a grid and get marked.

  1. Excel practice exercises for beginners, with answers

    Twelve Excel exercises on references, SUM, AVERAGE, COUNT, absolute references, IF and ROUND, each with the answer and why it works.

    · 10 min read

  2. VLOOKUP practice exercises: seven worked examples

    Seven VLOOKUP exercises from exact to approximate match, with the answers, the four causes of #N/A, and when to reach for XLOOKUP instead.

    · 9 min read

  3. The Excel keyboard shortcuts to practise first

    Nine shortcuts that do most of the work in a spreadsheet, in the order to learn them, with a drill for each, then the five that come next.

    · 7 min read

  4. #N/A in Excel: what it means and the six causes

    #N/A is a lookup saying it found nothing. The six things that cause it, how to test for each, and why IFNA is the right wrapper and IFERROR is not.

    · 8 min read

  5. #VALUE! in Excel: the wrong kind of thing in the formula

    Why plus fails where SUM works, the non-breaking space that TRIM cannot remove, and how to find the cause of a #VALUE! in under a minute.

    · 7 min read

  6. #REF! in Excel: what breaks a reference and how to fix it

    #REF! is damage, not a data problem. The three things that cause it, why undo is the only real fix, and how to survive a deleted column.

    · 7 min read

  7. XLOOKUP vs VLOOKUP: which to use, and when it matters

    Three things only XLOOKUP does, the one reason not to use it, and why INDEX and MATCH is still the right answer for a file you send to other people.

    · 8 min read

  8. INDEX and MATCH: learn the halves before the formula

    MATCH finds the position, INDEX returns what is there. Each half on its own first, then nested, then the two-way lookup XLOOKUP does not do as neatly.

    · 8 min read

  9. SUMIF and SUMIFS practice, with answers

    Eight exercises on one table, with answers: one condition, two, over a cell value, a date range and wildcards, plus the argument order people get wrong.

    · 9 min read

  10. Excel dates are numbers: why yours turned into 45658

    A date is the number of days since 1900. That one fact explains the number in your cell, the 03/01/1900 subtraction, and every text date from an export.

    · 9 min read

  11. Excel interview questions, answered by someone who uses it

    The definition is not what is being tested. Twelve questions with the short answer and the sentence that shows you have had it go wrong on real data.

    · 10 min read

  12. How to write an Excel formula, step by step

    Click the cell, type =, add the function and the range, press Enter. Each step with one small sheet, then IF, copying down, and five first mistakes.

    · 6 min read

  13. Instant feedback vs a quiz at the end

    Being told at once stops you practising a mistake. A score at the end is for measuring. Why lessons check each answer on Enter and the skills test waits.

    · 5 min read

  14. #DIV/0! in Excel: why it appears and how to fix it

    #DIV/0! means a formula divided by zero or by an empty cell. Where it comes from, how to test for it with IF, and when IFERROR hides a real problem.

    · 6 min read

  15. Absolute vs relative references: when to use $ in Excel

    A relative reference moves when you copy a formula; an absolute one stays. Worked examples of A1, $A$1, $A1 and A$1, and the F4 key that switches them.

    · 7 min read

  16. IF function practice exercises, with answers

    Ten IF exercises on one sheet, with answers: a pass mark, text results, AND and OR inside IF, a nested IF for grades, and the mistake each one catches.

    · 9 min read

  17. COUNTIF and COUNTIFS practice, with answers

    Eight counting exercises on one table, with answers: text, numbers, a comparison, dates, wildcards and two conditions at once, plus the quote marks rule.

    · 8 min read

  18. IFERROR vs IFNA: which to use, and when not to hide errors

    IFERROR hides every error; IFNA hides only #N/A. Why the difference matters for lookups, which one to choose, and the errors you should leave visible.

    · 6 min read

  19. The FILTER function in Excel: practice with answers

    FILTER returns every row that matches a condition, and updates by itself. Six exercises with answers, from one condition to two, then SORT and UNIQUE.

    · 8 min read

  20. Percentages in Excel: share of a total, change and increase

    Three percentage formulas cover most work: part of a total, change from old to new, and adding a percentage. Each with an example and the usual mistake.

    · 6 min read

  21. Split a full name into first and last name in Excel

    Split a full name into two columns with TEXTBEFORE and TEXTAFTER, or with LEFT, FIND and MID in older Excel. What to do with middle names.

    · 7 min read

  22. Clean up messy text in Excel: TRIM, PROPER and SUBSTITUTE

    Extra spaces and stray characters break lookups and counts; wrong capitals look careless. How TRIM, PROPER, UPPER, LOWER and SUBSTITUTE fix them.

    · 7 min read

  23. Working days in Excel: NETWORKDAYS and WORKDAY explained

    Count the working days between two dates, or find a deadline 10 working days away. NETWORKDAYS and WORKDAY with worked examples, holidays and weekends.

    · 6 min read

  24. Rounding in Excel: ROUND, ROUNDUP and ROUNDDOWN

    A number formatted to two decimals is not rounded; the full value is still there. When to round with a function, and how ROUND, ROUNDUP, ROUNDDOWN differ.

    · 6 min read

  25. Compare two columns in Excel: find matches and differences

    Check two lists row by row, or find which names in one list are missing from the other. Formulas with =, EXACT, COUNTIF and XLOOKUP, with a worked example.

    · 7 min read

  26. Calculate age in Excel from a date of birth

    Age in whole years is =DATEDIF(birth,TODAY(),"Y"). Why subtracting years gets birthdays wrong, how to show years and months, and length of service.

    · 6 min read

  27. COUNT, COUNTA and COUNTBLANK: which one counts what

    COUNT counts numbers, COUNTA counts anything that is not empty, COUNTBLANK counts empty cells. Where each one surprises you, with one small sheet.

    · 5 min read

  28. Numbers stored as text in Excel: why SUM ignores them

    A number stored as text looks right but SUM skips it and lookups miss it. How to spot it, why it happens after an import, and four ways to fix it.

    · 6 min read

  29. Extract text in Excel with LEFT, RIGHT and MID

    Take the first, last or middle characters out of a code, an ID or a date written as text. LEFT, RIGHT, MID and LEN with examples you can check.

    · 6 min read

  30. Combine text in Excel: &, CONCAT and TEXTJOIN

    Join a first and last name, build a full address, or list every item with commas. When to use &, CONCAT or TEXTJOIN, and how to keep the spaces right.

    · 6 min read

  31. Get a list of unique values in Excel, and count them

    UNIQUE returns each value once and updates by itself. How to list distinct values, count them, sort them, and what to use in older versions of Excel.

    · 6 min read

  32. Average vs median in Excel: which number to trust

    One big value pulls the average up; the median does not move. When each one tells the truth about your data, with salaries, delivery times and MODE.

    · 6 min read

  33. Running total in Excel: a cumulative sum that fills down

    A running total adds each row to the rows above it. The one formula with a half-locked range, why it breaks when you sort, and a running count.

    · 5 min read

  34. Find the top 3 values in Excel with LARGE and SMALL

    MAX gives the largest value; LARGE gives the second and third. How to list the top 3 or bottom 3, show who they belong to, and handle ties.

    · 6 min read

  35. Lookup with two criteria in Excel

    Find a price by product and size, or a score by name and date. Three ways to look up on two conditions: a helper column, XLOOKUP, and INDEX with MATCH.

    · 7 min read

  36. Approximate match lookup: tax bands, grades and discounts

    A lookup that finds the band a number falls into, not an exact match. How approximate match works in VLOOKUP and XLOOKUP, and the sort rule it needs.

    · 7 min read

  37. Replace a nested IF with IFS, SWITCH or a lookup table

    A nested IF with five levels is hard to read and easy to break. When IFS, SWITCH or a small lookup table does the same job better, with one example each.

    · 7 min read

  38. FIND vs SEARCH in Excel: the two differences

    FIND and SEARCH both give the position of text in a cell. FIND cares about capitals; SEARCH allows wildcards. And how to check if a cell contains a word.

    · 5 min read

  39. Check if a cell is blank, a number or text in Excel

    ISBLANK, ISNUMBER and ISTEXT answer TRUE or FALSE about a cell. Why a cell that looks empty is not always blank, and how to use each test inside IF.

    · 5 min read

  40. DATE, YEAR, MONTH and DAY: build and split dates in Excel

    Take a date apart into year, month and day, or build a real date from three numbers. Fix dates imported as text and find the first day of any month.

    · 6 min read

  41. Last day of the month and day of the week in Excel

    EOMONTH gives the last day of any month; WEEKDAY gives the day of the week as a number. Month-end reports, due dates and weekend checks, with examples.

    · 5 min read

  42. SEQUENCE in Excel: numbered lists and date series

    One SEQUENCE formula writes 1 to 100, a list of dates, or a grid of numbers. How its four parts work, with examples you can copy and check.

    · 5 min read

  43. SUBTOTAL vs SUM: totals that ignore filtered rows

    SUM adds every row, even ones a filter hides. SUBTOTAL adds only what you can see. The numbers 9 and 109, and why SUBTOTAL skips other subtotals.

    · 5 min read

  44. MAXIFS and MINIFS: highest and lowest with a condition

    The biggest order from one customer, or the earliest date in one region. MAXIFS and MINIFS with one and two conditions, and what happens with no match.

    · 5 min read

  45. SUMPRODUCT explained: three examples you will use

    SUMPRODUCT multiplies two lists and adds the results. A total of price times quantity, a weighted average, and a count with conditions, step by step.

    · 6 min read