Advanced Excel Test: Online Practice
Advanced Excel models fail in subtle ways when lookup settings, spill outputs, or criteria logic change after sorting, refresh, or added rows. This practice focuses on XLOOKUP/XMATCH match behavior, dynamic array spill control, and SUMPRODUCT-style boolean algebra, plus Power Query M and Power Pivot/DAX context rules. Expect edge cases with dates, structured references, and absolute vs. relative anchors.
Choose quiz length
1A reference like F2# always refers to the entire current spill range that begins in F2.
True / False
2XLOOKUP uses exact match by default when match_mode is omitted.
True / False
3In SUMPRODUCT, multiplying boolean tests (for example, (A=1)*(B=2)) implements AND logic.
True / False
4Which function commonly returns a dynamic array that spills results into neighboring cells?
5In XLOOKUP, which match_mode value enables wildcard matching (for example, looking up "ACME*" )?
6In Power Query, what does the Applied Steps pane represent?
7When match_mode is 0, XLOOKUP requires the lookup_array to be sorted.
True / False
8Cell F2 contains =FILTER(Sales,Sales[Amount]>1000) and spills a variable number of rows. Which formula best sums the spilled Amount column so it updates automatically when the spill size changes?
9You have same-sized named ranges Region and Amount. Which formula counts rows where (Region is West OR East) AND Amount>5000?
10Arrange the steps to build a robust exact-match lookup using INDEX + XMATCH with a friendly "not found" result.
Put in order
1Use INDEX with that position to return the value
2Use XMATCH with match_mode 0 to get the position
3Wrap the final formula in IFNA/IFERROR to handle missing items
4Define the lookup value
11Changing the name of a Power Query step never affects refresh because Power Query tracks steps only by position.
True / False
12You need a measure that ignores any Product filters but still respects Date and Region filters:
13Your PriceBreaks[Qty] is sorted ascending. To return the price for the largest Qty that is less than or equal to the requested quantity, which XLOOKUP match_mode should you use?
14Arrange a typical Power Query transformation flow to reduce refresh issues (from earliest to latest).
Put in order
1Load to worksheet or Data Model
2Connect to the source
3Filter/clean rows
4Set data types
5Promote headers (if needed)
6Group/aggregate (if needed)
15Arrange these actions to troubleshoot a #SPILL! error caused by blocked spill cells.
Put in order
1Recalculate/confirm the spill now expands
2Identify the highlighted spill range
3Ensure the spill isn’t trying to expand inside an Excel Table column
4Select the cell showing #SPILL!
5Clear or move any values/merged cells in the spill area
16Your SUMPRODUCT returns 0 because Amount is numeric but your criterion is "5000" stored as text. Which fix is best?
17In M, you want a new step to start from the previous step named Changed Type. Which expression correctly references that step?
18Arrange the conceptual order of how a DAX measure inside CALCULATE is evaluated in a Pivot/visual.
Put in order
1The visual provides an initial filter context (from rows/columns/slicers)
2The resulting scalar value is returned to the visual cell
3The expression inside CALCULATE is evaluated under the modified filter context
4CALCULATE evaluates its filter arguments
5CALCULATE modifies the filter context based on those arguments
19Arrange a practical sequence to debug a complex SUMPRODUCT with multiple criteria.
Put in order
1Wrap with SUMPRODUCT (and optionally -- to coerce) to get the final total
2Test each condition separately to confirm it returns TRUE/FALSE as expected
3Combine conditions into a single boolean expression
4Fix data type issues (numbers as text, dates as text)
5Add parentheses to make AND/OR grouping explicit
20You want a measure that shows Sales divided by total Sales for the same Product and Date, but the denominator must ignore Region filters. Which DAX pattern matches that requirement?
21In Power Query, you rename an Applied Step but later steps still reference the old step name. What practice best prevents refresh errors?
22Your formula returns multiple columns and spills because return_array is Customers[[Name]:[Phone]], but you only need the Phone value. What change is best?
23A DAX measure is evaluated once per row like an Excel formula in a calculated column.
True / False
Advanced Excel Errors That Break Workbooks Under Change (and How to Prevent Them)
Most “advanced Excel” failures aren’t syntax errors—they’re silent logic shifts that show up after data grows, refreshes, or gets re-sorted. Use the patterns below as a checklist while you review your answers.
XLOOKUP/XMATCH: match behavior and shape control
- Unstated match assumptions: using approximate or wildcard matching without confirming sort order and intent. Avoid: choose
match_modedeliberately and document whether you expect exact, wildcard, or nearest-match behavior. - Return array dimensions drift: lookup arrays and return arrays don’t align (different heights/widths), or you return a multi-column range and trigger an unexpected spill. Avoid: validate ranges are the same shape; constrain the return range or wrap with
INDEXwhen you need a scalar.
Dynamic arrays: spill hygiene and downstream references
- #SPILL! due to blocked output: a single value, merged cell, or table boundary blocks the spill range. Avoid: reserve a dedicated output area; don’t place spill formulas inside tables unless you intend structured expansion.
- Copying formulas without stable inputs: relative references shift across copies and your spill changes meaning. Avoid: anchor with
$where appropriate or use structured references (e.g.,Sales[Amount]).
SUMPRODUCT-style multi-criteria logic: boolean algebra mistakes
- AND/OR precedence bugs: mixing
*(AND) and+(OR) without parentheses changes the filter logic. Avoid: group each condition explicitly, then combine. - Type mismatches: criteria stored as text ("5000") compared to numbers yields unexpected zeros. Avoid: standardize types at the source or coerce intentionally (e.g.,
VALUE()).
Power Query + Power Pivot/DAX: refresh and context surprises
- Breaking M step dependencies: editing a step name or referencing the wrong prior step causes refresh failures. Avoid: rename steps meaningfully and confirm each step’s input/output.
- Confusing measures with row-by-row formulas: a DAX measure evaluates in filter context, not per-row like an Excel formula. Avoid: decide whether the problem requires a calculated column (row context) or a measure (aggregation under filters).
Authoritative References for XLOOKUP, Dynamic Arrays, SUMPRODUCT, Power Query M, and DAX
Use these primary references to verify function signatures, edge-case behavior, and the evaluation rules that show up in advanced scenarios.
- XLOOKUP function (Microsoft Support) — Official syntax and examples, including
match_mode/search_modebehaviors that commonly drive wrong-match results. ([support.microsoft.com](https://support.microsoft.com/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929?utm_source=openai)) - Dynamic array formulas and spilled array behavior (Microsoft Support) — How spills work, what triggers
#SPILL!, and how spill ranges behave when referenced downstream. ([support.microsoft.com](https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531?utm_source=openai)) - SUMPRODUCT function (Microsoft Support) — Definition, syntax, and performance considerations (including why full-column references can become expensive). ([support.microsoft.com](https://support.microsoft.com/en-gb/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e?utm_source=openai))
- Power Query M formula language reference (Microsoft Learn) — Entry point for M language concepts and function reference used when you go beyond the UI steps. ([learn.microsoft.com](https://learn.microsoft.com/ms-my/powerquery-m/?utm_source=openai))
- CALCULATE function (DAX) (Microsoft Learn) — Core reference for how
CALCULATEmodifies filter context (a frequent source of “why did my measure change?” mistakes). ([learn.microsoft.com](https://learn.microsoft.com/en-us/dax/calculate-function-dax?utm_source=openai))
Advanced Excel Practice FAQ: Spills, Lookups, SUMPRODUCT Logic, and Data Model Context
When should I prefer XLOOKUP over INDEX+XMATCH in advanced models?
Use XLOOKUP when you want a readable, single-function lookup with explicit not-found handling and controlled match/search modes. Use INDEX+XMATCH when you need a two-way lookup (row and column), or when separating “find position” from “return value” makes the model easier to audit and test.
Why did my XLOOKUP “suddenly spill” into multiple cells?
A spill usually means your return_array is more than one cell wide or tall, so Excel correctly returns an array instead of a scalar. If you only want one value, constrain the return range to a single column/row, or wrap the result with INDEX( … , 1) (or a specific row/column) to force a single-cell output.
What’s the fastest way to diagnose a #SPILL! error?
First, select the formula cell and look for the outlined spill area; something inside that area is blocking the output (a value, a merged cell, or a table boundary). Clear the obstruction or relocate the formula to a clean output zone. If the spill is intentional, reference it downstream with the spill operator (e.g., A1#) so dependent formulas resize automatically.
My SUMPRODUCT multi-criteria formula returns 0, but I’m sure matches exist—what should I check?
Check data types first: a numeric column compared to a text criterion (or vice versa) often yields no matches. Next, check parentheses: * (AND) and + (OR) need explicit grouping to reflect your intended logic. Finally, validate each condition separately (return the boolean array) before multiplying conditions together.
Why do DAX measures behave differently from Excel formulas in tables?
Excel formulas in a row behave like “calculate for this row.” A DAX measure is evaluated under the current filter context (slicers, rows/columns in a PivotTable, report filters), so the same measure can return different results depending on where it’s placed. Treat measures as reusable aggregations whose inputs are defined by the current filters, not by a single row.
Power Query refresh changed my results—what are the usual root causes?
Most refresh surprises come from type changes (text vs number vs date), locale-dependent parsing (especially dates), or a step order change that affects grouping/filtering. Review the Applied Steps from top to bottom and confirm the column types after merges, appends, and custom columns. If a step references an earlier step by name, renaming or deleting steps can also alter behavior.
What to Master for Reliable Advanced Excel Workbooks
- Declare lookup intent: choose XLOOKUP/XMATCH match and search modes intentionally, and design your lookup arrays so exact vs. nearest-match can’t be confused.
- Control output shape: know when a formula returns a scalar vs. an array, and constrain the return range (or use
INDEX) when a spill would be unsafe. - Design for spills: reserve clean output space for dynamic arrays and reference spill results with
#so downstream calculations resize automatically. - Write readable boolean math: in SUMPRODUCT-style logic, parenthesize every condition and validate each boolean array before combining AND/OR logic.
- Separate transform from calc: use Power Query for repeatable shaping/typing, and use DAX measures for filter-aware aggregation—don’t mix roles casually.
Advanced Excel Terms Used in This Practice (with Mini Examples)
- Spill range
- The set of cells populated by a dynamic array formula. Example: if
B2contains a formula that spills, reference the full result withB2#. - #SPILL!
- An error indicating Excel can’t place the spilled results. Example: a value in the intended spill area blocks
=SORT(A2:A100)from expanding. - match_mode (XLOOKUP/XMATCH)
- An argument controlling exact vs. approximate vs. wildcard matching. Example: use exact match when looking up IDs; use wildcard only when the lookup value intentionally contains patterns.
- Return array (XLOOKUP)
- The range/array returned from a match. Example: returning a 5-column range produces a 5-column spill; returning a single column produces a single-column result.
- Structured reference
- A table-based reference that expands with new rows. Example:
Orders[Amount]remains correct after inserts that would shift$E$2:$E$500. - Boolean coercion
- Converting TRUE/FALSE arrays into 1/0 for arithmetic filtering. Example: in SUMPRODUCT logic,
(Region="West")*(Sales>5000)multiplies two 1/0 masks. - Applied Steps (Power Query)
- The ordered transformations that define a query. Example: changing the order of “Changed Type” and “Removed Errors” can alter which rows survive refresh.
- Filter context (DAX)
- The active set of filters affecting a measure’s evaluation. Example: a Total Sales measure returns different values by Year in a PivotTable because each row imposes a different filter context.
