Hands-On Excel Course

The Excel course, in order

14 modules, from moving around the grid to cases from real jobs. 63 of the 120 lessons are ready to practise in your browser. In each formula lesson you do the task, then the same task on new data with no hints. The first 5 are free and the rest come with Pro. Lessons not built yet are listed so you can see where the path goes.

Getting ready for an Excel test for a job? A timed practice test shows which modules to start with.

14 modules120 lessons63 ready nowAbout 3 minutes each

0 of 63 ready lessons solved

Start with 1.5: Select to the end in one move.

Start lesson 1.5

Progress is saved in this browser. Every task also has its own free page, and the shortcut drills are free too.

1. Find your way around the grid

Move, select and read the sheet before you write a single formula.

8 lessons1 ready

  1. 1.1Every cell has an addressCell addresses, starter. Not available yet.
  2. 1.2Select a rangeDrag, Shift + click, starter. Not available yet.
  3. 1.3Move without the mouseArrow keys, Enter, Tab, starter. Not available yet.
  4. 1.4Jump to the edge of the dataCtrl + arrow, starter. Not available yet.
  5. 1.5Select to the end in one moveCtrl + Shift + arrow, core
  6. 1.6Whole rows and columnsShift + Space, Ctrl + Space, starter. Not available yet.
  7. 1.7Select everythingCtrl + A, starter. Not available yet.
  8. 1.8Let the status bar do the mathStatus bar: Sum, Count, Average, starter. Not available yet.

2. First formulas

Totals, averages, counts and percentages: the formulas most sheets are made of.

9 lessons6 ready

  1. 2.1Your first formula= + - * /, starter. Not available yet.
  2. 2.2Add it upSUM, starter
  3. 2.3AutoSum in one keystrokeAutoSum, starter. Not available yet.
  4. 2.4Count what's thereCOUNT, COUNTA, COUNTBLANK, starter
  5. 2.5Find the averageAVERAGE, starter
  6. 2.6Smallest and largestMIN, MAX, starter
  7. 2.7Brackets change the answerOrder of operations, starter. Not available yet.
  8. 2.8Percent of a totalPercentages, starter Pro
  9. 2.9Up or down by how much?Percent change, core Pro

3. Copy a formula, not a number

Write a formula once and fill it, with references that stay put when they should.

5 lessons3 ready

  1. 3.1Write once, fill downRelative references, starter Pro
  2. 3.2Fill rightCtrl + R, starter. Not available yet.
  3. 3.3Lock it with $Absolute references, core Pro
  4. 3.4Lock just the row or the columnMixed references, advanced Pro
  5. 3.5Mistake hunt: the totals don't add upFixing SUM, core. Not available yet.

4. Make it readable

Number formats, rounding, and the difference between what a cell shows and what it holds.

4 lessons2 ready

  1. 4.1Make the numbers readableNumber formats, starter Pro
  2. 4.2What you see vs what's storedROUND, core Pro
  3. 4.3Bold, italic, underlineCtrl + B, I, U, starter. Not available yet.
  4. 4.4Custom number formatsCustom formats, advanced. Not available yet.

5. Make decisions

IF and its relatives: let the sheet decide pass or fail, paid or not.

11 lessons5 ready

  1. 5.1TRUE or FALSE?Comparisons, starter. Not available yet.
  2. 5.2Pass or fail?IF, core Pro
  3. 5.3Both or either?AND, OR, core Pro
  4. extraExactly one of the twoXOR, core Pro
  5. 5.4Grade bands with nested IFNested IF, core Pro
  6. 5.5Many conditions, no nestingIFS, core. Not available yet.
  7. 5.6Match a code with SWITCHSWITCH, core. Not available yet.
  8. 5.7IF with AND and ORIF + AND, OR, core. Not available yet.
  9. 5.8What's in this cell?ISNUMBER, ISTEXT, ISBLANK, core. Not available yet.
  10. 5.9Catch the error, not the bugIFERROR, core Pro
  11. 5.10Mistake hunt: the commission sheetFixing IF, core. Not available yet.

6. Totals with conditions

Count and add up only the rows that match.

10 lessons7 ready

  1. 6.1Count the ones that matchCOUNTIF, core Pro
  2. 6.2Add up the ones that matchSUMIF, core Pro
  3. 6.3Use a cell as the conditionCriteria in a cell, core Pro
  4. 6.4Partial matches with * and ?SUMIF, COUNTIF with wildcards, core Pro
  5. 6.5Count with two conditionsCOUNTIFS, core Pro
  6. 6.6Add up with two conditionsSUMIFS, core Pro
  7. 6.7Average with conditionsAVERAGEIF, AVERAGEIFS, core. Not available yet.
  8. 6.8Count the blanksCOUNTBLANK, COUNTA, core. Not available yet.
  9. extraWhich invoices are missing?Compare two lists, core Pro
  10. 6.9Mistake hunt: the regional reportFixing SUMIF, core. Not available yet.

7. Find a value

VLOOKUP, XLOOKUP and INDEX/MATCH, and why a lookup comes back #N/A.

12 lessons7 ready

  1. 7.1Look it upVLOOKUP, core Pro
  2. 7.2Find the right bracketVLOOKUP approximate match, core Pro
  3. 7.3Fill a lookup downVLOOKUP + $, core. Not available yet.
  4. 7.4Look across a rowHLOOKUP, core. Not available yet.
  5. 7.5XLOOKUP, the modern wayXLOOKUP, core Pro
  6. 7.6XLOOKUP with wildcardsXLOOKUP match_mode, advanced. Not available yet.
  7. 7.7Where is it in the list?MATCH, core Pro
  8. 7.8Give me the nth oneINDEX, core. Not available yet.
  9. 7.9INDEX and MATCH togetherINDEX + MATCH, advanced Pro
  10. 7.10Row and column lookupINDEX + MATCH + MATCH, advanced. Not available yet.
  11. 7.11Mistake hunt: #N/A everywhereFixing #N/A, core Pro
  12. extraReconcile the ledger with the bankXLOOKUP or VLOOKUP + IFNA, core Pro

8. Clean up text

Join, split, trim and fix the capitals in messy text.

11 lessons5 ready

  1. 8.1Join text together&, CONCAT, PROPER, core Pro
  2. 8.2Join a list with commasTEXTJOIN, core. Not available yet.
  3. 8.3Fix the capitalsUPPER, LOWER, PROPER, starter. Not available yet.
  4. 8.4Stray spaces that break a countTRIM, core Pro
  5. 8.5Flag the bad entriesTRIM, LEN, EXACT, core Pro
  6. 8.6First and last charactersLEFT, RIGHT, starter. Not available yet.
  7. 8.7Pull from the middleMID, core. Not available yet.
  8. 8.8FIND or SEARCH?FIND, SEARCH, core. Not available yet.
  9. 8.9Split first and last namesLEFT, MID + FIND, core Pro
  10. 8.10Clean a bank descriptionTEXTBEFORE, TEXTAFTER, PROPER, advanced Pro
  11. 8.11Swap one word for anotherSUBSTITUTE, core. Not available yet.

9. Numbers and statistics

Median, mode, the top three, and rounding the way the task needs.

8 lessons2 ready

  1. 9.1The typical valueMEDIAN, core Pro
  2. extraGrowth per year (CAGR)CAGR with ^, advanced Pro
  3. 9.2The most common valueMODE, core. Not available yet.
  4. 9.3Top 3 and bottom 3LARGE, SMALL, core. Not available yet.
  5. 9.4Always round up (or down)ROUNDUP, ROUNDDOWN, core. Not available yet.
  6. 9.5Round to the nearest 5, 15, 0.25CEILING, FLOOR, core. Not available yet.
  7. 9.6Whole parts and leftoversINT, MOD, advanced. Not available yet.
  8. 9.7Round to odd or evenEVEN, ODD, core. Not available yet.

10. Dates

A date is a number in disguise: count days, ages and working days.

9 lessons5 ready

  1. 10.1How many days late?Date arithmetic, core Pro
  2. extraTurn text into real datesDATEVALUE, core Pro
  3. 10.2How many days are left?TODAY, starter. Not available yet.
  4. 10.3Build a date from partsDATE, YEAR, MONTH, DAY, core. Not available yet.
  5. 10.4Days overdue and aging bandsDate difference, aging bands, core Pro
  6. 10.5Age and years of serviceDATEDIF, core. Not available yet.
  7. 10.6Month ends and working daysEOMONTH, NETWORKDAYS, advanced Pro
  8. extraRenewal datesEDATE, core Pro
  9. 10.7Totals between two datesSUMIFS with dates, advanced. Not available yet.

11. Work with data

Sort, filter, paste values, highlight with rules, and work across sheets.

10 lessons7 ready

  1. 11.1Sort without breaking rowsSort, starter Pro
  2. 11.2Sort by two columnsMulti-level sort, core. Not available yet.
  3. 11.3Copy and paste a formulaCtrl+C, Ctrl+V, starter Pro
  4. 11.4Paste values onlyPaste Special → Values, core Pro
  5. 11.5Filter to what mattersFilter, starter Pro
  6. 11.6Insert and delete rows fastInsert and delete rows, core. Not available yet.
  7. 11.7Highlight what's over targetConditional formatting, core Pro
  8. 11.8Spot the duplicatesDuplicate Values rule, core Pro
  9. 11.9Work across sheetsSheet tabs, starter. Not available yet.
  10. 11.10Use a rate from another sheetReferences to other sheets, core Pro

12. Dynamic arrays and pivot tables

UNIQUE, FILTER and SORT, and a first pivot table.

5 lessons2 ready

  1. 12.1One of each, then the totalsUNIQUE, core Pro
  2. 12.2Rows that matchFILTER, advanced. Not available yet.
  3. 12.3Sorted, unique list in one formulaSORT + UNIQUE, advanced. Not available yet.
  4. 12.4Your first pivot tablePivot tables, core Pro
  5. 12.5Slice the pivotPivot filters, advanced. Not available yet.

13. Error clinic

Each lesson starts with a broken sheet: find the cell, then fix it.

7 lessons2 ready

  1. 13.1Fix #DIV/0!#DIV/0!, core. Not available yet.
  2. 13.2Fix #NAME?#NAME?, starter. Not available yet.
  3. 13.3Fix #VALUE!#VALUE!, core. Not available yet.
  4. 13.4Fix #REF!#REF!, core. Not available yet.
  5. 13.5Fix a circular referenceCircular references, core. Not available yet.
  6. 13.6Mistake hunt: numbers stored as textNumbers stored as text, core Pro
  7. 13.7Mistake hunt: hard-coded numbersReferences instead of typed numbers, core Pro

14. Real jobs

Short tasks that don't say which tool to use, then longer cases from real work.

11 lessons9 ready

  1. extraHow many late orders?Pick the tool, core Pro
  2. extraSales per repPick the tool, core Pro
  3. extraWhose part is it?Pick the tool, core Pro
  4. extraWho gets the bonus?Pick the tool, core Pro
  5. extraEach region's sharePick the tool, core Pro
  6. extraWhich category went up most?SUMIFS, change, lookup, advanced Pro
  7. extraCase: the supplier reportCase, five steps, advanced Pro
  8. 14.1Case: the monthly budgetCase, five steps, advanced Pro
  9. 14.2Case: the reorder listCase, five steps, advanced Pro
  10. 14.3Case: timesheet and overtimeTime, IF, ROUND, MOD, advanced. Not available yet.
  11. 14.4Case: messy customer listTRIM, PROPER, COUNTIF, UNIQUE, advanced. Not available yet.