Excel Dynamic Array Interview Questions: FILTER, UNIQUE, SORT, and Spill Errors

An interviewer gives you an Excel orders table and asks for an alphabetized list of products sold in the East region. Your FILTER formula returns three rows, UNIQUE returns two products, and the final formula suddenly shows #SPILL! when someone types a note beneath it. What changed: the data, the formula, or the space available for the result?

Microsoft's dynamic-array guide says a formula entered in one anchor cell can place a variable-size result in neighboring cells. The four-order sheet below is an original practice exercise, not a candidate report or a claim about a named employer's actual assessment. No verified same-cycle report establishes this exact task. For related workbook-grain practice, Build SQL pivot with lookups and currency conversion helps separate source rows from a summarized output.

Question 1: What does the formula return at each step?

Begin with a tiny source table named Orders. In the interview, you could sketch this on paper or type it into a clean workbook. The rows are invented and each OrderID is one order line for this exercise.

OrderID	Region	Product	Amount
O1	East	Apple	10
O2	East	Berry	20
O3	West	Apple	30
O4	East	Apple	40

=FILTER(Orders[Product],Orders[Region]="East","No matches") returns Apple, Berry, Apple in that source order. The include array has four Boolean values, one for each source row. Microsoft's FILTER reference requires its height or width to match the array being filtered. If you accidentally compare a three-cell region range against a four-cell product range, you have a shape problem, not a valid partial filter.

Wrap the result: =UNIQUE(FILTER(Orders[Product],Orders[Region]="East")). The two-element output is Apple, Berry, because the second Apple is another occurrence of the same product. Wrap again: =SORT(UNIQUE(FILTER(Orders[Product],Orders[Region]="East"))). The result remains Apple, Berry in this small example because it was already ascending. To prove SORT is doing work, reorder the source rows so Berry comes first; the final output should still be Apple, Berry. This is a simple but useful test that separates each function's role.

The functions compose from the inside out, but the worksheet receives only the final array. The FILTER result is not permanently written into a hidden range unless you explicitly place it there. If you need to explain the intermediate values in an interview, write them out in a diagnostic area or use Evaluate Formula where available. The clarity of your trace matters more than squeezing the entire answer into one long expression immediately.

Question 2: Does UNIQUE mean “appears once”?

No. Microsoft's UNIQUE documentation distinguishes the default distinct-value behavior from exactly_once=TRUE. In the East product list Apple, Berry, Apple, default UNIQUE returns Apple and Berry. =UNIQUE(FILTER(Orders[Product],Orders[Region]="East"),,TRUE) returns Berry because Berry occurs exactly once and Apple occurs twice. That is a different analytical question.

There is another easy mistake: applying UNIQUE to all four columns of the East orders. O1 and O4 both sell Apple, but their OrderID and Amount differ, so the rows are not duplicates. UNIQUE(FILTER(Orders,Orders[Region]="East")) can still return all three East order rows. If the requested output is a list of distinct products, pass the Product column to UNIQUE, not the entire order table. If you want one representative order per product, UNIQUE alone does not choose which order should survive; define a selection rule.

This is the underlying interview principle: define the comparison key. “Unique rows,” “unique product names,” and “products bought exactly once” are three different outputs. In a real workbook, apparent duplicates may differ by trailing spaces, nonbreaking spaces, or other source normalization issues. Inspect the source values before assuming UNIQUE is broken. Clean the data intentionally and preserve the original when an audit trail matters.

Question 3: What makes #SPILL! different from a wrong answer?

A dynamic-array formula lives in one anchor cell and needs a free rectangular spill range. Microsoft's spilled-array behavior guide says the cells in that range display the result but are not independently editable. If the formula in G2 should return Apple in G2 and Berry in G3, a note or space character already in G3 blocks the result. Excel shows #SPILL! at the anchor. The formula may be logically correct; the grid placement is not.

The #SPILL! troubleshooting page describes several causes, including obstructing cells, merged cells, output extending beyond the worksheet edge, and an indeterminate volatile array size. Diagnose the specific cause shown by Excel rather than clearing an entire column blindly. Select the error indicator and its indicated spill area. Check for visible values, formulas returning "", merged cells, and Table boundaries. Move the formula or move the obstruction only after confirming what the obstructing content is used for.

Symptom in this exercise	Likely cause	Safe next action
G2 shows #SPILL!; G3 contains a note	Result cannot occupy G3	Move the note to a documented location, then recalculate
G2 shows #SPILL! inside Orders	Spill formula placed inside an Excel Table	Put the formula in the normal grid outside the Table
Formula returns a list but not sorted	SORT absent or wrong range used	Trace FILTER output, then apply SORT to that output
Formula returns three rows instead of two	UNIQUE applied to whole orders, not Product	Pass the intended comparison column
Formula returns #CALC! when nothing matches	Empty array has no fallback	Define [if_empty] deliberately

This table is a diagnostic decision aid, not a claim that each error has only one cause. In particular, an obstruction can be a formula whose displayed result looks blank. Check the cell contents. Avoid replacing #SPILL! with IFERROR(...,""): that would hide the failure while the downstream list silently disappears.

Question 4: What happens when FILTER finds no rows?

Try changing the criterion from East to North, which is absent from the four-row fixture. Microsoft's FILTER documentation says an empty result without an [if_empty] value can produce #CALC!, because Excel does not currently support an empty array result there. =FILTER(Orders[Product],Orders[Region]="North","No matches") instead returns a one-cell message. That message is a display fallback, not a product. If a downstream formula expects a numeric list, choose a fallback appropriate to that consumer rather than automatically inserting text.

An error inside the Boolean include array is different from no rows matching. The same Microsoft reference says errors or values that cannot be converted to Boolean in include cause FILTER to return an error. Audit criteria columns and source error cells before wrapping the entire expression in IFERROR. A blanket replacement could make a broken region column appear to have zero matching sales. For a reporting workflow, show a visible error or add a separate quality check so operational failures are not confused with legitimate empty results.

This is where the interview conversation should shift from function syntax to output contract. Does “no East orders” mean show an empty message, a zero count, or a zero-row table for another calculation? Excel's function limitation and the business presentation choice are separate. State both.

Question 5: Where should the source and formula live?

Microsoft recommends an Excel Table as a practical source for dynamic formulas because structured references expand as rows are added. It also says spilled-array formulas are not supported inside Excel Tables themselves; put the result formula in the worksheet grid outside the Table. In this exercise, new row O5 added to Orders extends Orders[Region] and Orders[Product] together, so the formula can include it without manually editing $C$2:$C$5. Leave enough free cells under the anchor for future products. If another report object occupies that region, place the list in a dedicated output sheet.

The anchor matters for maintenance. Only the top-left formula cell is edited; other cells in the spill range are generated. A formula using the spill reference operator, such as G2#, can refer to the current whole spill output where supported. If a dependent chart or validation list uses that result, test what happens when the spill grows, shrinks, is blocked, or returns a fallback string. Do not assume a previously observed two-row output is a fixed range.

One portability boundary is worth naming. Microsoft notes limited support for dynamic arrays linked across workbooks: both workbooks need to be open for that scenario, and closing the source may yield #REF! on refresh. That is different from #SPILL!, which concerns the destination range. If the interviewer hands you a workbook whose linked source is closed, inspect the reference and refresh behavior before clearing nearby cells. Product availability also varies by Excel version; verify that the target environment supports the functions you propose.

An interview answer and validation routine

“I would filter the Product column by East, take distinct product values with default UNIQUE, and sort that result. In the fixture, East gives Apple, Berry, Apple; distinct yields Apple, Berry. The formula belongs in one anchor cell outside the source Table and needs two free output cells today, potentially more later. If it shows #SPILL!, I would inspect the indicated output range for an obstruction or Table/merged-cell issue before changing the logic. If the region has no matches, I would use FILTER's [if_empty] according to the display contract and keep source errors visible.”

Then test four changes: reorder the source rows; add a new East product; remove every East order; place and remove a note in the expected second output cell. Those tests reveal whether sorting, structured-reference growth, empty handling, and spill recovery work. Record the expected array size at each step. A polished answer includes both the result and a way to falsify it.

If asked to count East orders by product, explain that the two-name list is only a dimension. You still need a counting formula or pivot over source orders. UNIQUE does not aggregate amounts, and a two-row product list alone does not prove East revenue. This avoids a common spreadsheet interview failure: presenting a clean-looking array whose numbers answer a different question from the one the analyst was asked.

Sources and further reading

• Microsoft Support: FILTER function — Boolean include arrays and empty-result behavior.
• Microsoft Support: UNIQUE function — distinct values versus exactly_once.
• Microsoft Support: SORT function — ordering a returned array.
• Microsoft Support: Dynamic-array and spill behavior — anchor cells, Tables, and linked workbooks.
• Microsoft Support: Correct a #SPILL! error — blockage, merged cells, edge, and indeterminate size.