Excel guide · 6 min read

Excel assessment questions for employers (with answer key)

Quick answerThe most useful Excel assessment questions are small tasks, not trivia: give the candidate a realistic sheet and ask them to produce a total, a lookup, a cleaned list or a fixed formula. Below are 11 tasks on one expense-claims sheet, grouped by level, each with a model answer calculated by a spreadsheet engine, what a strong answer looks like and the weak answers to watch for, plus follow-up questions to ask afterwards.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	E	F	G	I	K	L
1	Claim	Employee	Dept	Submitted	Paid	Amount	Status	Limit	Dept	Manager
2	C-201	dana lee	Sales	Oct 1, 2026	Oct 6, 2026	240	Approved	500	Sales	Priya Shah
3	C-202	MARK BROWN	Ops	Oct 2, 2026	Oct 9, 2026	610	Approved		Ops	Tom Reed
4	C-203	sara kim	Finance	Oct 5, 2026		85	Pending		Finance	Ana Cruz
5	C-204	dana lee	Sales	Oct 7, 2026	Oct 13, 2026	1150	Approved
6	C-205	Luis Ortega	Marketing	Oct 8, 2026		320	Rejected
7	C-206	mark brown	Ops	Oct 12, 2026	Oct 16, 2026	95	Approved
8	C-207	Priya Nair	Sales	Oct 14, 2026		540	Pending
9	C-208	sara kim	Finance	Oct 15, 2026	Oct 20, 2026	410	Approved

Download this sheet as an Excel practice file (.xlsx)

How to use these tasks

Copy the sheet above into a workbook: claims in A:G, the claim limit in I2, a department-to-manager table in K:L and a report department in N2. The names in column B are messy on purpose (extra spaces, capitals), just like a real export. Pick the level that matches the role and give every candidate the same tasks.

Give instructions in plain words, the way a manager would ask: "Flag every claim over the limit" rather than "Use IF with an absolute reference". Naming the function turns the task into a recall test. Allow about 3-5 minutes per task, and tell candidates any working formula is fine. For how to run and score the test, see how to test Excel skills in an interview.

Basic tasks (admin, coordinator, support roles)

1. What is the total of all claims?

Strong: a SUM over the whole Amount column. Weak: a range that stops at F8, or the number typed in after adding it on a calculator.

=SUM(F2:F9)

Result: 3450

3,450 across the eight claims.

2. How many claims are approved?

Strong: COUNTIF on the Status column. Weak: counting by eye and typing 5, which fails as soon as a status changes.

=COUNTIF(G2:G9,"Approved")

Result: 5

5 approved claims.

3. In column H, flag each claim over the limit in I2 with "Check", otherwise "OK".

This one tests the dollar sign. Strong: the limit locked as $I$2 so the formula copies down. Weak: I2 without $, which moves to I3, I4 and so on as it's copied, or 500 typed into the formula.

=IF(F2>$I$2,"Check","OK")

Result: OK

OK for the first claim (240). Copied down, the claims of 610, 1,150 and 540 show Check.

4. What share of the total is each claim?

Strong: the claim divided by a locked total, formatted as a percentage. Weak: dividing by a total typed in as 3450.

=F2/SUM($F$2:$F$9)

Result: 0.069565217391 shows as 7.0% with Percent format

The first claim is about 7.0% of the total.

Intermediate tasks (operations, sales ops, junior analyst)

5. Total approved claims for the department in N2.

Strong: SUMIFS with the department pointed at N2 and Approved as a second condition. Weak: SUMIF on department only, which also counts the pending claim.

=SUMIFS(F2:F9,C2:C9,N2,G2:G9,"Approved")

Result: 1390

1,390 for Sales: 240 plus 1,150. The pending 540 is left out; SUMIF on department alone would give 1,930.

6. Look up each claim's manager from the table in K:L, showing "No manager listed" if the department isn't there.

Marketing isn't in the table, which is the point. Strong: XLOOKUP with a not-found value, or VLOOKUP with FALSE wrapped in IFNA. Weak: VLOOKUP without FALSE, or a #N/A left showing with no explanation.

=XLOOKUP(C6,$K$2:$K$4,$L$2:$L$4,"No manager listed")

Result: No manager listed

No manager listed, for Luis Ortega's Marketing claim. The same formula returns Priya Shah for the Sales claims.

7. Clean up the employee names: no extra spaces, each word capitalised.

Strong: TRIM to remove spaces (including the double space inside "sara kim") and PROPER for capitals, or Flash Fill with a check afterwards. Weak: retyping names by hand.

=PROPER(TRIM(B4))

Result: Sara Kim

Sara Kim, with the inner double space reduced to one.

8. How many working days did each paid claim take, leaving unpaid claims blank?

Strong: NETWORKDAYS between Submitted and Paid, with an IF for an empty Paid date. Weak: plain subtraction (calendar days, not what was asked) or NETWORKDAYS on blank dates, which gives nonsense.

=IF(E2="","",NETWORKDAYS(D2,E2))

Result: 4

4: Thursday 1 October to Tuesday 6 October, counting both ends and skipping the weekend.

Advanced tasks (financial and data analysts)

9. How many different employees submitted claims?

This tests whether the candidate notices dirty data before counting it. Counting the raw names gives 7, because " dana lee " and "dana lee" are different text, and so are "sara kim" and "sara kim". UNIQUE ignores capitals, so MARK BROWN and mark brown already count once.

Strong: clean first, then count, in one formula or with a helper column. Also strong: noticing the problem and saying so, even with a different method.

=COUNTA(UNIQUE(PROPER(TRIM(B2:B9))))

Result: 5

5 different employees once the names are cleaned.

10. List the claims that are over the limit and not rejected.

Strong (Excel 365 or 2021): FILTER with two conditions multiplied together. Also fine: a filter on a Table, or a helper column and an advanced filter. Weak: copying rows by hand.

=FILTER(A2:A9,(F2:F9>I2)*(G2:G9<>"Rejected"))

Result: spills 3 rows × 1 column

C-202

C-204

C-207

C-202, C-204 and C-207 spill into the cells below.

11. A colleague's share-of-total column gives wrong numbers from row 3 down. Find and fix the bug.

Give them the broken formula. It was written as =F2/SUM(F2:F9) and copied down without $, so the total slides down with each row and loses claims from the top.

Strong: spots it in under a minute and locks the total as $F$2:$F$9, which gives about 17.7% for this row. Very strong: also checks that the shares now add up to 100%.

=F3/SUM(F3:F10)

Result: 0.19003115265 shows as 19.0% with Percent format

About 19.0%: wrong, because the total only covers rows 3 to 10 and leaves out the first claim.

Follow-up questions to ask after the tasks

Five minutes of talk after the tasks tells you whether they understood what they built. Listen for the reasoning, not textbook wording.

"Walk me through the formula in task 5."

Listen for: what each range is for, why N2 instead of typing Sales, and why the second condition matters.

"How would you check your answers before sending this to a manager?"

Listen for: a cross-check (department totals adding up to the grand total), a row count, looking for blanks or text in number columns.

"What would break if someone inserted a row in the middle of the data?"

Listen for: ranges expanding when the row is inside them, typed-in cell picks not updating, and turning the list into a Table with Ctrl+T so new rows are picked up.

"When would you use a PivotTable instead of formulas?"

Listen for: PivotTables to explore or answer a one-off question, formulas for a fixed report layout, and remembering to refresh a PivotTable.

"Tell me about a spreadsheet you built or fixed that other people used."

Listen for: who used it, what went wrong, how they made it harder to break. Specifics beat function names.

Scoring the set

Score each task 0 (not done or wrong), 1 (right result, but typed values or a fragile method) or 2 (right, and still right when the data changes). For a basic role, tasks 1-4 out of 8; intermediate, tasks 1-8 out of 16; advanced, all 11 out of 22. Decide the pass mark before the first candidate.

Test the "still right" part yourself: change N2 to Ops, change the limit in I2, or insert a claim in the middle. Some tools automate this; Cellwise Hire runs each answer on hidden versions of the sheet and shows you the formula the candidate wrote. If you also want quick conversation questions, the job-seeker page of Excel interview questions shows what well-prepared candidates will have practised.

Common mistakes

Naming the function in the task

Describe the outcome ("total approved claims for Sales") and let the candidate choose the method.

Using perfectly clean data

Real exports have extra spaces, blanks and a missing lookup value. Include one of each so the test shows how candidates handle them.

Marking only one formula as correct

SUMIFS, SUMPRODUCT and a PivotTable can all be right. Score the result and robustness, not the method.

Giving advanced tasks to an admin role

Match the level to the job. Hard tasks for a basic role reject people who would do it well.

FAQ

What questions are on an Excel assessment test?

Usually totals and counts, IF, lookups, SUMIFS or COUNTIFS, text cleanup, dates, and at higher levels multi-condition lookups, dynamic arrays and fixing a broken formula. The best tests ask candidates to do these on a realistic sheet rather than pick from options.

How many questions should an Excel test for candidates have?

Four to six tasks for a basic role and eight to eleven for an analyst role is plenty. More tasks mostly test stamina; a few well-chosen ones show the skills that matter.

Should candidates be allowed to search online during an Excel test?

For take-home tests, assume they will, and design for it: realistic data and a follow-up conversation reveal understanding better than banning search. For live tasks, allowing a quick syntax lookup mirrors real work.

What is a good score on an Excel assessment?

There's no universal number. Set the pass mark from your own rubric before testing anyone, and compare candidates for the same role on the same test.

Functions used

Related guides

Updated November 7, 2026