Excel Interview Questions

Sample Answer

Use =XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,, -1) to return the rate for the largest tier that is less than or equal to the revenue. You lock the tier ranges with absolute references so the lookup table does not drift when you fill down. The -1 match_mode forces an exact or next smaller match, which is the right logic for tiered thresholds. If XLOOKUP is not available, use =VLOOKUP(D2,$A$2:$B$6,2,TRUE).

Sample Answer

You could do it with nested IFs, or you could use boolean arithmetic with guards. Nested IF wins here because you must explicitly handle blanks before comparing dates, otherwise blanks can behave like 0 and create false on time flags. Use =IF(E2="","",IF(E2<=F2,1,0)). When you fill down, keep the references relative because each row compares its own dates.

Sample Answer

You want each grid cell to temporarily point the model inputs to the row and column headers. In B3, set growth to the column header with an anchored row: =$B$30 after you first link inputs, specifically set B20 to =B$2 and B21 to =$A3 in the table context. Step by step: in B3 you reference the output cell, typically =$B$30, because the output should always pull from the model result. Then make sure the model input cells use mixed references to headers when filled, growth from B$2 so the row stays 2 as you fill down, discount from $A3 so column stays A as you fill across. Copy B3 across and down, the mixed references ensure each cell uses its own growth and discount pair.

Sample Answer

You could do XLOOKUP with search_mode for last-to-first, or you could do INDEX MATCH with a MAX date helper. XLOOKUP wins here because you can target the last occurrence directly if Ratings are sorted by date, for example `=XLOOKUP([@AccountID],Ratings[AccountID],Ratings[RiskRating],,0,-1)`. If you cannot rely on sort order, use a two-step: compute the max date per AccountID with `MAXIFS`, then return the rating with `INDEX` plus `MATCH(1,(AccountID=id)*(Date=maxdate),0)` or XLOOKUP on a concatenated key. Either way, you are explicitly defining "latest" so duplicates do not create random picks.

Sample Answer

First, you run the lookup and flag misses with the not_found argument, then count them: `=IFNA(XLOOKUP([@ProductID],Master[ProductID],Master[Category]),"MISSING")` and `=COUNTIF(ResultCol,"MISSING")`. Next, you detect duplicate keys in the master with `=COUNTIF(Master[ProductID],[@ProductID])` or a pivot to count ProductID occurrences, anything greater than 1 is a one-to-many risk. Then you quantify dollars affected by summing Sales where the result is missing or where the key is duplicated, for example with `SUMIFS(Sales[Revenue],ResultCol,"MISSING")` and `SUMIFS(Sales[Revenue],DupCountCol,">1")`. Finally, you decide the remediation: fix master data, or define a deterministic rule for which duplicate row you will return.

Sample Answer

First I normalize whitespace so the parsing is stable, I would use `TRIM` and also remove non breaking spaces with `SUBSTITUTE(A2,CHAR(160)," ")`. Next I branch on whether there is a comma using `IF(ISNUMBER(SEARCH(",",A2)),...)`, because that tells me which pattern I am in. For the comma case I take last name as `TEXTBEFORE(A2,",")` and the rest as `TRIM(TEXTAFTER(A2,","))`, then split first and middle with `TEXTBEFORE` and `TEXTAFTER` on the first space. For the no comma case I treat the last token as last name using `TEXTAFTER(A2," ",-1)`, take first name as `TEXTBEFORE(A2," ")`, and middle initial as the remaining token if it exists, then rebuild with `=Last&", "&First&IF(MI<>""," "&MI&".","")`.

Sample Answer

This question is checking whether you can identify non obvious data quality issues that break joins and deduping, and fix them without introducing new errors. You diagnose by comparing `LEN` versus expected length, using `CODE(MID(A2,n,1))` on suspicious rows, and checking for leading or trailing whitespace that `TRIM` may not remove. Then you clean with a robust sequence like `=CLEAN(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),""),CHAR(9),""))` and wrap with `TRIM` if spaces matter. Finally you validate by re running the join, confirming match rate, and only then dedupe using the cleaned key, not the raw key.

Sample Answer

This question is checking whether you can avoid aggregation traps when the grain is not one row per order. You cannot just put Revenue in Values and set it to Average, because that averages line items, not orders. The correct move is to first compute order level totals, either by creating a helper table with a pivot that sums Revenue by OrderID and Channel and Month, or by adding the data to the Data Model and using a DAX measure like $$AOV = \frac{\text{SUM}(Revenue)}{\text{DISTINCTCOUNT}(OrderID)}$$. Then you pivot by Channel and Month using that measure so each order contributes once to the denominator.

Sample Answer

The standard move is Count when each row is already a unique customer, so counting rows equals counting customers. But here, Distinct Count matters because duplicated customer IDs across multiple transactions will inflate a simple Count. Rule of thumb: if the field you are counting is not unique at the row level, use Distinct Count via the Data Model, or pre-aggregate to customer level first. To reconcile, you define the metric in plain language, for example, "unique customers who transacted," then you show a quick audit by filtering to one segment and comparing Count vs DISTINCTCOUNT(CustomerID) and inspecting duplicate IDs via drilldown.

Sample Answer

The standard move is to build one clean NPV output cell that depends only on two input cells, discount rate and terminal growth, then point the data table to that output. But here, calculation dependencies matter because Data Tables only vary one row input cell and one column input cell, and they can break if your model uses volatile references or multiple discount rate cells. Put discount rate and growth in dedicated single cells, reference them everywhere, and keep the NPV in one cell, for example $$\text{NPV} = \sum_{t=1}^{T} \frac{FCF_t}{(1+r)^t} + \frac{FCF_{T+1}}{(r-g)(1+r)^T}$$. Then select the grid, run Data Table with Row input as the growth cell and Column input as the discount rate cell, and set calculation to Automatic except data tables if performance becomes an issue.

Sample Answer

Get this wrong in production and the business approves a price point that loses money, or rejects one that would have been profitable. The right call is to first trace units end to end, label every assumption with its unit, and convert everything to a single time basis, for example all monthly, before you touch any logic. Next, use Evaluate Formula, Trace Precedents, and Find to locate hardcoded numbers, pull them into an Inputs table, and replace constants with cell references. Finally, add a scenario switch that drives those inputs, plus a simple check section, for example reconciling revenue, costs, and margin and flagging when implied volumes or rates are out of bounds.

Sample Answer

Get this wrong in production and your file becomes unusable, leading people to export to CSV, break governance, and create conflicting numbers. The right call is to identify the bottleneck first: check calculation mode, use Evaluate Formula for hotspots, inspect conditional formatting bloat, and look for volatile functions like OFFSET, INDIRECT, TODAY, NOW, RAND. Then reduce the calc surface area by converting ranges to Tables, replacing whole-column references with structured references, caching intermediate results in helper columns, and pushing heavy joins and reshapes into Power Query. Finally, avoid loading raw data to sheets when you can load to the Data Model, and limit Pivot caches and slicers to what is necessary.

Sample Answer

A formula approach sounds reasonable but breaks under scale, because nested XLOOKUP logic across large tables can create heavy recalcs and fragile edge-case handling. Pure dynamic array stacking also does not work cleanly when you need deterministic join precedence and auditability across refreshes. That leaves Power Query: create a primary merge on ISIN, then for null matches perform a second merge on CUSIP and coalesce the fields, keeping the steps documented in the query. You end up with a single refreshable pipeline, and the logic is visible and testable in the Applied Steps.