BenchmarkPrep › Guides

Sample Excel test questions with answers

These are the kinds of questions you'll meet in a hands-on Excel test for a job, from the basics through to analyst level. Each one shows the formula and why it works. Try them yourself first, then check.

Updated 6 November 2026

All the questions use this small sales list:

1. Revenue per row (basic)

Question: In F2, calculate Revenue (Units × Price) and copy it down to F6.

Answer: =D2*E2, then copy down. Relative references shift automatically, so F3 becomes =D3*E3. F2 shows $500.00.

2. Total units (basic)

Question: Total the units sold.

Answer: =SUM(D2:D6), which gives 187.

3. Flag big sales (IF)

Question: In G2, show "Large" if Units is 40 or more, otherwise "Small".

Answer: =IF(D2>=40,"Large","Small"). Watch the "or more": that's >=, not >.

4. Count one region (COUNTIF)

Question: How many sales were in the North region?

Answer: =COUNTIF(B2:B6,"North"), which gives 3.

5. Units with two conditions (SUMIFS)

Question: Total the units Priya sold in the North.

Answer: =SUMIFS(D2:D6,B2:B6,"North",C2:C6,"Priya"), which gives 90. The sum range comes first in SUMIFS, then each range-and-condition pair.

6. Conditions on dates (intermediate)

Question: Total the units sold in February 2026.

Answer: =SUMIFS(D2:D6,A2:A6,">="&DATE(2026,2,1),A2:A6,"<="&DATE(2026,2,28)), which gives 72. The comparison goes in quotes and is joined to the date with &.

7. Look up a value (VLOOKUP / XLOOKUP)

Question: A rep's name is typed in I2. In I3, return the region of that rep's first sale.

Answer: =XLOOKUP(I2,C2:C6,B2:B6), or with INDEX and MATCH: =INDEX(B2:B6,MATCH(I2,C2:C6,0)). VLOOKUP can't do this directly, because it only looks to the right of the lookup column, and Region is to the left of Rep.

8. Share of total with $ references (intermediate)

Question: In H2, show each sale's share of total units, then copy it down.

Answer: =D2/SUM($D$2:$D$6), formatted as a percentage. The $ signs keep the total range fixed as you copy down; without them, the range would shift and every row below would be wrong. H2 shows about 21%.

How did you do?

If you got 1–5 easily, you're ready for most admin and office tests. If 6–8 took you a while, practise those under time pressure: they're exactly what analyst-level tests are built around.

Frequently asked questions

What are the most common Excel test questions?

Calculations copied down a column, SUM and AVERAGE, IF, COUNTIF and SUMIF, SUMIFS/COUNTIFS with several conditions, and a lookup (VLOOKUP, XLOOKUP or INDEX + MATCH). Formatting and sorting also appear in most tests.

Should I use VLOOKUP or XLOOKUP?

Either is usually accepted. XLOOKUP is simpler and can look left; VLOOKUP works in every version of Excel. INDEX + MATCH works everywhere and can also look left.

Why does my answer show the right number but get marked wrong?

Usually because it was typed in rather than calculated, or because the formula includes a value that should come from a cell. Many tests change the data to check that your formula still works.

Where can I practise these in a real spreadsheet?

Our free Excel test lets you type formulas into a live spreadsheet, timed, with instant marking and model answers.