Microsoft Excel Expert (Office 2019) Free Sample Questions

Description

Covers managing workbooks and collaboration and language settings, filling, formatting and validating data with advanced conditional formatting, and advanced charts and PivotTables.

17 free sample questions270 in the full practice test Other version: 77-427(155)

Try simulator

MO-201 Sample Questions

  1. Question 1

    AdvancedMultiple answers

    Create advanced formulas and macros · Trace precedence and dependence

    When tracing precedents for a formula, what do a red tracer arrow and a dashed black tracer arrow indicate? (Select TWO)

    flowchart LR subgraph Worksheet1 A1(10) --> C1 B1(20) --> C1{=A1+B1} end subgraph Worksheet2 D5(5) -.-> C1 end subgraph ErrorSheet E1(#REF!) -- color:red --> C1 end
    Answer and explanation

    Correct answers: C, D

    Red tracer arrows are used to signify that a precedent cell is the source of an error that is propagating through the formulas.

    When a precedent is not on the active sheet, Excel displays a dashed black arrow pointing to a small worksheet icon, indicating an external link.

  2. Question 2

    Intermediate

    Manage workbook options and settings · Manage workbooks

    You are managing a workbook named 'Budget_Master.xlsx' that contains several macros in a standard module. You need to transfer the module named 'Q1_Processing' to another open workbook named 'Archive_2023.xlsm'. What is the most efficient method to accomplish this within the Visual Basic Editor (VBE)?

    Answer and explanation

    Correct answer: B

    The most efficient way to copy a macro module between open workbooks is to drag and drop the module icon from the source project to the destination project within the Project Explorer window of the VBE.

  3. Question 3

    Intermediate

    Manage workbook options and settings · Prepare workbooks for collaboration

    You are preparing a 'Q3_Financials.xlsx' workbook for distribution to department heads. The workbook contains proprietary formulas that you wish to remain hidden. However, users must be able to input data into cells B5:B20. Which sequence of actions must you take to achieve this?

    Answer and explanation

    Correct answer: B

    To hide formulas while allowing data entry, you must first unlock the input range (B5:B20) and verify the formula cells are set to 'Hidden' in the Format Cells dialog (Protection tab). Finally, you must enable Worksheet Protection for these settings to take effect.

  4. Question 4

    Beginner

    Manage workbook options and settings · Prepare workbooks for collaboration

    A user reports that a specific workbook is calculating slowly. Upon investigation, you notice the status bar indicates 'Calculate' even after you press F9. You suspect circular references or iteration issues. Where should you navigate to check if 'Enable iterative calculation' is turned on?

    Answer and explanation

    Correct answer: A

    The 'Enable iterative calculation' setting, along with the maximum number of iterations and maximum change values, is located in the Excel Options dialog under the Formulas category, not on the ribbon.

  5. Question 5

    Beginner

    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?

    Answer and 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.

  6. Question 6

    Intermediate

    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?

    Answer and 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.

  7. Question 7

    Advanced

    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?

    Answer and 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.

  8. Question 8

    Intermediate

    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?

    Answer and 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.

Register free for 9 more questions

Or unlock all 270 MO-201 questions with explanations, timed mode and flashcards.