Excel test for interviews
A free practice test like the ones finance and admin employers use. Twelve questions on one invoice ledger: type a formula and it's marked straight away, the way an interviewer would mark it.
0 of 12 correct
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Customer | Region | Amount | Status | Days overdue | Loan amount | 25,000 | |
| 2 | INV-1001 | Alder Foods | North | 4,200 | Paid | 0 | Annual interest rate | 6% | |
| 3 | INV-1002 | Birch Motors | South | 7,850 | Unpaid | 34 | Years | 5 | |
| 4 | INV-1003 | Cedar Clinic | East | 1,260 | Paid | 0 | |||
| 5 | INV-1004 | Alder Foods | North | 3,375 | Unpaid | 12 | |||
| 6 | INV-1005 | Dune Logistics | West | 9,400 | Unpaid | 61 | |||
| 7 | INV-1006 | Elm Studio | South | 980 | Paid | 0 | |||
| 8 | INV-1007 | Fern Dental | East | 5,125 | Unpaid | 5 | |||
| 9 | INV-1008 | Birch Motors | South | 2,640 | Paid | 0 | |||
| 10 | INV-1009 | Kestrel Ltd | North | 6,300 | Unpaid | 47 | |||
| 11 | INV-1010 | Grove Hotels | West | 3,890 | Paid | 0 | |||
| 12 | INV-1011 | Cedar Clinic | East | 2,215 | Unpaid | 19 | |||
| 13 | INV-1012 | Dune Logistics | West | 1,745 | Paid | 0 |
-
SUM What is the total of all invoices?
Hint
Add up the Amount column, D2:D13.
Show an answer
=SUM(D2:D13) -
SUMIF What is the total invoiced to the North region?
Hint
SUMIF(range to test, criteria, range to add).
Show an answer
=SUMIF(C2:C13,"North",D2:D13) -
COUNTIF How many invoices are unpaid?
Hint
Count the cells in the Status column that say "Unpaid".
Show an answer
=COUNTIF(E2:E13,"Unpaid") -
SUMIFS What is the total unpaid amount in the South region?
Hint
SUMIFS takes the range to add first, then pairs of range and criteria.
Show an answer
=SUMIFS(D2:D13,C2:C13,"South",E2:E13,"Unpaid") -
AVERAGEIF What is the average invoice value in the East region, rounded to 2 decimal places?
Hint
Wrap AVERAGEIF in ROUND(…, 2).
Show an answer
=ROUND(AVERAGEIF(C2:C13,"East",D2:D13),2) -
VLOOKUP Using VLOOKUP, what is the amount on invoice INV-1007?
Hint
Amount is the 4th column of A2:F13. Use FALSE for an exact match.
Show an answer
=VLOOKUP("INV-1007",A2:F13,4,FALSE) -
INDEX MATCH Using INDEX and MATCH, which customer is on invoice INV-1010?
Hint
MATCH finds the row of INV-1010 in A2:A13; INDEX returns that row from B2:B13.
Show an answer
=INDEX(B2:B13,MATCH("INV-1010",A2:A13,0)) -
XLOOKUP Using XLOOKUP, which region is Kestrel Ltd in?
Hint
XLOOKUP(what to find, where to look, what to return).
Show an answer
=XLOOKUP("Kestrel Ltd",B2:B13,C2:C13) -
COUNTIFS How many unpaid invoices are more than 30 days overdue?
Hint
Two conditions: Status is "Unpaid" and Days overdue is ">30".
Show an answer
=COUNTIFS(E2:E13,"Unpaid",F2:F13,">30") -
MAXIFS What is the largest unpaid invoice?
Hint
MAXIFS(range to take the maximum of, range to test, criteria).
Show an answer
=MAXIFS(D2:D13,E2:E13,"Unpaid") -
PMT What is the monthly repayment on the loan in I1:I3? Show it as a positive number, rounded to 2 decimal places.
Hint
Monthly rate is I2/12, number of payments is I3*12, and put a minus in front of the loan amount to get a positive result.
Show an answer
=ROUND(PMT(I2/12,I3*12,-I1),2) -
IFERROR Look up the amount on invoice INV-2000, showing "Not found" if it doesn't exist.
Hint
Wrap a VLOOKUP (or XLOOKUP) in IFERROR(…, "Not found").
Show an answer
=IFERROR(VLOOKUP("INV-2000",A2:F13,4,FALSE),"Not found")
What an Excel interview test looks like
You're usually given a small data set, often invoices, sales or payroll, and asked to answer questions with formulas: totals by region, counts that meet two conditions, a lookup by ID, a value that shows "Not found" instead of an error, and sometimes a loan or investment calculation. The interviewer checks the answer and the formula, so a hard-typed number or the wrong function loses marks.
Want to practise on real data afterwards? The bank statement converter turns a PDF bank statement into Excel or CSV, with every row checked against the statement's running balance, so you can try SUMIFS and lookups on your own transactions.
Questions
What Excel skills do interview tests check?
Most finance and admin Excel tests check the same core: SUM and AVERAGE, SUMIF and SUMIFS, COUNTIF and COUNTIFS, VLOOKUP, INDEX and MATCH, XLOOKUP, IF and IFERROR, rounding, and a finance function such as PMT. This practice test covers all of them on one realistic invoice ledger.
Is this like a real Excel assessment test?
Yes in format: you get a data sheet and questions, and you answer each with a formula. Real tests are often timed and some are in a live copy of Excel, but the functions and the thinking are the same.
Should I use VLOOKUP or INDEX MATCH in an interview?
Know both. VLOOKUP is still the most asked-about function, INDEX and MATCH show you can look left and handle changing columns, and XLOOKUP is the modern replacement in Excel 365. Some questions here ask for a specific one, as interview tests often do.
Why does it say to use cell references?
An interviewer wants a formula that would still be right if the data changed. Typing the answer as a number would score nothing in a real test, so it scores nothing here.
Can I use array formulas like SUMPRODUCT((range="x")*range)?
Not in this practice sheet: it handles normal formulas only. Every question can be answered with SUMIFS, COUNTIFS or a lookup, which is what most interview tests expect anyway.
Is it free?
Yes. Nothing to install and no sign-up. Your answers are checked in your browser and your score is saved on your own device.