Excel interview questionsTechnical interview questions
← Interview & exam prep
Technical interview questions

Excel interview questions

29 questions from beginner to advanced, each with the correct answer and a one-line explanation of why. Study them here, or take them as a timed test.

29 questionsBeginner 11 · Intermediate 10 · Advanced 8Answers + explanations
Take it as a timed test
Free PDF
All 389 questions & answers, in one file

DAX, Excel, Power BI, Fabric, certification practice and analyst interviews — every answer with the reason behind it. Print it, study it offline.

Instant download, plus the free Tuesday briefing. Unsubscribe in one click.

Beginner 11 questions

1Which VLOOKUP 4th argument forces an EXACT match?

  1. FALSE (0)
  2. the word exact
  3. TRUE
  4. omit it
  5. 1
Show answerHide answer

A · FALSE (0) FALSE/0 = exact match; TRUE/1 = approximate and requires sorted data.

2Which reference locks BOTH the row and the column?

  1. A$1
  2. A#1
  3. A1
  4. $A$1
  5. $A1
Show answerHide answer

D · $A$1 $A$1 is fully absolute. $A1 and A$1 are mixed (one part locked).

3COUNTA counts…

  1. Non-empty cells of any type
  2. Only text
  3. Blank cells
  4. Unique values
  5. Only numbers
Show answerHide answer

A · Non-empty cells of any type COUNTA counts all non-empty cells; COUNT counts only numbers.

4IFERROR(x, y) returns y when…

  1. x is blank
  2. always
  3. x evaluates to an error
  4. x is 0
  5. x is FALSE
Show answerHide answer

C · x evaluates to an error IFERROR substitutes y only when x is an error value.

5Which modern function can look to the LEFT of the key, unlike VLOOKUP?

  1. INDEX only
  2. CHOOSE
  3. SUMIF
  4. HLOOKUP
  5. XLOOKUP
Show answerHide answer

E · XLOOKUP XLOOKUP searches independently of column position and can return values to the left.

6Which returns the POSITION of a value within a range?

  1. INDEX
  2. LOOKUP
  3. MATCH
  4. CHOOSE
  5. OFFSET
Show answerHide answer

C · MATCH MATCH returns the relative position; INDEX returns the value at a position.

7Which spills a list of unique values?

  1. DISTINCT
  2. SINGLE
  3. UNIQUE
  4. DEDUPE
  5. REMOVEDUPES
Show answerHide answer

C · UNIQUE UNIQUE is the dynamic-array function; DISTINCT is DAX, not Excel.

8Referencing a spilled range that starts in A2 uses…

  1. A2~
  2. A2!
  3. A2$
  4. A2#
  5. A2*
Show answerHide answer

D · A2# The spill operator A2# refers to the whole dynamic array that originates at A2.

9To sum with MULTIPLE criteria you use…

  1. SUMPRODUCT only
  2. SUMIFS
  3. SUMIF
  4. SUM
  5. DSUM
Show answerHide answer

B · SUMIFS SUMIFS supports several criteria pairs; SUMIF handles a single condition.

10A cleaner alternative to deeply nested IFs is…

  1. CHOOSECOLS
  2. AND only
  3. MULTIIF
  4. IFS or SWITCH
  5. NESTIF
Show answerHide answer

D · IFS or SWITCH IFS (and SWITCH) evaluate multiple conditions without heavy nesting.

11ROUND(2.5, 0) returns…

  1. 3
  2. 0
  3. 2.5
  4. 2
  5. an error
Show answerHide answer

A · 3 Excel's ROUND rounds halves away from zero, so 2.5 becomes 3.

Intermediate 10 questions

12XLOOKUP match_mode for 'exact or the NEXT LARGER item' is…

  1. 1
  2. TRUE
  3. -1
  4. 0
  5. 2
Show answerHide answer

A · 1 1 = exact or next larger; -1 = exact or next smaller; 0 = exact; 2 = wildcard.

13LET() is used to…

  1. Define a Table
  2. Create a macro
  3. Assign names to intermediate results inside one formula
  4. Lock cells
  5. Loop rows
Show answerHide answer

C · Assign names to intermediate results inside one formula LET names sub-expressions, improving readability and avoiding recomputation.

14Which array function returns a SINGLE accumulated value?

  1. SCAN
  2. MAP
  3. REDUCE
  4. LAMBDA
  5. BYROW
Show answerHide answer

C · REDUCE REDUCE returns one accumulated result; SCAN returns the array of intermediate accumulations.

15FILTER(range, cond) returns #CALC! when…

  1. The range is text
  2. There are too many rows
  3. No rows match and if_empty is omitted
  4. It is circular
  5. The condition is TRUE
Show answerHide answer

C · No rows match and if_empty is omitted With no matches and no if_empty argument, FILTER yields the #CALC! empty-array error.

16SUMPRODUCT is powerful because it can…

  1. Only sum one column
  2. Look up to the left
  3. Create a PivotTable
  4. Join text
  5. Multiply arrays element-wise and sum, handling multiple conditions
Show answerHide answer

E · Multiply arrays element-wise and sum, handling multiple conditions SUMPRODUCT does array math and multi-criteria sums without helper columns.

17In a Table, the structured reference [@Sales] means…

  1. The whole Sales column
  2. This row's value in the Sales column
  3. The totals row
  4. The Sales header
  5. An absolute reference
Show answerHide answer

B · This row's value in the Sales column [@Column] is the current row's value; [Column] is the entire column.

18TEXTJOIN differs from CONCAT because it can…

  1. Return numbers
  2. Do math
  3. Only join two cells
  4. Sort text
  5. Use a delimiter and optionally skip empty cells
Show answerHide answer

E · Use a delimiter and optionally skip empty cells TEXTJOIN adds a delimiter and can ignore blanks; CONCAT just concatenates.

19A key advantage of INDEX/MATCH over VLOOKUP is…

  1. It survives inserted columns and can look left
  2. It is always faster on huge data
  3. It sorts automatically
  4. It is shorter
  5. It needs no arguments
Show answerHide answer

A · It survives inserted columns and can look left INDEX/MATCH references columns by position/lookup, so inserting columns won't break it, and it can look left.

20SEQUENCE(3) generates…

  1. A random number
  2. The value 3
  3. Text
  4. An array {1;2;3}
  5. A blank
Show answerHide answer

D · An array {1;2;3} SEQUENCE spills an array of consecutive numbers.

21In text criteria, the wildcard for 'any SINGLE character' is…

  1. %
  2. ~
  3. *
  4. #
  5. ?
Show answerHide answer

E · ? ? matches one character; * matches any number of characters; ~ escapes a wildcard.

Advanced 8 questions

22A recursive LAMBDA must be…

  1. Marked volatile
  2. Written in VBA
  3. Wrapped in IFERROR
  4. Placed in a Table
  5. Named in the Name Manager so it can call itself
Show answerHide answer

E · Named in the Name Manager so it can call itself Recursion needs a defined name so the LAMBDA can reference itself.

23Volatile functions (NOW, RAND, OFFSET) are problematic because they…

  1. Recalculate on every recalculation, hurting performance
  2. Are always faster
  3. Never recalculate
  4. Only run on open
  5. Cache their result
Show answerHide answer

A · Recalculate on every recalculation, hurting performance Volatile functions force frequent recalculation across the workbook.

24The implicit intersection operator @ in a formula…

  1. Creates an error
  2. Sums a range
  3. Makes a reference absolute
  4. Forces a single value (legacy behavior)
  5. Spills an array
Show answerHide answer

D · Forces a single value (legacy behavior) @ coerces to a single value, reproducing pre-dynamic-array behavior.

25To combine text from a range with a delimiter while skipping blanks, use…

  1. TEXTJOIN
  2. CONCATENATE
  3. CONCAT
  4. MERGE
  5. JOIN
Show answerHide answer

A · TEXTJOIN Only TEXTJOIN offers both a delimiter and ignore-empty in one call.

26BYROW(array, LAMBDA(...)) is typically used to…

  1. Remove duplicates
  2. Apply a calculation per row and return one result per row
  3. Merge queries
  4. Transpose
  5. Sort each row
Show answerHide answer

B · Apply a calculation per row and return one result per row BYROW maps a LAMBDA across rows, returning a column of per-row results.

27In an array constant, columns and rows are separated by…

  1. Spaces
  2. Tabs
  3. Pipes
  4. Semicolon for columns, comma for rows
  5. Comma for columns, semicolon for rows
Show answerHide answer

E · Comma for columns, semicolon for rows {1,2,3} is a row; {1;2;3} is a column — comma separates columns, semicolon separates rows (US locale).

28MMULT performs…

  1. Matrix multiplication of two arrays
  2. Text merge
  3. A lookup
  4. Rounding
  5. Multiple IFs
Show answerHide answer

A · Matrix multiplication of two arrays MMULT returns the matrix product of two arrays (with compatible dimensions).

29To return multiple columns from XLOOKUP at once, you…

  1. Wrap in TEXTJOIN
  2. Use COUNTIF
  3. Use VLOOKUP instead
  4. Give a multi-column return_array so it spills several columns
  5. Cannot
Show answerHide answer

D · Give a multi-column return_array so it spills several columns XLOOKUP can return an entire row/multiple columns when the return array spans them.

What changed lately? Latest Formulas & Functions news, releases & questions →

Coming soon $19 one-time · early-access price
Prep Pro: full mock exams
  • Full-length, timed mock exams in the real PL-300, DP-600 and MO-201 format
  • Score breakdown by skill, with a study plan for your weak spots
  • New questions every month, written against the current exam outline

No payment now. We'll email you once, when it opens.

More technical interview questions