How to Pass an Excel Assessment Test for a Job
· 7 min read
An Excel assessment test is a timed check of how well you use spreadsheets, given by employers or recruiters during hiring. Most tests ask you to do a handful of practical tasks on a small table: total and count with conditions, look up values, write IF logic, summarise with a pivot table, and spot a formula that is wrong. If you can do these quickly and check your own answers, you are ready for most of them:
=SUMIFS(sum_range, criteria_range, criteria, ...)
=COUNTIFS(criteria_range, criteria, ...)
=XLOOKUP(lookup_value, lookup_array, return_array)
=IF(condition, value_if_true, value_if_false)Formats differ from employer to employer, so this guide does not describe any particular test. It covers the question types that come up again and again, a short sample test with answers, and a plan to prepare.
What an Excel skills test usually looks like
You will usually meet one of three formats:
- Questions in a browser. Multiple choice ("which formula returns...") or a simulated spreadsheet where you click or type the answer.
- A live spreadsheet task. You get a file and a list of questions, and you type formulas into real cells while the clock runs.
- A take-home case. A larger file and a business question, such as "which region is underperforming and why?", with a short write-up.
Ask the recruiter which format to expect, how long it is, and which version of Excel it uses. That last point matters: XLOOKUP needs Excel 2021, Microsoft 365 or Excel for the web, so on an older version you need VLOOKUP or INDEX MATCH.
Question types by level
Basic
- SUM, AVERAGE, MIN, MAX and COUNT on a range
- Relative vs absolute references (
$), and filling formulas down - Sorting, filtering and simple number formats (percent, currency, two decimals)
Intermediate
- SUMIF, SUMIFS, COUNTIF, COUNTIFS and AVERAGEIF
- VLOOKUP with exact match, XLOOKUP or INDEX MATCH
- IF with AND or OR, and IFERROR or IFNA
- Pivot tables: totals by group, percent of total, grouping dates by month
- Percent change and share of total
Advanced
- Lookups with several criteria, two-way lookups
- Dynamic arrays such as FILTER, UNIQUE and SORT (newer versions only)
- Cleaning messy data: TRIM, text splitting, removing duplicates, fixing numbers stored as text
- Charts that answer a question, and a short written conclusion
Data analyst roles usually test the intermediate level hardest, plus at least one pivot table.
A sample Excel assessment test
Here is a short practice test on one table. Try each question before you read the answer. Allow yourself 10 minutes. You can answer in the sheet itself: click a cell in column H, type your formula and press Enter. H6 already holds the colleague's formula from question 6, and column E is free for question 5.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Status | Amount | Size | Question | Your formula | |
| 2 | 5001 | North | completed | 320 | 1. Total of all amounts | |||
| 3 | 5002 | West | completed | 145 | 2. Completed orders | |||
| 4 | 5003 | West | cancelled | 210 | 3. Completed West revenue | |||
| 5 | 5004 | East | completed | 480 | 4. Average completed order | |||
| 6 | 5005 | South | refunded | 95 | 6. Colleague's West revenue | 615 | ||
| 7 | 5006 | West | completed | 260 | ||||
| 8 | 5007 | North | completed | 175 |
1. What is the total of all amounts?
=SUM(D2:D8)Answer: 1,685.
2. How many orders are completed?
=COUNTIF(C2:C8, "completed")Answer: 5.
3. What is the revenue from completed West orders?
=SUMIFS(D2:D8, B2:B8, "West", C2:C8, "completed")Answer: 405 (orders 5002 and 5006).
4. What is the average amount of a completed order?
=AVERAGEIF(C2:C8, "completed", D2:D8)Answer: 276 (1,380 / 5).
5. In column E, mark orders of 300 or more as "Large", the rest as "Standard".
=IF(D2>=300, "Large", "Standard")Filled down, orders 5001 and 5004 are Large. Note the >=: the question says "300 or more", and tests often include a value right on the boundary to check this.
6. A colleague says West revenue is 615, using =SUMIF(B2:B8, "West", D2:D8). What is wrong?
Answer: the formula adds every West order, including the cancelled order 5003 (210). Revenue should count completed orders only, which gives 405. A "find the mistake" question like this is common, because it shows whether you check numbers instead of just producing them.
7. In a pivot table with Region in rows, Amount in values and Status filtered to completed, what does South show?
Answer: South does not appear at all. Its only order is refunded, so the filter removes every South row. North shows 495, East 480 and West 405. Noticing a missing group is part of the answer.
Here is the same table with every answer filled in, so you can compare it with yours. Click a cell to see its formula.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Status | Amount | Size | Question | Answer | |
| 2 | 5001 | North | completed | 320 | Large | 1. Total of all amounts | 1,685 | |
| 3 | 5002 | West | completed | 145 | Standard | 2. Completed orders | 5 | |
| 4 | 5003 | West | cancelled | 210 | Standard | 3. Completed West revenue | 405 | |
| 5 | 5004 | East | completed | 480 | Large | 4. Average completed order | 276 | |
| 6 | 5005 | South | refunded | 95 | Standard | 6. Colleague's formula | 615 | |
| 7 | 5006 | West | completed | 260 | Standard | 6. Fixed: completed only | 405 | |
| 8 | 5007 | North | completed | 175 | Standard |
For more practice of this kind, see our Excel practice exercises.
Excel test for a job interview: how to prepare in a week
- Day 1: find your level. Do the sample test above with a timer. Note what slowed you down.
- Days 2 and 3: conditional totals and counts. SUMIFS, COUNTIFS and AVERAGEIF on a table with a status column. Always ask which rows should count.
- Day 4: lookups. VLOOKUP with
FALSE, XLOOKUP, INDEX MATCH. Practise the#N/Acauses: missing values, spaces, numbers stored as text. - Day 5: pivot tables. Build totals by region and month, show values as percent of total, refresh after adding rows.
- Day 6: checking. Take formulas you did not write and find what is wrong with them. Trace Precedents helps here, and the free Excel formula checker breaks any formula into steps with their values.
- Day 7: a full timed run on a fresh table, without help.
If the test is part of an interview, you may be asked to explain your formulas out loud. Our list of Excel interview questions for data analysts covers the questions that usually follow.
Common mistakes
Practising with an AI assistant doing the work. It is tempting to ask a chatbot for each formula while you prepare. In the test you will not have it, and its formulas often contain exactly the mistakes tests look for: a VLOOKUP without FALSE, a SUM range that stops one row early, or a revenue total that includes cancelled orders. Use an assistant to explain a formula after you have written your own, then check its answer the same way you would check a colleague's.
Not reading the question's conditions. "Completed orders", "300 or more" and "in 2025" are each a criterion. Missing one gives a number that looks fine and is wrong.
Typing results instead of formulas. Many tests check that a cell contains a formula. A typed 405 may be marked wrong even if the value is right.
Forgetting $ before filling down. The first row is right and the rest are not. Click the last cell before moving on.
Spending too long on one question. Skip it, finish the rest, and come back.
FAQ
What is on an Excel assessment test?
Usually SUM and AVERAGE, conditional functions such as SUMIFS and COUNTIFS, lookups, IF logic, pivot tables and sometimes charts or data cleaning. Many also include a formula to fix.
How do I pass an Excel skills test for a job?
Practise the intermediate functions on a small table under a time limit, check every answer with a second method, and read each question's conditions carefully.
What level of Excel do data analysts need?
At least intermediate: conditional totals, lookups, IF logic and pivot tables, used confidently and checked carefully.
Is an Excel interview test hard?
It depends on the role, but most focus on everyday tasks rather than obscure functions. Speed and accuracy matter more than knowing every feature.
Where can I practise for an Excel test?
Our practice page has drills on one company's data, including exercises where you find and fix an AI assistant's mistake.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- The AI’s total looks lowAudit
- West revenue, completed only
- Prices pulled by the AI look oddAudit
- Which orders need a follow-up?