QuickCheckTools

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

ABCDEFGHI
1InvoiceCustomerRegionAmountStatusDays overdueLoan amount25,000
2INV-1001Alder FoodsNorth4,200Paid0Annual interest rate6%
3INV-1002Birch MotorsSouth7,850Unpaid34Years5
4INV-1003Cedar ClinicEast1,260Paid0
5INV-1004Alder FoodsNorth3,375Unpaid12
6INV-1005Dune LogisticsWest9,400Unpaid61
7INV-1006Elm StudioSouth980Paid0
8INV-1007Fern DentalEast5,125Unpaid5
9INV-1008Birch MotorsSouth2,640Paid0
10INV-1009Kestrel LtdNorth6,300Unpaid47
11INV-1010Grove HotelsWest3,890Paid0
12INV-1011Cedar ClinicEast2,215Unpaid19
13INV-1012Dune LogisticsWest1,745Paid0
  1. SUM What is the total of all invoices?

    Hint

    Add up the Amount column, D2:D13.

    Show an answer

    =SUM(D2:D13)

  2. 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)

  3. COUNTIF How many invoices are unpaid?

    Hint

    Count the cells in the Status column that say "Unpaid".

    Show an answer

    =COUNTIF(E2:E13,"Unpaid")

  4. 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")

  5. 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)

  6. 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)

  7. 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))

  8. 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)

  9. 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")

  10. 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")

  11. 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)

  12. 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.

Free calculator by QuickCheckTools