Which Excel function returns a dynamic array of unique values from a range, automatically spilling results into adjacent cells?
Advanced Formulas
Question · Medium
A formula =FILTER(A2:C50,B2:B50>1000) returns the error #CALC!. What is the most likely cause?
Question · Hard
Chapter 1: Exam Overview & MO-211 Fundamentals
1.1 MO-211 Exam Structure, Scoring, & Performance-Based Strategy
Study guide
Which formula generates a sequential list of integers from 1 to 10 that spills automatically into cells below the formula cell?
Tech Exam Prep Podcast
Listen on Spotify
Podcast
A user writes =LET(x,FILTER(A2:A100,B2:B100="East"),SUM(x)). What does this formula accomplish?
Which formula correctly defines a LAMBDA function named DoubleIt that accepts one argument and returns twice its value, stored via the Name Manager?
=MAP(A2:A10, LAMBDA(val, val^2)) — what does this formula return?
1.2 Excel Environment & Microsoft 365 Architecture
What does =REDUCE(0, A2:A10, LAMBDA(acc, val, acc+val)) calculate?
A formula =SORT(A2:C20,3,-1) is entered. What does the -1 argument control?
Which formula uses INDIRECT to sum a named range whose name is stored as text in cell A1?
=OFFSET(B2,3,1,4,2) — which range does this formula reference?
Chapter 2: Managing Workbooks & File Options
2.1 Workbook Versioning, AutoRecover, & File Properties
A macro recorded in Excel is stored in which language?
Macros And Vba
Which file format MUST be used to save an Excel workbook that contains VBA macros?
In VBA, which statement correctly assigns the value 500 to cell B3 on the active sheet?
A VBA Sub loops through all worksheets in a workbook and prints each sheet name to the Immediate Window. Which code correctly iterates all sheets?
2.2 External Data Referencing & Cross-Workbook Formulas
Which VBA event procedure runs automatically whenever a user changes the value of any cell on Sheet1?
What is the effect of setting Application.ScreenUpdating = False at the start of a VBA macro?
A macro needs to check whether the value in A1 is greater than 100 and display "High" in B1, otherwise "Low". Which VBA code is correct?
In Power Query, what does the M formula language expression Table.FilterRows(Source, each [Region] = "West") accomplish?
Power Query
2.3 Macro Security, Portability, & Workbook Distribution
Which Power Query step type is used to combine data from two queries based on a matching column, similar to a SQL JOIN?
In Power Query's M language, what is the purpose of the #"Changed Type" step?
A PivotTable is showing incorrect totals after the source data was updated. What is the correct action to refresh the results?
Pivottables And Charts
Which PivotTable feature allows you to display values as a percentage of the grand total?
Chapter 3: Workbook Protection & Collaboration Settings
3.1 Cell Locking, Range Permissions, & Worksheet Protection
What is a Slicer in the context of Excel PivotTables?
In Power Pivot, what is the purpose of creating a relationship between two tables?
Power Pivot
Which DAX function in Power Pivot calculates a sum for each row of a table by evaluating an expression in row context?
A workbook uses a What-If Analysis Scenario Manager. What information does a scenario store?
What If Analysis
3.2 Workbook Structure Protection & Editing Restrictions
Which What-If Analysis tool allows a user to work backward — specifying a desired formula result and finding the input value that produces it?
Which function in Excel predicts future values based on a linear regression of historical time-series data?
A formula =GROWTH(B2:B13,A2:A13,A14:A16) is entered. What does it return?
Which workbook protection setting prevents users from adding, deleting, renaming, moving, or hiding worksheets?
Workbook Options Settings
3.3 Formula Calculation Options & Iterative Calculations
A user wants to allow certain users to edit specific ranges in a protected sheet while keeping all other cells locked. Which feature enables this?
Which Excel feature tracks all changes made to a shared workbook, recording who changed what and when?
What does enabling Iterative Calculation in Excel Options allow?
A user wants to display a custom message and input prompt when someone clicks a specific cell. Which feature provides this?
Chapter 4: Advanced Data Entry & Series
4.1 Flash Fill Patterns & Text Extraction Techniques
Which formula correctly uses XMATCH to find the position of the value "Gamma" in the range A1:A20 with exact match?
=CHOOSE(3,"Alpha","Beta","Gamma","Delta") — what value does this formula return?
Which formula uses SWITCH to return "Low" when A1<50, "Mid" when A1<80, and "High" otherwise?
A user needs to apply a custom number format that displays numbers with a comma thousands separator, two decimal places, and negative numbers in red with parentheses. Which format code achieves this?
Manage And Format Data
Loading more…