Microsoft Excel Advanced 2025 — 55 Q&A Data Analysis and
Modelling Verified
Series:
CrashCourses Professional Study Series
Author:
Dr Z. Moomba, MBChB, MRCPsych | BethelWellness Ltd
Exam Target:
Microsoft Excel MO-201
Year:
2025/2026
Format:
55 Questions with Verified Answers and Rationales
>
Author's Note:
This document is an original work produced for the CrashCourses Professional Study Series.
Clinical questions and professional scenarios were composed by Dr Z. Moomba based on current
exam objectives, published guidelines, and evidence-based sources (2024–2025). All patient
names, ages, and case details are fictional. Any resemblance to existing published Q&A banks is
coincidental. For personal study use only — not for reproduction or redistribution.
PART 1: CORE PRINCIPLES & FOUNDATIONS
Question 1
A public health analyst at a regional trust is parsing a registry of 6,200 outpatient encounter
records. He requires a self-updating, alphabetically ordered array of distinct ICD-10 codes located
in column C, strictly using formulaic extraction rather than a PivotTable. Which Excel 2025
dynamic array formula correctly accomplishes this?
A. `=SORT(UNIQUE(C2:C6200))`
B. `=UNIQUE(SORT(C2:C6200))`
C. `=FILTER(C2:C6200, UNIQUE(C2:C6200))`
D. `=INDEX(C2:C6200, MATCH(UNIQUE(C2:C6200)))`
,Answer: A
Rationale:
Lead: The UNIQUE function isolates distinct text strings from a given array, while wrapping it in
SORT automatically orders the resulting spilled array in ascending alphabetical order.
Discriminator: Nesting UNIQUE inside of SORT is computationally optimal, as the sorting
engine only processes the deduplicated subset rather than the entire raw dataset.
Distractor: Option B executes successfully but forces the engine to sort all 6,200 rows before
stripping duplicates, consuming unnecessary memory.
Examiner Insight: Dynamic array functions eliminate legacy Ctrl+Shift+Enter (CSE) array
constraints and automatically adapt their spill range as underlying data changes. [Microsoft
Learn: Dynamic Arrays 2025]
Question 2
A dispensary pharmacist is building a real-time tracking sheet. She uses XLOOKUP to locate the
current inventory of "Paracetamol 1g IV" (referenced in cell G4) against a formulary list in
`A2:A200`, returning the quantity from `D2:D200`. If the drug is absent from the formulary, the cell
must display "Unlisted". Which syntax is accurate?
A. `=XLOOKUP(G4, A2:A200, D2:D200, "Unlisted", 0)`
B. `=XLOOKUP(G4, D2:D200, A2:A200, "Unlisted")`
C. `=VLOOKUP(G4, A2:D200, 4, FALSE, "Unlisted")`
D. `=XLOOKUP(G4, A2:A200, D2:D200, 0, "Unlisted")`
Answer: A
Rationale:
Lead: XLOOKUP syntax requires `(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])`. Option A maps these parameters flawlessly.
Discriminator: The built-in fourth argument natively handles missing data, removing the need to
wrap the formula in an `IFERROR` statement.
Distractor: Option C attempts to use a fifth argument in VLOOKUP, which does not exist;
VLOOKUP strictly requires an external error handler for missing matches.
High-Yield Point: XLOOKUP defaults to an exact match (`0`), unlike legacy lookup functions
that default to approximate matches, thereby reducing clinical mismatch errors. [MO-201 Exam
Objectives 2025]
,Question 3
A senior matron needs to dynamically generate a roster of patients (`A2:A80`) currently housed in
the "Neurology" department (`B2:B80`) whose length of admission exceeds 7 days (`C2:C80`).
Which dynamic array formula will correctly filter this dual-condition logic?
A. `=FILTER(A2:A80, (B2:B80="Neurology") + (C2:C80>7))`
B. `=FILTER(A2:A80, (B2:B80="Neurology") * (C2:C80>7))`
C. `=FILTER(A2:A80, AND(B2:B80="Neurology", C2:C80>7))`
D. `=UNIQUE(A2:A80, (B2:B80="Neurology") * (C2:C80>7))`
Answer: B
Rationale:
Lead: In Excel's dynamic array boolean logic, multiplying two arrays with an asterisk (`*`)
functions as an AND operator, evaluating to 1 (TRUE) only when both row conditions are met.
Discriminator: The FILTER function natively understands these boolean arrays, extracting only
the rows that correspond to a TRUE outcome.
Distractor: Option C looks correct to legacy users, but the standard `AND()` function
aggregates arrays into a single boolean value, breaking the row-by-row array evaluation
required by FILTER.
Clinical Pearl: Using the plus sign (`+`) in array math produces an OR logical test, which would
erroneously include non-neurology patients who stayed over 7 days. [Microsoft Excel Formulas
& Functions 2025]
Question 4
While constructing a 14-day medication schedule, a clinician enters `=SEQUENCE(14, 1)` into a
cell, but Excel immediately returns a `#SPILL!` error. The syntax itself is flawless. What is the
fundamental cause of this obstruction?
A. The SEQUENCE function mandates a minimum of three arguments to execute properly.
B. The file was saved in an older compatibility mode (.xls) which rejects arrays.
C. A non-empty cell (such as hidden text or a space) exists within the target expansion range.
D. The user forgot to finalize the formula entry with Ctrl+Shift+Enter.
Answer: C
Rationale:
, Lead: The `#SPILL!` error triggers defensively when a dynamic array attempts to populate a
range of cells, but one or more of the required destination cells already contains data.
Discriminator: Excel refuses to overwrite existing grid data with a spilled array, forcing the user
to clear the blocking content before the formula will manifest.
Distractor: Option B is incorrect because older Excel versions do not throw `#SPILL!` errors;
they typically return an `_xlfn.` prefix or `#NAME?` error.
Key Principle: Even a cell containing a single invisible space character will block an array from
spilling, requiring clearing of formats and contents to resolve. [Excel Support Documentation
2025]
Question 5
A triage nurse uses a spreadsheet to categorize patients by their Glasgow Coma Scale (GCS) in
cell E4. A score of 15 is "Normal", 13-14 is "Mild", and <13 is "Severe". Which modern logical
formula is the cleanest method to evaluate these sequential thresholds without complex nesting?
A. `=SWITCH(E4, =15, "Normal", >=13, "Mild", "Severe")`
B. `=IFS(E4=15, "Normal", E4>=13, "Mild", E4<13, "Severe")`
C. `=IF(E4=15, "Normal", IF(E4<13, "Severe", "Mild"))`
D. `=CHOOSE(E4, "Severe", "Mild", "Normal")`
Answer: B
Rationale:
Lead: The IFS function is specifically engineered to test multiple conditions sequentially,
returning the value corresponding to the very first TRUE statement encountered.
Discriminator: It allows for clear, left-to-right reading of multiple logical tests (`E4>=13`, etc.)
without the confusing parenthesis depth of nested IFs.
Distractor: Option A fails because the SWITCH function requires exact value matches (e.g.,
matching the literal number 15) and cannot process logical operators like `>=` within its
arguments.
Examiner Insight: Because IFS stops at the first TRUE condition, ordering your clinical
thresholds correctly (e.g., evaluating the highest or lowest severity first) is paramount to
prevent misclassification. [MO-201 Guidelines 2025]
Question 6
An informatics lead is configuring conditional formatting to monitor sepsis bundle compliance
times. They require distinct visual markers in the cell: a green checkmark for completion under 1
Modelling Verified
Series:
CrashCourses Professional Study Series
Author:
Dr Z. Moomba, MBChB, MRCPsych | BethelWellness Ltd
Exam Target:
Microsoft Excel MO-201
Year:
2025/2026
Format:
55 Questions with Verified Answers and Rationales
>
Author's Note:
This document is an original work produced for the CrashCourses Professional Study Series.
Clinical questions and professional scenarios were composed by Dr Z. Moomba based on current
exam objectives, published guidelines, and evidence-based sources (2024–2025). All patient
names, ages, and case details are fictional. Any resemblance to existing published Q&A banks is
coincidental. For personal study use only — not for reproduction or redistribution.
PART 1: CORE PRINCIPLES & FOUNDATIONS
Question 1
A public health analyst at a regional trust is parsing a registry of 6,200 outpatient encounter
records. He requires a self-updating, alphabetically ordered array of distinct ICD-10 codes located
in column C, strictly using formulaic extraction rather than a PivotTable. Which Excel 2025
dynamic array formula correctly accomplishes this?
A. `=SORT(UNIQUE(C2:C6200))`
B. `=UNIQUE(SORT(C2:C6200))`
C. `=FILTER(C2:C6200, UNIQUE(C2:C6200))`
D. `=INDEX(C2:C6200, MATCH(UNIQUE(C2:C6200)))`
,Answer: A
Rationale:
Lead: The UNIQUE function isolates distinct text strings from a given array, while wrapping it in
SORT automatically orders the resulting spilled array in ascending alphabetical order.
Discriminator: Nesting UNIQUE inside of SORT is computationally optimal, as the sorting
engine only processes the deduplicated subset rather than the entire raw dataset.
Distractor: Option B executes successfully but forces the engine to sort all 6,200 rows before
stripping duplicates, consuming unnecessary memory.
Examiner Insight: Dynamic array functions eliminate legacy Ctrl+Shift+Enter (CSE) array
constraints and automatically adapt their spill range as underlying data changes. [Microsoft
Learn: Dynamic Arrays 2025]
Question 2
A dispensary pharmacist is building a real-time tracking sheet. She uses XLOOKUP to locate the
current inventory of "Paracetamol 1g IV" (referenced in cell G4) against a formulary list in
`A2:A200`, returning the quantity from `D2:D200`. If the drug is absent from the formulary, the cell
must display "Unlisted". Which syntax is accurate?
A. `=XLOOKUP(G4, A2:A200, D2:D200, "Unlisted", 0)`
B. `=XLOOKUP(G4, D2:D200, A2:A200, "Unlisted")`
C. `=VLOOKUP(G4, A2:D200, 4, FALSE, "Unlisted")`
D. `=XLOOKUP(G4, A2:A200, D2:D200, 0, "Unlisted")`
Answer: A
Rationale:
Lead: XLOOKUP syntax requires `(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])`. Option A maps these parameters flawlessly.
Discriminator: The built-in fourth argument natively handles missing data, removing the need to
wrap the formula in an `IFERROR` statement.
Distractor: Option C attempts to use a fifth argument in VLOOKUP, which does not exist;
VLOOKUP strictly requires an external error handler for missing matches.
High-Yield Point: XLOOKUP defaults to an exact match (`0`), unlike legacy lookup functions
that default to approximate matches, thereby reducing clinical mismatch errors. [MO-201 Exam
Objectives 2025]
,Question 3
A senior matron needs to dynamically generate a roster of patients (`A2:A80`) currently housed in
the "Neurology" department (`B2:B80`) whose length of admission exceeds 7 days (`C2:C80`).
Which dynamic array formula will correctly filter this dual-condition logic?
A. `=FILTER(A2:A80, (B2:B80="Neurology") + (C2:C80>7))`
B. `=FILTER(A2:A80, (B2:B80="Neurology") * (C2:C80>7))`
C. `=FILTER(A2:A80, AND(B2:B80="Neurology", C2:C80>7))`
D. `=UNIQUE(A2:A80, (B2:B80="Neurology") * (C2:C80>7))`
Answer: B
Rationale:
Lead: In Excel's dynamic array boolean logic, multiplying two arrays with an asterisk (`*`)
functions as an AND operator, evaluating to 1 (TRUE) only when both row conditions are met.
Discriminator: The FILTER function natively understands these boolean arrays, extracting only
the rows that correspond to a TRUE outcome.
Distractor: Option C looks correct to legacy users, but the standard `AND()` function
aggregates arrays into a single boolean value, breaking the row-by-row array evaluation
required by FILTER.
Clinical Pearl: Using the plus sign (`+`) in array math produces an OR logical test, which would
erroneously include non-neurology patients who stayed over 7 days. [Microsoft Excel Formulas
& Functions 2025]
Question 4
While constructing a 14-day medication schedule, a clinician enters `=SEQUENCE(14, 1)` into a
cell, but Excel immediately returns a `#SPILL!` error. The syntax itself is flawless. What is the
fundamental cause of this obstruction?
A. The SEQUENCE function mandates a minimum of three arguments to execute properly.
B. The file was saved in an older compatibility mode (.xls) which rejects arrays.
C. A non-empty cell (such as hidden text or a space) exists within the target expansion range.
D. The user forgot to finalize the formula entry with Ctrl+Shift+Enter.
Answer: C
Rationale:
, Lead: The `#SPILL!` error triggers defensively when a dynamic array attempts to populate a
range of cells, but one or more of the required destination cells already contains data.
Discriminator: Excel refuses to overwrite existing grid data with a spilled array, forcing the user
to clear the blocking content before the formula will manifest.
Distractor: Option B is incorrect because older Excel versions do not throw `#SPILL!` errors;
they typically return an `_xlfn.` prefix or `#NAME?` error.
Key Principle: Even a cell containing a single invisible space character will block an array from
spilling, requiring clearing of formats and contents to resolve. [Excel Support Documentation
2025]
Question 5
A triage nurse uses a spreadsheet to categorize patients by their Glasgow Coma Scale (GCS) in
cell E4. A score of 15 is "Normal", 13-14 is "Mild", and <13 is "Severe". Which modern logical
formula is the cleanest method to evaluate these sequential thresholds without complex nesting?
A. `=SWITCH(E4, =15, "Normal", >=13, "Mild", "Severe")`
B. `=IFS(E4=15, "Normal", E4>=13, "Mild", E4<13, "Severe")`
C. `=IF(E4=15, "Normal", IF(E4<13, "Severe", "Mild"))`
D. `=CHOOSE(E4, "Severe", "Mild", "Normal")`
Answer: B
Rationale:
Lead: The IFS function is specifically engineered to test multiple conditions sequentially,
returning the value corresponding to the very first TRUE statement encountered.
Discriminator: It allows for clear, left-to-right reading of multiple logical tests (`E4>=13`, etc.)
without the confusing parenthesis depth of nested IFs.
Distractor: Option A fails because the SWITCH function requires exact value matches (e.g.,
matching the literal number 15) and cannot process logical operators like `>=` within its
arguments.
Examiner Insight: Because IFS stops at the first TRUE condition, ordering your clinical
thresholds correctly (e.g., evaluating the highest or lowest severity first) is paramount to
prevent misclassification. [MO-201 Guidelines 2025]
Question 6
An informatics lead is configuring conditional formatting to monitor sepsis bundle compliance
times. They require distinct visual markers in the cell: a green checkmark for completion under 1
