Excel Expert (Office 2019) Practice Questions
Prepare for MO-201 with more than an answer.
- Exam fee
- $100 USD
- Level
- Expert
- Valid for
- Lifetime (does not expire)
Domains covered on the exam 4
- Manage workbook options and settings17.5%
- Manage and format data22.5%
- Create advanced formulas and macros32.5%
- Manage advanced charts and tables27.5%
- 1
You are calculating the project end date. The start date is in A1 (Oct 1, 2023), and the duration is 15 working days. The project team works Monday through Saturday (6-day workweek). Which function should you use?
Show answer details
Correct answer: D
WORKDAY.INTL allows you to specify custom weekend parameters. The code '11' corresponds to Sunday only being the weekend, which fits the Monday-Saturday workweek requirement.
- 2
You have a sales report with 'Region' (A), 'Product' (B), and 'Sales' (C). You need to calculate the total sales for 'West' region for the product 'Laptop'. Which formula is the most appropriate?
Show answer details
Correct answer: A
SUMIFS sums a range (C:C) based on multiple criteria (Region='West' and Product='Laptop'). The syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...).
- 3
You are analyzing loan repayment options. You know the loan amount (PV), the interest rate (Rate), and the number of periods (Nper). You need to calculate the monthly payment amount. Which financial function should you use?
Show answer details
Correct answer: A
The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.
- 4
You have a complex formula in cell D10 that involves inputs from several other sheets. You want to monitor the value of D10 while you are working on a different sheet, 'Inputs'. What tool allows you to do this?
Show answer details
Correct answer: C
The Watch Window (Formulas tab) allows you to add specific cells to a floating window so you can inspect their values and formulas even when the active sheet is different.
- 5
You are creating a chart to compare 'Revenue' (Currency, large numbers) and 'Profit Margin' (Percentage, small decimals) over time. To ensure both series are clearly visible, what chart configuration should you use?
Show answer details
Correct answer: C
A Combo Chart with a Secondary Axis is ideal for comparing two data series with significantly different scales (e.g., millions vs percentages).
- 6
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 endShow answer details
Correct answer: 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.
- 7
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)?
Show answer details
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.
- 8
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?
Show answer details
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.
