Excel test for a job interview: what to expect and how to practise
Most Excel tests for office, admin, finance and HR jobs check the same handful of skills: adding up and averaging, IF, lookups such as VLOOKUP or XLOOKUP, SUMIFS and COUNTIFS, $ references, percentages, sorting and filtering, and sometimes a pivot table. Practise those on realistic data and you'll be ready for most of them.
How the tests work
- Tasks in a real spreadsheet. You get a workbook and a list of jobs: "total the sales for the North region", "look up each employee's manager". This is the most common kind for admin and finance roles.
- Online tests with a pretend spreadsheet. Short questions, each on a small sheet, often timed at 20 to 40 minutes.
- Multiple choice. "Which formula gives…?" questions. Knowing what each part of a formula does matters more than remembering menus.
Ask the recruiter which version of Excel the test uses. If it's older than Excel 2021, XLOOKUP may not be there, so know VLOOKUP as well.
What usually comes up
| Skill | Typical question | Practise it |
|---|---|---|
| SUM, AVERAGE, MAX, MIN, COUNT | Total and average of a column | Practice |
| $ references | Copy a formula down that uses one rate or target cell | Practice · guide |
| Percentages | Share of a total, percentage change | Practice · guide |
| IF | Show "Over budget" or "OK" on each row | Practice · guide |
| COUNTIF and COUNTIFS | How many orders are from London? | Practice · guide |
| SUMIF and SUMIFS | Total sales for one region, or one region in one month | Practice · guide |
| VLOOKUP or XLOOKUP | Bring each customer's town in from another table | Practice · guide |
| Text | Join first name and surname; tidy pasted data | Practice · guide |
| Dates | Days between two dates, working days, age | Practice · guide |
| Sorting, filtering, pivot tables | Show the top 5; summarise sales by region | Sorting and filtering lesson · Pivot tables lesson |
A typical question, worked through
"The sheet lists orders, with the region in column B and the value in column C. In G2, total the orders for the region in G1."
- You want to add up only some rows, the ones for one region: that's SUMIF.
- Where to look for the region: B2:B9. What to look for: G1. What to add up: C2:C9.
=SUMIF(B2:B9,G1,C2:C9). For North that's £120 + £95 + £210 = £425.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Value | Region | North | ||
| 2 | 1001 | North | £120.00 | Total | £425.00 | ||
| 3 | 1002 | South | £80.00 | ||||
| 4 | 1003 | North | £95.00 | ||||
| 5 | 1004 | East | £150.00 | ||||
| 6 | 1005 | South | £60.00 | ||||
| 7 | 1006 | North | £210.00 | ||||
| 8 | 1007 | East | £45.00 | ||||
| 9 | 1008 | South | £130.00 |
Pointing at G1 rather than typing "North" into the formula is what testers look for: the answer updates when the question changes.
On the day
- Read every question first, and do the ones you're sure of before the hard ones.
- Check a few answers by eye. If the total for one region is bigger than the grand total, something's wrong.
- Use $ signs whenever you copy a formula that points at one fixed cell.
- If you're stuck on a lookup, check for #N/A causes first: spaces and numbers stored as text.
Try it now
A typical test question: in G2, total the orders for the region in G1. Point at G1 rather than typing the region into the formula.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Value | Region | South | ||
| 2 | 1001 | North | £120.00 | Total | |||
| 3 | 1002 | South | £80.00 | ||||
| 4 | 1003 | North | £95.00 | ||||
| 5 | 1004 | East | £150.00 | ||||
| 6 | 1005 | South | £60.00 | ||||
| 7 | 1006 | North | £210.00 | ||||
| 8 | 1007 | East | £45.00 | ||||
| 9 | 1008 | South | £130.00 |
For a full run-through, the practice pages cover every skill in the table above, each marked as you type.