Excel interview questions

10 Excel interview questions, answered.

Practical tasks of the kind employer Excel tests set, each with the data layout, the formula and why it works. Read them, then practise them in a live spreadsheet — the test checks what you type, not what you recognise.

Try 3 live questions Take the free 15-minute test

  1. 1. Total for one region

    Basic

    Data: Orders in rows 2–100: Region in A, Product in B, Sales in C.

    Task: Total sales for the West region.

    =SUMIF(A2:A100, "West", C2:C100)

    SUMIF adds C only where A matches. With more than one condition, switch to SUMIFS, which puts the sum range first.

  2. 2. Count with a condition

    Basic

    Data: Same table.

    Task: How many orders are over 1,000?

    =COUNTIF(C2:C100, ">1000")

    The operator goes inside the quotes. To compare against a cell instead, join it: ">"&F1.

  3. 3. Two conditions, one excluded value

    Intermediate

    Data: Same table.

    Task: Total North sales, excluding the product Lamp.

    =SUMIFS(C2:C100, A2:A100, "North", B2:B100, "<>Lamp")

    "<>" means not equal to. Each extra condition is another range and criterion pair.

  4. 4. Lookup with a missing ID

    Intermediate

    Data: Sheet Staff: Employee ID in A, Name in B, Department in C. Your sheet has an ID in E2.

    Task: Return the department, or "Not found" if the ID doesn't exist.

    =XLOOKUP(E2, Staff!A:A, Staff!C:C, "Not found")

    XLOOKUP's fourth argument replaces #N/A, and it can return a column to the left of the lookup column, which VLOOKUP cannot.

  5. 5. The same lookup without XLOOKUP

    Intermediate

    Data: As above, in a version of Excel without XLOOKUP.

    Task: Return the department with INDEX and MATCH.

    =IFERROR(INDEX(Staff!C:C, MATCH(E2, Staff!A:A, 0)), "Not found")

    MATCH finds the row (0 = exact match), INDEX returns that row from column C, IFERROR handles the missing ID.

  6. 6. Percent change

    Basic

    Data: Last year's revenue in B2, this year's in C2.

    Task: Calculate growth as a percentage.

    =(C2-B2)/B2

    New minus old, divided by old. Format the cell as a percentage rather than multiplying by 100.

  7. 7. Lock a reference

    Basic

    Data: Sales in C2:C20, one commission rate in G2.

    Task: Commission for row 2, written so it can be filled down to row 20.

    =C2*$G$2

    Without the dollar signs, filling down turns G2 into G3, G4… which are empty. F4 toggles the anchoring while editing.

  8. 8. Eligibility rule

    Basic

    Data: Performance rating in B, years of service in C.

    Task: Show Eligible if the rating is at least 3 and service is at least 1 year, otherwise Not eligible.

    =IF(AND(B2>=3, C2>=1), "Eligible", "Not eligible")

    AND returns TRUE only when every condition holds. Use OR when any one condition is enough.

  9. 9. Tiered rate from a table

    Intermediate

    Data: Sales in B2. Tier table: thresholds 0, 50000, 100000 in F2:F4; rates 5%, 7%, 10% in G2:G4.

    Task: Return the commission rate for B2.

    =XLOOKUP(B2, $F$2:$F$4, $G$2:$G$4, , -1)

    Match mode -1 returns the exact match or the next smaller threshold. A tier table survives new tiers; a nested IF has to be rewritten.

  10. 10. Clean messy names

    Basic

    Data: Names in A typed inconsistently, e.g. " sara AHMED ".

    Task: Return the name with extra spaces removed and proper capitalisation.

    =PROPER(TRIM(A2))

    TRIM removes leading, trailing and repeated spaces; PROPER capitalises each word.

Reading answers isn't the same as writing them

Practise these in Formula Quest's browser spreadsheet with instant feedback. The free Fundamentals Check scores you by skill in 15 minutes, and the Excel test guide explains what employer tests look like.

Take the free test