Start for free

An Excel test for a job application? Here's how to pass

If the job ad says "good knowledge of Excel", expect a test. Practise below against the clock: afterwards you see for each question where you dropped points.

Try this task

Looking things up comes up a lot in an Excel test. Type the formula yourself, and if you make a mistake you read straight away what went wrong.

Try it yourself

What does the item with the code in E2 cost? Look it up with VLOOKUP in F2 and press Enter. Feel free to make a mistake, for example by leaving out the last argument.

F2
A
B
C
D
E
F
G
1
Code
Item
Price
Code to find
Price
2
P-310
Office chair
189
P-204
3
P-118
Monitor
219
4
P-502
Keyboard
59
5
P-067
Laptop bag
35
6
P-204
Desk lamp
45
7
8
Ready

Want more practice? The full lesson on VLOOKUP is in the Beginner module. Or start with the free placement test.

The tests

What to practise

No two tests are the same, but with these topics you're usually well prepared:

Calculating with cells

VAT, percentages and locking a reference.

Totals per group

How much per region, per month or per customer?

Decisions with IF

Who gets a bonus, who still has to pay?

Looking something up

The price for a code, the name for a number.

PivotTables

Turning a long list into a summary.

Sorting and filtering

Getting the right rows in front of you quickly.

What can you expect?

In an Excel test you usually get a worksheet with data and a series of tasks, often against the clock. An employer looks not just at the result, but also at how you got there: are there formulas, or typed-in numbers? For admin roles it's often about totals, sorting and filtering. For finance and analytical roles, lookup functions and PivotTables are added to that. If you're not sure, ask beforehand roughly what will be asked and whether you'll work on your own device.

Five sample questions

Try these yourself first on the table below. Then click a question to see the formula.

NameRegionRevenue
AnnaLeeds1200
BenYork340
ChloeLeeds410
DanManchester980
EmmaYork75

The names are in column A, the regions in column B and the revenue in column C. The first name is in row 2.

What is the total revenue for Leeds?

=SUMIF(B2:B6,"Leeds",C2:C6) gives 1610. You search the regions and add up only the revenue on the rows for Leeds.

How many people have at least 400 in revenue?

=COUNTIF(C2:C6,">=400") gives 3. A comparison always goes in quotation marks.

Does Anna get a bonus if the threshold is 500?

=IF(C2>=500,"Bonus","No bonus") gives Bonus. IF chooses between two results. If you fill the formula down, everyone gets an answer.

Which region does Dan work in?

=VLOOKUP("Dan",A2:C6,2,FALSE) gives Manchester. The region is in the second column of the table. FALSE makes it an exact match.

What is the average revenue in York?

=AVERAGEIF(B2:B6,"York",C2:C6) gives 207.5. Built the same way as SUMIF, but with an average.

How to prepare

  1. Take the free placement test, so you know where you stand.
  2. First practise the topics above that you can't do yet. Start with what the job ad asks for.
  3. Then take a timed test, so you get a feel for the pace.
  4. See which parts cost you points, and practise those again.

On the day

Frequently asked questions

What does a test like this look like?

Every employer decides that for themselves. Often you get a file with company data and half an hour to answer questions about it: making totals, looking something up, flagging an exception.

How long should I practise?

That depends on where you stand now and on the job. Start with the basics test: after fifteen minutes you know where to spend your time.

Which version of Excel will I get?

You usually don't know beforehand. So learn VLOOKUP too, even if you prefer XLOOKUP yourself: the older function works everywhere.

Can I look things up during the test?

Ask beforehand, because it varies. Better not to count on it.