For an Excel test or skills check for an office, admin or analyst job.
Excel test prep
Practice the tasks that come up in many Excel tests: IF, SUMIF and SUMIFS, lookups, cleaning up a list, pivot tables and highlighting.
Tests vary by employer. These tasks come up again and again. The first lesson is free and needs no sign-up.
Do this path on your computer.
Its lessons run in your browser on a laptop or desktop, and every step is checked as you go. Send yourself the link. Nothing is saved until you sign in.
The lessons, in order
Each one takes about 10 minutes. Four are free; the rest are in the full course.
- 1
Why your formula broke when you copied it
Free · Beginner · 7 min
Lock a cell with $ so a formula keeps working when you copy it down.
Start the lesson - 2
IF: make a decision in a cell
In the full course. Beginner · 11 min
Write an IF that shows one result or another depending on a test, with text, numbers or an empty cell as the result.
See the lesson - 3
IF with AND and OR
In the full course. Confident · 10 min
Make IF check two or more conditions at once, with AND when all must be true and OR when one is enough.
See the lesson - 4
SUMIF and COUNTIF
In the full course. Confident · 12 min
Total and count only the rows that match a condition, and copy the formula down without breaking its ranges.
See the lesson - 5
SUMIFS and COUNTIFS with several conditions
In the full course. Confident · 11 min
Total and count the rows that pass several conditions at once, including a date range, with SUMIFS and COUNTIFS.
See the lesson - 6
Look up a price with VLOOKUP and XLOOKUP
Free · Confident · 11 min
Bring each order's price over from a price list with VLOOKUP or XLOOKUP, and avoid the missing-FALSE trap.
Start the lesson - 7
Why VLOOKUP returns #N/A
In the full course. Confident · 12 min
Tell which of the four causes is behind each #N/A from VLOOKUP, fix it, and hide only the misses that are real.
See the lesson - 8
INDEX and MATCH
In the full course. Confident · 10 min
Look up a value from any column, even one to the left of the column you search, with INDEX and MATCH.
See the lesson - 9
XLOOKUP, the modern lookup
In the full course. Confident · 10 min
Look up a value in any column and return any other column with XLOOKUP, including columns to the left, with your own result when nothing is found.
See the lesson - 10
Hide errors with IFERROR and IFNA
In the full course. Confident · 11 min
Replace expected errors such as #DIV/0! with a clear value using IFERROR, and use IFNA for lookups so real mistakes still show.
See the lesson - 11
Remove extra spaces with TRIM
In the full course. Beginner · 9 min
Find the invisible spaces that break lookups and hide duplicates, and clean them with TRIM in a helper column.
See the lesson - 12
Remove duplicates
Free · Confident · 9 min
Delete repeated rows from a merged list, and choose which columns decide what counts as a duplicate.
Start the lesson - 13
Your first pivot table
Free · Confident · 10 min
Turn 200 rows of sales into totals by region, read the answer, and re-ask the question by rep or product.
Start the lesson - 14
Change sum to count, average or percent
In the full course. Confident · 9 min
Make a pivot table count orders, average them and show each group's share of the total, instead of only adding things up.
See the lesson - 15
Highlight cells above a value
In the full course. Confident · 8 min
Color every amount above a limit automatically, so the cells that need attention stand out and keep standing out when the numbers change.
See the lesson
Other paths in the Excel course
Excel basics in a weekend
For starting from zero, or only typing numbers into cells today.
14 lessons · about 2 h · 4 free · Beginner to Confident
See the pathLookups without the #N/A
For matching lists by hand today: prices, product names, staff details, commission tiers.
9 lessons · about 1 h 30 min · 2 free · Beginner to Advanced
See the pathReports that don't break
For a weekly or monthly report other people rely on.
11 lessons · about 1 h 45 min · 2 free · Beginner to Confident
See the pathExcel for small business
For quotes, stock and money kept in a spreadsheet.
12 lessons · about 2 h 15 min · 3 free · Confident
See the pathExcel for finance and accounting
For bookkeeping, accounts or finance support work, or accounting study.
11 lessons · about 1 h 55 min · 1 free · Beginner to Advanced
See the pathGet the full course
Every lesson in the Excel course, with new lessons added as they're written.
- All 217 Excel lessons open today, and every new one as it's written.
- The Roadmap, Skills and all eight paths.
- A cheat sheet for every lesson you finish.
- At checkout, add the Google Sheets course for US$49.