MO-201 Sample Questions

MO-201 Sample Questions & Answers

Advanced formulas and macros, including logical and lookup functions, carry the most weight, alongside managing PivotTables, PivotCharts and advanced charts, filling and validating data with conditional formatting, and workbook collaboration settings.

Launch the full MO-201 simulator →

Showing 8 of 17 free samples.

  1. Question 1Beginner

    Manage workbook options and settings · Prepare workbooks for collaboration

    You are reviewing a workbook where threaded comments are used for collaboration. You need to ensure that a specific discussion is marked as resolved so that it no longer appears as an active conversation, but the history is preserved. What should you do?

    Show answer & explanation

    Correct answer: B

    In Excel 2019/365 threaded comments, using the 'Resolve Thread' option closes the discussion but keeps it accessible for future reference, unlike deleting it which removes the history.

  2. Question 2Intermediate

    Manage and format data · Fill cells based on existing data

    You have a list of full names in Column A in the format 'Last, First Middle'. You want to extract just the First Name into Column B. You type the first desired result in cell B2. What is the most reliable way to fill the rest of the column using Flash Fill?

    Show answer & explanation

    Correct answer: D

    Ctrl+E is the keyboard shortcut for Flash Fill. After providing an example in B2, selecting the next cell (B3) and pressing Ctrl+E triggers Excel to recognize the pattern and fill the column.

  3. Question 3Advanced

    Manage and format data · Format and validate data

    You are creating a custom number format for a financial report. Positive numbers should be blue with two decimals, negative numbers should be red in parentheses, and zeros should be displayed as a dash '-'. Which format code is correct?

    Show answer & explanation

    Correct answer: D

    Custom number formats follow the syntax: Positive;Negative;Zero;Text. This code sets Blue for positive, Red/Parentheses for negative, and a dash for zero.

  4. Question 4Intermediate

    Manage and format data · Format and validate data

    You have a dataset of customer orders. You need to identify duplicate orders based on a combination of 'CustomerID' (Column A) and 'OrderDate' (Column C), ignoring other columns. What is the correct procedure?

    Show answer & explanation

    Correct answer: A

    To identify duplicates based on a composite key (multiple columns), you must use the Remove Duplicates dialog and explicitly select only the columns that define uniqueness, in this case, CustomerID and OrderDate.

  5. Question 5Advanced

    Manage and format data · Apply advanced conditional formatting and filtering

    You are managing an inventory list in cells A2:E100. You want to highlight the entire row in green if the 'Stock Status' in column E is 'In Stock'. Which formula should you use in the Conditional Formatting New Rule dialog?

    Show answer & explanation

    Correct answer: B

    To highlight an entire row based on the value of a specific column, you must lock the column reference ($E) but keep the row relative (2). This applies the check for 'In Stock' to every cell in the row based on column E's value.

  6. Question 6Advanced

    Manage and format data · Format and validate data

    You need to create a validation rule for cell A1 that only accepts a text string that is exactly 5 characters long and consists of uppercase letters. Which formula would you use in the Data Validation 'Custom' type?

    Show answer & explanation

    Correct answer: D

    This formula checks two conditions: LEN(A1)=5 ensures the length is exactly 5, and EXACT(A1, UPPER(A1)) ensures the text is uppercase (EXACT is case-sensitive). Both must be TRUE.

  7. Question 7Intermediate

    Create advanced formulas and macros · Perform logical operations in formulas

    A project manager wants to assign a status to tasks based on their '% Complete' in cell C2. The logic is: 100% is 'Completed', >75% is 'Review', >50% is 'In Progress', and anything else is 'Not Started'. Which IFS formula correctly implements this logic?

    Show answer & explanation

    Correct answer: A

    The IFS function evaluates conditions in order. It first checks for completion (100%), then Review (>75%), then In Progress (>50%). The final condition 'TRUE' acts as a catch-all 'Else' for any value that didn't meet previous criteria.

  8. Question 8Intermediate

    Create advanced formulas and macros · Look up data by using functions

    You need to look up a Product ID in column A based on a Product Name in column B. The Product Name is 'Widget X'. Since the return column (ID) is to the left of the lookup column (Name), VLOOKUP cannot be used. Which formula performs this left-lookup correctly?

    Show answer & explanation

    Correct answer: B

    INDEX(Return_Range, Row_Number) combined with MATCH(Lookup_Value, Lookup_Range, 0) allows for a left lookup. MATCH finds the row number of 'Widget X' in column B, and INDEX returns the value from that same row in column A.

Ready for the real thing?

The full MO-201 simulator has every exam-style question, timed mode, and instant scoring.

Go to the MO-201 simulator →