Excel Test Questions and Answers With Worked Examples
By ExcelReady TeamUpdated 7 min read
The short answer
Most Excel test questions ask you to total, average, count, look up or label data in a small table. The usual answers are SUM and AVERAGE for totals, VLOOKUP or XLOOKUP for one value, SUMIF and COUNTIF for groups, IF for labels and IFERROR for missing items. Practise each until it takes under two minutes.
Key takeaways
- Most test questions are written as a manager's request, not as "use SUMIF", so learn to spot the function behind the words.
- VLOOKUP needs FALSE as its last argument for an exact match, or it can return the wrong row.
- SUMIF adds by group, COUNTIF counts by group and IF turns a rule into a label you can fill down.
- Lock fixed inputs like a raise percentage with $ before you fill a formula down.
- Check every answer against the data with a quick mental sum before you move on.
On this page+
These Excel test questions and answers cover the tasks that show up again and again in job tests: totals, averages, lookups, totals by group, counts, labels and missing values. Every answer is worked on the same small staff table, so you can type the data in and check each result yourself. It's for anyone with an Excel test coming up who wants to see real questions answered step by step.
The sample data for every question#
Most Excel tests give you one small table and ask several questions about it. All ten questions below use this staff table in A1:E7:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Name | Dept | Salary | Rating |
| 2 | 101 | Asha | Sales | 52000 | 4 |
| 3 | 102 | Ben | Finance | 61000 | 3 |
| 4 | 103 | Carla | Sales | 48000 | 5 |
| 5 | 104 | Dev | IT | 70000 | 4 |
| 6 | 105 | Elena | Finance | 58000 | 2 |
| 7 | 106 | Farid | Sales | 55000 | 3 |
Notice that the questions are written the way a manager would ask them. A test rarely says "use SUMIF". Part of the skill is spotting which function the question needs.
Questions 1–3: totals and averages#
These are the warm-up questions on almost every basic test.
Question 1: What is the total salary cost?
=SUM(D2:D7)Answer: 344000. SUM adds every number in the range.
Question 2: What is the average salary?
=AVERAGE(D2:D7)Answer: 57333.33 (344000 ÷ 6, shown to two decimals). AVERAGE ignores empty cells, so a blank salary would not count as zero.
Question 3: What is the highest salary, and who earns it?
=MAX(D2:D7) returns 70000. To get the name as well, find the position of the maximum and return the name from the same row:
=INDEX(B2:B7,MATCH(MAX(D2:D7),D2:D7,0))Answer: Dev. MATCH finds which row holds 70000 (the 4th), and INDEX returns the 4th name.
Questions 4–5: looking up one value#
Lookup questions give you an ID or name and ask for a detail from the same row.
Question 4: What is the salary of employee 104?
=VLOOKUP(104,A2:E7,4,FALSE)Answer: 70000. The four parts are: what to find (104), where to look (A2:E7, with the IDs in the first column), which column to return (4 = Salary), and FALSE for an exact match. Leave out FALSE and VLOOKUP uses an approximate match, which can return a wrong row on unsorted data. The VLOOKUP function guide explains each argument, and the article on VLOOKUP interview questions covers the follow-up questions interviewers like to ask.
Question 5: Look up ID 110. If it doesn't exist, show "Not found".
=IFERROR(VLOOKUP(110,A2:E7,4,FALSE),"Not found")Answer: Not found. Without IFERROR, the cell would show #N/A. In Excel 2021 or Microsoft 365, =XLOOKUP(110,A2:A7,D2:D7,"Not found") does the same in one function.
Questions 6–8: totals and counts by group#
Group questions ask "how much" or "how many" for one department, region or status.
Question 6: What is the total salary for the Sales department?
=SUMIF(C2:C7,"Sales",D2:D7)Answer: 155000 (52000 + 48000 + 55000). SUMIF checks the Dept column for "Sales" and adds the matching salaries. See the SUMIF function guide for the argument order.
Question 7: How many people work in Sales?
=COUNTIF(C2:C7,"Sales")Answer: 3. COUNTIF counts matches; it doesn't add anything.
Question 8: What is the average salary in Finance?
=AVERAGEIF(C2:C7,"Finance",D2:D7)Answer: 59500 ((61000 + 58000) ÷ 2). If you mix up when to sum, count or look up, the comparison of VLOOKUP vs SUMIF shows when each one is the right answer.
Questions 9–10: labels and fixed inputs#
These questions check that you can turn a rule into a formula and fill it down correctly.
Question 9: In column F, show "Bonus" for anyone rated 4 or higher, and "No bonus" for everyone else.
In F2, type:
=IF(E2>=4,"Bonus","No bonus")Then fill down to F7 (Windows: Ctrl+D; Mac: ⌘D). Answer: Asha, Carla and Dev get "Bonus"; Ben, Elena and Farid get "No bonus". IF has three parts: the test, the result if true, and the result if false. Text results need double quotes. See the IF function guide for more.
Question 10: H1 holds a raise of 5%. In column G, show each new salary.
In G2, type:
=D2*(1+$H$1)Answer for Asha: 54600. The $ signs lock H1, so every row uses the same raise when you fill down. Without them, the reference would slide to H2, H3 and so on, which are empty, and every row after the first would just repeat the old salary. This is one of the most common mistakes in timed tests.
How to answer Excel test questions so they're marked correct#
The right number is not always enough. Many tests check how you got it. Follow these habits:
- Use a formula, not a typed number. If the cell holds 155000 as a typed value, a grader that checks formulas will mark it wrong.
- Point to cells, not values.
=VLOOKUP(G2,A2:E7,4,FALSE)is better than typing 105 into the formula, because it still works when the ID changes. - Check with a quick estimate. Three Sales salaries around 50000 should total about 150000. If you see 15500 or 1550000, something is off.
- Read the question twice. "Average salary in Finance" and "average salary" are different questions.
The guide on how to pass an Excel test for a job interview adds a one-week practice plan, and the list of 25 Excel interview questions covers the spoken questions that often come with a test. If you're new to work, start with the Excel interview questions for freshers.
Practise it#
Reading answers is a start, but a test checks whether you can type them under time pressure. Practise VLOOKUP in a live spreadsheet and practise SUMIF totals by group: you type the formula, and it checks it instantly. Then work the whole staff table in the HR roster interview drill, or sit the timed intermediate Excel mock test to see how you do against the clock.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
What is the salary for employee ID 105? Click H2 and use VLOOKUP to find the ID in G2 in the table A2:E7, returning the Salary column (column 4) with an exact match.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rating | ID | |||||||
| 2 | |||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 |
Tip: VLOOKUP takes what to find, the table, the column number to return, then FALSE for an exact match.
Practise this in a lesson
Frequently asked questions
What questions are asked in an Excel test?
Most Excel tests ask you to total and average columns, look up a value by ID, total or count by a group, label rows with IF, and handle missing values. Intermediate tests add pivot tables, COUNTIFS and SUMIFS with two conditions, and text or date formulas.
How do I pass an Excel test for a job?
Practise the common tasks on small tables until each takes under two minutes, read every question twice, and check each answer against the data. Use formulas rather than typed numbers, because many tests check the formula in the cell.
Is VLOOKUP on every Excel test?
Not every test, but a lookup by ID or name appears in most intermediate tests. If you know VLOOKUP with FALSE, XLOOKUP and INDEX MATCH, you can answer any version of the question.
What is the difference between SUMIF and COUNTIF?
SUMIF adds the numbers in rows that match a condition, such as the total salary for Sales. COUNTIF counts how many rows match, such as how many people work in Sales.