Technology & IT
Advanced Excel Test: Online Practice
This quiz drills advanced Excel skills that determine whether your workbooks stay accurate under change: XLOOKUP/XMATCH patterns, dynamic array spills, and SUMPRODUCT-style multi-criteria logic. You’ll also face Power Query M and Power Pivot/DAX scenarios where context and refresh behavior matter. Expect edge cases around absolute references, dates, and structured table syntax.
Choose quiz length
1What function counts the number of cells in a range that meet a single criterion?
2Which function sums values in a range that meet a single criterion?
3Which function concatenates text from multiple cells and can use a delimiter?
4Which function returns the current date (without the time) in Excel?
5Which feature applies formatting rules to cells based on their values (e.g., color scales or icons)?
6Which keyboard shortcut inserts a new worksheet in an existing workbook (Windows Excel)?
7Your price list is sorted ascending by QuantityBreak. You want the price for the largest break less than or equal to the order quantity in E2. Which XLOOKUP match_mode fits best?
8You need to return the most recent status for an EmployeeID when the log table has multiple entries per ID (latest is at the bottom). Which XLOOKUP setting helps?
9You want a spilled list of all rows in Table1 where Sales > 1000. Which formula best fits?
10If a dynamic array formula spills into cells that already contain data, Excel will show a #SPILL! error until the blocking cells are cleared.
True / False
11You need to sum Quantity for rows where Region="West" and Sales>5000. Quantity is in C2:C100, Region in A2:A100, Sales in B2:B100. Which formula works without helper columns?
12In Power Query M, which expression filters a table named Source to only rows with [Status] = "Active"?
13You have a Sales table related to a Date table. You need a measure for Sales Amount in 2023 regardless of the current report filters on Date. Which DAX pattern best fits?
14A user reports that a DAX measure returns the same value for every product in a matrix. Which issue is the most likely cause?
15Creating proper relationships in the Data Model can improve PivotTable reporting performance compared with using repeated VLOOKUPs across large tables.
True / False
16You need a case-sensitive lookup of the text in A2 against a list in D2:D200 and return the corresponding value from E2:E200. Which approach is most appropriate?
17A measure should compute Year-to-Date Sales but must ignore any filters on the Product table while still respecting Date filters. Which DAX pattern best matches?
Frequent Advanced Excel Errors in Lookups, Spill Formulas, Power Query, and DAX
Modern lookup pitfalls (XLOOKUP/XMATCH)
- Wrong match_mode assumptions: XLOOKUP defaults to exact match, but many users accidentally select approximate behavior (or forget it’s required for sorted-data approximations). Avoid: set
match_modedeliberately (0 exact, -1/1 next smaller/larger, 2 wildcard) and document the expectation in a nearby note cell. - Returning the wrong shape: returning an entire column when you need a single value can quietly spill a column of results. Avoid: confirm whether you want a scalar result or an array; wrap with
INDEXor constrain the return range when needed.
Dynamic arrays and #SPILL! misunderstandings
- Blocked spill ranges: a single value in the spill area causes
#SPILL!. Avoid: clear the spill range, convert nearby data to a table, or place spill formulas in a dedicated output area. - Unstable references: copying spill formulas without anchoring inputs (or using structured references) makes results drift. Avoid: use
$anchors appropriately or table references likeSales[Amount].
SUMPRODUCT logic errors
- Boolean math mistakes: mixing
*(AND) and+(OR) without parentheses changes meaning. Avoid: explicitly group conditions, e.g.,((Region="West")*(Sales>5000)). - Text/number mismatches: criteria like
"5000"(text) against numeric columns can yield zeros. Avoid: enforce types; useVALUEor fix source data.
Power Query and Data Model gotchas
- Confusing UI steps with M: editing M without understanding step dependencies breaks refresh. Avoid: rename steps clearly and reference the prior step consistently.
- DAX context confusion: expecting a measure to behave like a row-by-row Excel formula. Avoid: learn the difference between row context and filter context, and use
CALCULATEonly when you intend to modify filter context.
Advanced Excel Practice Takeaways: What to Get Right Under Time Pressure
- Choose match behavior explicitly in XLOOKUP/XMATCH: default exact match is safe, but wildcard and approximate modes must be intentional and paired with the right data ordering.
- Design for spill outputs: treat dynamic arrays as report generators; reserve clean output space and reference results with
A1#when downstream formulas should resize automatically. - Prefer structured references for models that grow: formulas tied to tables (e.g.,
Orders[Qty]) survive inserts, deletes, and refreshes far better than fixed ranges. - Build multi-criteria calculations with readable boolean algebra: in
SUMPRODUCT, use parentheses to make AND/OR logic unambiguous and validate each condition separately. - Separate transformation from calculation: use Power Query to clean/reshape (types, filters, grouping), then use Excel formulas or DAX for measures—this reduces fragile, over-nested worksheet logic.
Advanced Excel Glossary: Spill Ranges, Structured References, and Context
- XLOOKUP
- Lookup function that returns a matching value (or array) from a return range. Example:
=XLOOKUP(A2,Products[ID],Products[Name],"Not Found",0). - XMATCH
- Returns the relative position of an item in a range/array, often paired with
INDEX. Example:=XMATCH("SKU-14",Products[ID],0). - Dynamic array (spill)
- A formula that returns multiple results which “spill” into adjacent cells. Example:
=FILTER(Sales,Sales[Amount]>1000)produces a resized output table. - Spill range reference (A1#)
- Shorthand that refers to the entire spilled output starting at a cell. Example: if
F2contains a spill formula,=ROWS(F2#)counts its current rows. - Structured reference
- Table-based reference that stays aligned with column names and automatically expands. Example:
=SUM(Orders[Total])sums the entireTotalcolumn in theOrderstable. - Power Query step
- A named transformation in a query’s applied steps; each step typically references the previous step. Example: filtering to active rows might be expressed as
Table.SelectRows(PreviousStep, each [Status]="Active"). - Filter context (DAX)
- The set of filters (slicers, rows/columns in a pivot, CALCULATE modifiers) that determines what rows a DAX measure evaluates. Example: a revenue measure changes when a Year slicer is applied.
- CALCULATE (DAX)
- A DAX function that evaluates an expression under a modified filter context. Example:
=CALCULATE([Revenue], 'Date'[Year]=2023)forces a year filter for that measure.
Authoritative References for Advanced Excel Functions, Power Query M, and DAX
- XLOOKUP function (Microsoft Support)Syntax, match modes (exact/approx/wildcard), search direction, and return-array behavior.
- XMATCH function (Microsoft Support)Position-based lookup for modern INDEX/XMATCH patterns and advanced search modes.
- FILTER function (Microsoft Support)Core dynamic array filtering behavior, including spill outputs and common constraints.
- Power Query M formula language reference (Microsoft Learn)Official entry point to M syntax, operators, and table/list/record functions used in transformations.
- CALCULATE function (DAX) (Microsoft Learn)Definitive reference for how CALCULATE modifies filter context in Power Pivot and other tabular models.
Advanced Excel Practice FAQ: Lookups, Spill Formulas, Power Query, and DAX
When should I use XLOOKUP versus XMATCH + INDEX?
Use XLOOKUP when you want a direct “find-and-return” pattern and may need built-in not-found handling or flexible search direction. Use XMATCH + INDEX when you want to separate “where is it?” from “what do I return?”, especially in models where multiple returns share the same position logic or where you’re building more complex retrieval patterns.
Why does a dynamic array formula work in one file but show #SPILL! in another?
#SPILL! most often means Excel can’t place the full result because cells in the spill area are not empty. It can also happen when a spill formula is placed where the output would collide with a table, merged cells, or other blocked ranges. A reliable workflow is to dedicate a clear output area and reference the result with A1# so downstream formulas resize automatically.
What’s the fastest way to debug a multi-criteria SUMPRODUCT that returns 0?
Check three things in order: (1) each criterion returns the expected TRUE/FALSE array (evaluate them one at a time), (2) data types match (numbers stored as text are a common cause), and (3) your AND/OR logic is parenthesized correctly. If you intend OR logic, use + with parentheses; for AND logic, use *.
In Power Query, when is it worth writing M instead of using the UI?
Write or edit M when you need a transformation the UI can’t express cleanly (custom functions, reusable patterns, parameter-driven logic) or when you need to make a step more robust (for example, selecting columns by name lists that may change). Even if you start in the UI, learning to read step-by-step M helps you predict refresh failures before they happen.
Why does a DAX measure with CALCULATE feel “inconsistent” compared with Excel formulas?
DAX measures are evaluated under filter context that changes with pivot rows/columns, slicers, and relationships in the Data Model—so the “same” measure can legitimately return different numbers in different visual contexts. CALCULATE is powerful because it can modify that context, but it also makes debugging harder if you’re not explicit about which filters you’re adding, keeping, or overriding. If you want to strengthen your step-by-step reasoning for complex logic, the Computational Thinking Test - Free Online Assessment pairs well with advanced formula work.
