Excel Practice Test With Answers: 15 Real Questions
By ExcelReady TeamUpdated 5 min read
The short answer
This Excel practice test has 15 questions on one small sales table, in three parts: basic formulas, conditional counting and summing, and lookups with logic. Each question shows the correct formula and its result. Type the data into a sheet, answer without looking, then check. Aim for 12 or more correct in 20 minutes.
Key takeaways
- Every question uses the same eight-row table, so you can type it once and answer all 15.
- Most screening tests mix three skills: basic maths, conditional totals and lookups.
- Write the formula first, then compare it with the answer; the result alone can hide a lucky guess.
- Lock ranges with $ whenever a formula will be filled down, as in question 4.
- If you score under 10, practise the part where you dropped the most points before you retake.
On this page+
This Excel practice test with answers gives you 15 questions on one small sales table, the kind of tasks that come up again and again in job screening tests. It runs from basic formulas through COUNTIF and SUMIFS to VLOOKUP and INDEX MATCH, and every answer shows the formula and the result. The questions were written by ExcelReady for practice; they are not taken from any employer or test provider.
How to use this practice test#
Treat it like the real thing. Type the table below into a blank sheet, set a timer for 20 minutes, and write each formula before you read the answer.
- Answer in a spare cell to the right of the data, such as column G or H.
- Check the formula, not only the result. A right number from the wrong formula fails when the data changes.
- Mark each question right or wrong, then use the score guide at the end.
If you want more warm-up first, the list of Excel practice tasks to do before a test covers the core skills one by one.
The practice data#
All 15 questions use this order list in A1:E9:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Rep | Region | Units | Price |
| 2 | 1001 | Ana | North | 12 | 25 |
| 3 | 1002 | Ben | South | 5 | 40 |
| 4 | 1003 | Ana | East | 8 | 25 |
| 5 | 1004 | Cara | North | 20 | 15 |
| 6 | 1005 | Ben | North | 3 | 40 |
| 7 | 1006 | Cara | South | 15 | 15 |
| 8 | 1007 | Ana | South | 6 | 40 |
| 9 | 1008 | Dev | East | 10 | 25 |
Part 1: Basic formulas (questions 1 to 5)#
These check that you can write a formula, use a function and copy it correctly.
Q1. How many units were sold in total?
Answer: =SUM(D2:D9) returns 79.
Q2. What is the average price, rounded to 2 decimal places?
Answer: =ROUND(AVERAGE(E2:E9),2) returns 28.13. AVERAGE gives 28.125, and ROUND rounds the 5 up. Changing the cell format only changes how it looks; ROUND changes the value.
Q3. In F2, calculate the revenue for order 1001, then fill it down to F9.
Answer: =D2*E2 returns 300. Fill down with a double-click on the fill handle or Ctrl+D (Mac: ⌘D). The column totals 1,835.
Q4. In G2, show each order's share of total revenue, ready to fill down.
Answer: =F2/SUM($F$2:$F$9) returns 0.163, or 16.3% formatted as a percentage. The $ signs lock the total range. Without them, the range slides down as you fill and the shares are wrong.
Q5. What is the largest number of units on one order?
Answer: =MAX(D2:D9) returns 20 (order 1004).
Part 2: Conditional counting and summing (questions 6 to 10)#
These are the most common questions in screening tests. Watch the quotes around text and operators.
Q6. How many orders came from the North region?
Answer: =COUNTIF(C2:C9,"North") returns 3. See the COUNTIF function guide for every type of criteria.
Q7. How many orders had 10 or more units?
Answer: =COUNTIF(D2:D9,">=10") returns 4. The operator goes inside the quotes.
Q8. How many units did Ana sell?
Answer: =SUMIF(B2:B9,"Ana",D2:D9) returns 26 (12 + 8 + 6).
Q9. How many units did Cara sell in the South?
Answer: =SUMIFS(D2:D9,C2:C9,"South",B2:B9,"Cara") returns 15. In SUMIFS, the range to add comes first.
Q10. In H2, label each order "Large" if it has 10 or more units, otherwise "Small".
Answer: =IF(D2>=10,"Large","Small") returns Large for order 1001. The IF function guide explains the three parts.
Part 3: Lookups and logic (questions 11 to 15)#
These separate a basic score from a strong one. Exact matches matter here.
Q11. Which rep handled order 1006?
Answer: =VLOOKUP(1006,A2:E9,2,FALSE) returns Cara. FALSE forces an exact match; without it, VLOOKUP can return a wrong row on unsorted data. The VLOOKUP function guide covers the four arguments.
Q12. What was the price on order 1003? Use INDEX and MATCH.
Answer: =INDEX(E2:E9,MATCH(1003,A2:A9,0)) returns 25.
Q13. What is Dev's order number?
Answer: =INDEX(A2:A9,MATCH("Dev",B2:B9,0)) returns 1008. VLOOKUP can't do this, because the order number is to the left of the rep. This is the exercise on this page.
Q14. Look up the rep for order 1010 and show "Not found" if it doesn't exist.
Answer: =IFERROR(VLOOKUP(1010,A2:E9,2,FALSE),"Not found") returns Not found.
Q15. How many North orders had 10 or more units?
Answer: =COUNTIFS(C2:C9,"North",D2:D9,">=10") returns 2 (orders 1001 and 1004). Every condition must be true for a row to count.
How to score yourself and what to practise next#
Count your correct formulas and compare with this guide:
| Score | What it means | Next step |
|---|---|---|
| 13 to 15 | Ready for most basic and intermediate tests | Take a timed mock test |
| 10 to 12 | Solid basics, gaps in one area | Practise the part where you lost points |
| Under 10 | Core skills need work | Rebuild Parts 1 and 2 before lookups |
Look at where you lost points. Mistakes in Part 1 usually come from cell references. Mistakes in Part 2 are usually quotes and argument order. Part 3 mistakes are usually a missing FALSE or 0 for an exact match.
For more practice questions, see Excel test questions and answers and the Excel interview questions for freshers. When you're ready for a timed run, try a mock Excel test online such as the basic Excel test or the intermediate Excel test. Not sure where you stand? The Excel level test places you in 12 questions. And if a real test didn't go well, read what to do after failing an Excel test.
Practise it#
Reading answers is not the same as typing them under time pressure. Start with the INDEX MATCH exercise on this page. Then work through the skill checks, where you solve tasks with no hints and every formula is checked instantly: the basics skill check, the summary grid skill check with COUNTIFS and SUMIFS and the INDEX/MATCH skill check.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Question 13: what is Dev's order number? The Order column is to the left of Rep, so use INDEX and MATCH. Type the formula in H2.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Price | ||||||||
| 2 | Dev's order | ||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 | |||||||||
| 12 |
Tip: MATCH finds Dev's position in B2:B9 (use 0 for an exact match). INDEX returns the order number at that position in A2:A9.
Practise this in a lesson
Frequently asked questions
What questions are on an Excel test for a job?
Most cover basic formulas like SUM and AVERAGE, cell references, IF, COUNTIF and SUMIF, and a lookup with VLOOKUP or XLOOKUP. Harder tests add SUMIFS, INDEX MATCH, pivot tables and charts.
How can I practise for an Excel test online?
Use a timed practice test in a real spreadsheet, so you type formulas instead of picking multiple-choice answers. Then review every mistake and practise that skill on its own.
What is a good score on an Excel practice test?
On this test, 12 out of 15 in 20 minutes shows you are ready for most basic and intermediate screening tests. Employers set their own pass marks, so aim to be fast and accurate rather than just passing.
Are these the questions employers actually use?
They are realistic questions of the type that appear in common Excel screening tests, written by ExcelReady. They are not copied from any employer or test provider.