I85PREP
⌘ K
MO-211 study materials
MO-211 study resources

MO-211 practice questions

One question at a time. Build your MO-211 understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Unverified domain 1 - official domain list not published on retrieved page

Which Excel function adds values in a range only when every corresponding criterion is met?

  1. COUNTIF
  2. AVERAGEIF
  3. IFERROR
  4. SUMIFS Correct answer.

Explanation

SUMIFS sums a sum_range after evaluating one or more criteria_range/criteria pairs; a row contributes only if all criteria pairs are true. COUNTIF and AVERAGEIF do not return a sum across multiple criteria pairs, and IFERROR only handles errors.

Question 2Unverified domain 1 - official domain list not published on retrieved page

In a table named Sales, what does the structured reference Sales[Amount] refer to?

  1. The total row value for Amount
  2. The entire Sales table including headers
  3. Data cells in the Amount column Correct answer.
  4. Only the header cell of Amount

Explanation

In a table named Sales, Sales[Amount] is a structured reference to the data body cells of the Amount column, excluding the header and total row. To include the header you would use Sales[[#Headers],[Amount]].

Question 3Unverified domain 1 - official domain list not published on retrieved page

Which Excel feature limits a cell to values from a list you define?

  1. Data Validation Correct answer.
  2. PivotTable
  3. Consolidate
  4. Conditional Formatting

Explanation

Data Validation can restrict entries to a list, whole number, date, or custom formula. Conditional Formatting changes appearance, PivotTables summarize, and Consolidate combines ranges.

Question 4Unverified domain 1 - official domain list not published on retrieved page

Which chart type is most appropriate for showing a continuous trend over time?

  1. Doughnut chart
  2. Scatter chart
  3. Pie chart
  4. Line chart Correct answer.

Explanation

A line chart places time on the horizontal axis and connects data points, making trends over time easy to see. Pie and doughnut charts show parts of a whole, while scatter charts compare two numeric variables.

Question 5Unverified domain 1 - official domain list not published on retrieved page

What is the main benefit of assigning a name to a worksheet range?

  1. The range is automatically protected from editing
  2. The range is converted into a PivotTable
  3. The workbook is saved in a macro-enabled format
  4. Formulas can use a meaningful label Correct answer.

Explanation

A defined name lets formulas use a descriptive label instead of a cell address, improving readability and maintenance. It does not protect, change file format, or create a PivotTable.

Question 6Unverified domain 1 - official domain list not published on retrieved page

Which function returns the relative position of a lookup value within a one-dimensional range?

  1. INDEX
  2. OFFSET
  3. MATCH Correct answer.
  4. CHOOSE

Explanation

MATCH returns the position of a value in a range, which INDEX can then use to return a corresponding value. INDEX returns a value or reference, OFFSET returns a shifted reference, and CHOOSE selects from an argument list.

Question 7Unverified domain 1 - official domain list not published on retrieved page

Which tool lets you summarize large data by placing fields into rows, columns, and values areas?

  1. PivotTable Correct answer.
  2. Scenario Manager
  3. Goal Seek
  4. Data Table

Explanation

A PivotTable lets you drag fields into row, column, filter, and value areas to summarize data interactively. Goal Seek, Scenario Manager, and Data Table are what-if tools, not summarization layouts.

Question 8Unverified domain 1 - official domain list not published on retrieved page

Which workbook file type can store VBA macros?

  1. .xlsm Correct answer.
  2. .xltx
  3. .csv
  4. .xlsx

Explanation

The .xlsm format stores VBA projects along with workbook content. .xlsx and .xltx cannot retain macros, and .csv stores only plain text values.

Question 9Unverified domain 1 - official domain list not published on retrieved page

Which argument order is correct for the SUMIFS function?

  1. criteria1, criteria_range1, sum_range
  2. sum_range, criteria1, criteria_range1
  3. criteria_range1, criteria1, sum_range
  4. sum_range, criteria_range1, criteria1 Correct answer.

Explanation

SUMIFS begins with the range to sum, then takes criteria_range1 and criteria1, followed by optional additional pairs. Putting the criteria first matches SUMIF-style order, which SUMIFS does not use.

Question 10Unverified domain 1 - official domain list not published on retrieved page

A lookup value is in column D and the return value is in column A. Which approach can return the column A value?

  1. VLOOKUP with exact match
  2. CHOOSE with a single index
  3. INDEX/MATCH on column D Correct answer.
  4. HLOOKUP with approximate match

Explanation

INDEX/MATCH can return a value from a column to the left of the lookup column because MATCH locates the row independently. VLOOKUP searches rightward from the lookup column, HLOOKUP searches across rows, and CHOOSE needs an index number.

Question 11Unverified domain 1 - official domain list not published on retrieved page

You want to highlight rows 2 through 100 when the value in column B equals "Overdue". Which conditional formatting formula should you use when the applied range is A2:D100?

  1. =B$2="Overdue"
  2. =$B2="Overdue" Correct answer.
  3. =$B$2="Overdue"
  4. =B2="Overdue"

Explanation

For a range starting at A2, the conditional formatting formula is relative to A2. Locking column B with $B while leaving the row relative, as in =$B2="Overdue", makes every column in the row test column B.

Question 12Unverified domain 1 - official domain list not published on retrieved page

Which custom Data Validation formula prevents duplicate entries in cells A2:A100 when applied to that range?

  1. =COUNTIF(A2:A100,A2)>1
  2. =COUNTIF($A$2:$A$100,A2)<>0
  3. =COUNTIF(A2:A100,A2)=0
  4. =COUNTIF($A$2:$A$100,A2)=1 Correct answer.

Explanation

A custom Data Validation formula must return TRUE for allowed entries. COUNTIF($A$2:$A$100,A2)=1 is true only when the entry occurs exactly once in the range, so duplicates are rejected.

Question 13Unverified domain 1 - official domain list not published on retrieved page

In a PivotTable, which object creates a new data field whose values are calculated from existing fields?

  1. Grouping of items
  2. Calculated item for a field
  3. Slicer filter control
  4. Calculated field Correct answer.

Explanation

A calculated field creates a new value field using a formula that references other fields in the PivotTable. A calculated item instead creates a new item within an existing row or column field.

Question 14Unverified domain 1 - official domain list not published on retrieved page

In Power Query, which transformation converts month columns into a single column of month labels and a single column of values?

  1. Unpivot columns Correct answer.
  2. Transpose selected columns
  3. Merge two queries
  4. Group rows by value

Explanation

Unpivot columns transforms wide data, such as one column per month, into a tall structure with an attribute column and a value column. Transpose swaps rows and columns, Group By aggregates rows, and Merge combines queries.

Question 15Unverified domain 1 - official domain list not published on retrieved page

Which dynamic array function returns a list of distinct values from a range?

  1. SEQUENCE
  2. SORT
  3. FILTER
  4. UNIQUE Correct answer.

Explanation

UNIQUE returns the distinct values from an array or range and spills the results. SORT orders values, FILTER returns rows meeting a condition, and SEQUENCE generates a series of numbers.

Question 16Unverified domain 1 - official domain list not published on retrieved page

In XLOOKUP, which argument specifies the value to return when no match is found?

  1. return_array
  2. if_not_found Correct answer.
  3. lookup_array
  4. match_mode

Explanation

XLOOKUP's if_not_found argument supplies the result when the lookup value is absent. lookup_array is the search range, return_array is the result range, and match_mode controls exact or approximate matching.

Question 17Unverified domain 1 - official domain list not published on retrieved page

What does the # operator do after a dynamic array formula reference, such as A2#?

  1. Returns only the first value in A2
  2. Refers to the entire spilled range from A2 Correct answer.
  3. Converts the formula to a legacy array formula
  4. Locks cell A2 as an absolute reference

Explanation

The # operator after a dynamic array formula reference refers to the whole spilled range, including the formula cell and all spilled values. It is not an absolute-reference marker and does not convert the formula to legacy array entry.

Question 18Unverified domain 1 - official domain list not published on retrieved page

In an Excel table, what does the @ in a formula such as =[@Amount]*[@Rate] indicate?

  1. It uses the current row's value Correct answer.
  2. The formula returns a spilled array
  3. The column is an absolute reference
  4. The column contains a calculated field

Explanation

Inside a table, @ refers to the current row for the named column, enabling row-by-row calculated columns. Without @, the reference can refer to the whole column and may spill.

Question 19Unverified domain 1 - official domain list not published on retrieved page

Which Excel tool stores and switches between named sets of input values to compare outcomes?

  1. Scenario Manager Correct answer.
  2. Solver optimization tool
  3. Data Table sensitivity tool
  4. Goal Seek analysis tool

Explanation

Scenario Manager stores multiple named input sets and switches among them to compare results. Goal Seek solves for one input, Data Table shows sensitivity, and Solver optimizes with constraints.

Question 20Unverified domain 1 - official domain list not published on retrieved page

You know the desired result of a formula but not the input value needed. Which tool should you use?

  1. Subtotal
  2. Scenario Manager
  3. Goal Seek Correct answer.
  4. Flash Fill

Explanation

Goal Seek adjusts one input cell until a formula reaches a specified target value. Scenario Manager compares saved input sets, Subtotal inserts summary rows, and Flash Fill fills patterns from examples.

Question 21Unverified domain 1 - official domain list not published on retrieved page

In Power Pivot, which DAX function evaluates an expression in a modified filter context?

  1. SUMX iterator
  2. RELATED function
  3. CALCULATE Correct answer.
  4. ALL filter remover

Explanation

CALCULATE evaluates an expression after modifying the filter context, such as adding or removing filters. RELATED fetches a value from a related table, SUMX iterates rows, and ALL removes filters but does not itself evaluate an expression with a changed context.

Question 22Unverified domain 1 - official domain list not published on retrieved page

What does the VBA statement For Each cell In Range("A1:A10") do?

  1. Iterates through each cell in A1:A10 Correct answer.
  2. Deletes every cell in A1:A10
  3. Selects only the first cell in the range A1:A10
  4. Creates a named range for A1:A10

Explanation

For Each ... In Range("A1:A10") loops through each cell in the specified range, assigning the current cell to the loop variable. It does not delete, select only the first cell, or create a named range.

Question 23Unverified domain 1 - official domain list not published on retrieved page

Which formula totals Amount values where Region equals "East" and Month equals "Jan" without using SUMIFS?

  1. =SUMPRODUCT(Amount,Region="East",Month="Jan")
  2. =SUMPRODUCT(Amount*(Region="East")+(Month="Jan")*1)
  3. =SUMPRODUCT((Region="East")*(Month="Jan")*Amount) Correct answer.
  4. =SUMPRODUCT((Region="East")*(Month="Jan"))

Explanation

SUMPRODUCT multiplies corresponding elements of arrays and sums the products. Multiplying the two Boolean arrays by Amount gives Amount only where both conditions are true; the other formulas either omit Amount or combine conditions incorrectly.

Question 24Unverified domain 1 - official domain list not published on retrieved page

In an Excel Data Model, what is required before you can build a PivotTable that uses fields from two related tables?

  1. Both tables must have identical column headers
  2. Both tables must be on the same worksheet
  3. A relationship must link the tables Correct answer.
  4. Both tables must be converted to ranges

Explanation

A Data Model PivotTable can use fields from multiple tables only after relationships define how the tables are connected. The tables do not need to be on the same sheet, share headers, or be converted to ordinary ranges.

Question 25Unverified domain 1 - official domain list not published on retrieved page

In Solver, which constraint type requires a decision variable to be integer?

  1. bin
  2. int Correct answer.
  3. >=
  4. dif

Explanation

In Solver, the int constraint requires a variable to be an integer; bin requires 0 or 1, dif enforces integer differences, and >= is an inequality relationship. The constraint type is specified separately from the cell reference.

Question 26Unverified domain 1 - official domain list not published on retrieved page

Which calculation option in Excel recalculates formulas only when you explicitly request it?

  1. Manual Correct answer.
  2. Automatic
  3. Iterative
  4. Automatic Except for Data Tables

Explanation

The Manual option stops Excel from recalculating when a value changes or a workbook opens; pressing F9 or Ctrl+Alt+F9 forces a full recalculation. Automatic recalculates after every edit, and the data-table variant still recalculates all ordinary formulas automatically.

Question 27Unverified domain 1 - official domain list not published on retrieved page

Which feature keeps the top rows of a worksheet on screen while you scroll down through the data?

  1. Split
  2. Hide
  3. Group
  4. Freeze Panes Correct answer.

Explanation

Freeze Panes locks the rows above and the columns to the left of the active cell so they stay visible while the rest of the sheet scrolls. Split divides the window into separately scrollable panes but does not anchor a header row in place.

Question 28Unverified domain 1 - official domain list not published on retrieved page

Which feature fills a column by detecting a pattern from a few values you type as examples?

  1. Fill Down
  2. Text to Columns
  3. Flash Fill Correct answer.
  4. AutoFill series

Explanation

Flash Fill recognises a pattern in the examples you type and completes the remaining cells without a formula; it runs from Data > Flash Fill or with Ctrl+E. AutoFill extends series such as dates or numbers rather than inferring text patterns.

Question 29Unverified domain 1 - official domain list not published on retrieved page

Which paste option keeps only the results shown in the cells and removes the underlying formulas?

  1. Paste Special > Formatting
  2. Paste Special > Formulas
  3. Paste Special > Link
  4. Paste Special > Values Correct answer.

Explanation

The Values option pastes the calculated results as static content, so the destination cells no longer hold formulas. The Formulas option copies the formula text, Formatting copies only appearance, and Link creates a reference back to the source cells.

Question 30Unverified domain 1 - official domain list not published on retrieved page

What does a sparkline display?

  1. A chart on a separate chart sheet
  2. A PivotChart tied to a slicer
  3. A mini chart inside one cell Correct answer.
  4. A trendline on a column chart

Explanation

A sparkline is a compact chart drawn inside a single cell so a row of numbers can be shown as a line, column set, or win/loss graphic beside the data. It is inserted from the Insert tab and belongs to the cell that contains it.

Question 31Unverified domain 1 - official domain list not published on retrieved page

Which area of Excel holds the security settings that decide whether macros and add-ins may run?

  1. Name Manager
  2. Macro Recorder
  3. Trust Center Correct answer.
  4. Add-ins dialog

Explanation

The Trust Center, reached through File > Options, contains macro settings, trusted locations, trusted publishers, and add-in security. Those settings apply to the application rather than travelling with an individual workbook.

Question 32Unverified domain 1 - official domain list not published on retrieved page

Where is a recorded macro stored when you want it available in every workbook you open?

  1. The current workbook
  2. The Personal workbook Correct answer.
  3. A brand-new workbook
  4. A hidden worksheet tab

Explanation

Choosing the Personal Macro Workbook during recording stores the code in Personal.xlsb, which Excel loads in the background at start-up so the macro is available in all workbooks. A macro stored in an ordinary workbook exists only while that file is open.

Question 33Unverified domain 1 - official domain list not published on retrieved page

Which function returns TRUE only when the value it tests is the #N/A error?

  1. ISERROR
  2. ISNA Correct answer.
  3. IFERROR
  4. ISERR

Explanation

ISNA tests specifically for #N/A and returns TRUE only for that error. ISERROR catches any error, ISERR catches any error except #N/A, and IFERROR replaces an error with a substitute value instead of returning a logical result.

Question 34Unverified domain 1 - official domain list not published on retrieved page

Which custom number format shows 1234 as 1,234 and -1234 as (1,234)?

  1. #,##0;[Red]-#,##0
  2. #,##0;[Red]#,##0
  3. #,##0;(#,##0) Correct answer.
  4. 0.00;(0.00)

Explanation

Custom formats are split into sections separated by semicolons in the order positive; negative; zero; text. Using #,##0 for the positive section and (#,##0) for the negative section places negatives in parentheses while keeping the thousands separator.

Question 35Unverified domain 1 - official domain list not published on retrieved page

Which function turns a text string such as "Sheet2!B5" into a live reference that Excel evaluates?

  1. TEXTJOIN
  2. INDIRECT Correct answer.
  3. ADDRESS
  4. CHOOSE

Explanation

INDIRECT converts text into an evaluated reference, so an address can be assembled from other cells or sheet names. ADDRESS works the other way round, producing an address as text from row and column numbers.

Question 36Unverified domain 1 - official domain list not published on retrieved page

Starting from cell A1, which formula returns a 3-row by 2-column range whose top-left cell is five rows below A1?

  1. =OFFSET(A1,5,0,3,2) Correct answer.
  2. =OFFSET(A1,0,5,3,2)
  3. =OFFSET(A1,5,0,2,3)
  4. =OFFSET(A1,3,2,5,0)

Explanation

OFFSET takes rows first and then columns, followed by height and then width. Five rows down and no columns across needs 5,0, and a block of 3 rows by 2 columns needs a height of 3 and a width of 2.

Question 37Unverified domain 1 - official domain list not published on retrieved page

What is the purpose of the LET function?

  1. It evaluates a condition and returns one of two values
  2. It spills a range into neighbouring cells automatically
  3. It names intermediate results inside a formula Correct answer.
  4. It creates a reusable custom function without VBA code

Explanation

LET assigns a name to a calculation within a formula and then reuses that name, which shortens long formulas and avoids evaluating the same expression repeatedly. It does not create a function that other formulas can call by name.

Question 38Unverified domain 1 - official domain list not published on retrieved page

What does the formula =SUMPRODUCT((A2:A10="East")*B2:B10) return?

  1. The count of East entries in column A
  2. The total of B values where A equals East Correct answer.
  3. The average of B values where A equals East
  4. The number of rows with numeric B values

Explanation

The comparison produces an array of 1s and 0s that is multiplied by the B values, and SUMPRODUCT adds those products, giving the sum of B for the rows marked East. Rows that fail the test are multiplied by zero and drop out.

Question 39Unverified domain 1 - official domain list not published on retrieved page

A chart plots revenue in millions and units sold in thousands, and the units series is almost invisible. What is the best fix?

  1. Add a trendline to the units series
  2. Change the chart to a pie chart
  3. Plot units on a secondary axis Correct answer.
  4. Move the units series to a chart sheet

Explanation

A secondary value axis gives the smaller series its own scale so both series remain readable in the same plot area; it is set per series in Format Data Series. Changing the chart type or adding a trendline does not correct a scale mismatch.

Question 40Unverified domain 1 - official domain list not published on retrieved page

A PivotTable shows daily dates in its Rows area. Which command combines those dates into quarters?

  1. Calculated Item
  2. Field Settings
  3. Group Field Correct answer.
  4. Show Values As

Explanation

Grouping a date field creates grouping levels such as Quarters and Years, after which the measures are summarised at that level. Show Values As changes how the numbers are presented but never how the rows are combined.

Question 41Unverified domain 1 - official domain list not published on retrieved page

Which tool copies rows that meet criteria to another location, using a separate criteria range placed on the worksheet?

  1. Remove Duplicates
  2. Advanced Filter Correct answer.
  3. Text to Columns
  4. Filter by colour

Explanation

Advanced Filter reads a criteria range whose headers match the list headers and can filter in place or copy the matching rows to another location. AutoFilter criteria are entered in drop-downs and cannot copy results elsewhere.

Question 42Unverified domain 1 - official domain list not published on retrieved page

When Remove Duplicates runs on a selected range, what happens to the repeated rows?

  1. They are moved to a new worksheet
  2. They are highlighted with a fill colour
  3. They are removed, keeping the first copy Correct answer.
  4. They are hidden but remain in place

Explanation

Remove Duplicates deletes the later rows that match an earlier row across the compared columns and keeps the first occurrence. Because the deletion is permanent for that data, working on a copy is advisable.

Question 43Unverified domain 1 - official domain list not published on retrieved page

You are recording a macro that must act on whichever row the user selects, not only the row where recording started. Which option should you enable?

  1. Use Absolute References
  2. Use Relative References Correct answer.
  3. Assign a shortcut key
  4. Store in Personal Workbook

Explanation

With relative references the recorder stores offsets from the starting cell, so the macro acts on the active cell wherever it happens to be. Absolute references replay the steps on the exact cells used during recording.

Question 44Unverified domain 1 - official domain list not published on retrieved page

A cell uses a validation list and users keep typing values that are not on the list. Which setting determines whether Excel blocks the entry or merely warns?

  1. Ignore blank
  2. Input Message
  3. Error Alert style Correct answer.
  4. In-cell dropdown

Explanation

The Error Alert tab offers Stop, Warning, and Information styles; Stop refuses the entry while Warning and Information allow it after a prompt. The Input Message tab only shows a tooltip when the cell is selected.

Question 45Unverified domain 1 - official domain list not published on retrieved page

Which feature reports the elements that will be lost when a workbook is saved for an older version of Excel?

  1. Compatibility Checker Correct answer.
  2. Accessibility Checker
  3. Track Changes
  4. Inspect Document

Explanation

The Compatibility Checker lists features unsupported in the target version, such as newer functions or certain formatting, and can run automatically each time the file is saved. The Document Inspector instead removes hidden metadata and personal information.

Question 46Unverified domain 1 - official domain list not published on retrieved page

After building a working LAMBDA formula in a cell, what must you do before you can call it by name like a built-in function?

  1. Enable iterative calculation
  2. Add it to the Quick Access Toolbar
  3. Save the file as .xlsm
  4. Give it a name in Name Manager Correct answer.

Explanation

A LAMBDA becomes a usable named function only when it is defined as a name whose Refers to box holds the LAMBDA formula; the name is then typed like any built-in function. No macro-enabled format is needed because LAMBDA is a native worksheet feature.

Question 47Unverified domain 1 - official domain list not published on retrieved page

A dynamic array formula returns #SPILL!. What is the most likely cause?

  1. The lookup value was not found anywhere
  2. The function is not supported in this version
  3. The formula contains a circular reference
  4. Cells in the spill range are not empty Correct answer.

Explanation

#SPILL! appears when the area the results need to occupy is blocked by existing values, merged cells, or a table boundary. Clearing the blocking cells lets the results spill and the error disappears.

Question 48Unverified domain 1 - official domain list not published on retrieved page

Which PivotTable object creates a new row or column category by combining existing categories, such as merging two product groups into one?

  1. Report filter
  2. Calculated item Correct answer.
  3. Show Values As
  4. Calculated field

Explanation

A calculated item builds a new item inside a row or column field from existing items of that same field. A calculated field instead adds a new measure to the Values area computed from other value fields.

Question 49Unverified domain 1 - official domain list not published on retrieved page

How do macros in a workbook that is stored in a folder marked as a trusted location behave when the file is opened?

  1. They are disabled and cannot be enabled
  2. They are blocked until Enable Content is clicked
  3. They run without a security prompt Correct answer.
  4. They ask for a password each time

Explanation

Files inside a trusted location bypass the Trust Center's macro blocking, so the code runs without the security bar or Enable Content prompt. That is why trusted locations should be limited to folders you fully control.

Question 50Unverified domain 1 - official domain list not published on retrieved page

You need a formula that pulls one value from a PivotTable and still returns the correct figure after the PivotTable layout is rearranged. Which approach is most reliable?

  1. Use GETPIVOTDATA with field names Correct answer.
  2. Paste the value as a link
  3. Reference the PivotTable cell directly
  4. Use VLOOKUP on the PivotTable range

Explanation

GETPIVOTDATA retrieves a value by its field and item names, so it keeps returning the right figure even when the PivotTable is rearranged, filtered, or refreshed. Position-based references break as soon as the layout moves.