MO-200 Excel Certification Practice Exam

A financial analyst wants row 1 (column headings) to stay visible while scrolling down through hundreds of sales transactions. Which ribbon path configures this view?

In target cell C6, calculate the total base salary for all logistics employees using the SUM function.

Which of the following operations can be performed directly by right-clicking a worksheet tab at the bottom of the workbook window? (Select all that apply)

In target cell B6, calculate the average units sold across the four fiscal quarters using the AVERAGE function.

A formula in cell C2 is written as '=A2*$B$1'. If you copy cell C2 and paste it into cell C3, what formula will appear in cell C3?

Complete the inventory purchase order sheet. In cell D2, calculate the total line cost for Item A (Quantity * Unit Price). If the Order Subtotal in D5 is less than the Supplier Minimum of $5,000, an additional surcharge of $250 applies in D6; otherwise the surcharge is $0. Calculate the Final Order Total in cell D7.

Which keyboard shortcut converts an ordinary cell range into an official formatted Excel Table (ListObject) with filter dropdowns enabled?

In target cell B7, identify the highest single daily sales revenue from the week using the MAX function.

Which built-in features become automatically available when a cell range is converted into an Excel Table? (Select all that apply)

In target cell B6, determine the lowest monthly operating expense among the listed production facilities using the MIN function.

What is the primary visual difference between the Accounting number format and the Currency number format in Excel?

The regional sales manager must calculate commission bonuses. In cell C2, evaluate rep Jordan: If Closed Sales (B2) are greater than or equal to the Quota Target of 50000, award a bonus of 2500; otherwise award 500. Then verify the total department bonus payout across all reps.

When printing a 15-page inventory audit sheet, where do you set row 1 to automatically repeat at the top of every printed page?

A manager needs to know how many transactions in column B contain numeric sales amounts, ignoring text labels like 'Pending' or 'Exempt'. Which function should be used?

Which built-in visualization presets are available under the Home tab > Conditional Formatting menu? (Select all that apply)

In target cell A6, count how many client accounts are non-empty in column A (A2:A5) using the COUNTA function.

Which cell reference format keeps row 5 locked while allowing the column to change dynamically when copied horizontally?

The invoicing team needs to calculate net invoice amounts. In cell D2, calculate the Discount Amount: if Purchase Subtotal (C2) is greater than or equal to 10000, the client receives a 10% discount (C2 * 0.1); otherwise discount is 0. Then verify that Net Billed in E2 equals Subtotal minus Discount.

Where can an exam candidate quickly move a chart legend from the right side of a column chart to the bottom?

In target cell B2, extract the 3-letter regional prefix from the asset code in A2 using the LEFT function.

When sorting tabular business records using the Data > Sort dialog, which options can be configured? (Select all that apply)

Which parameters are strictly required by both the LEFT and RIGHT functions when extracting characters from a text string? (Select all that apply)

Which chart type is officially recommended to visualize the percentage breakdown of regional market share against a single total company revenue?

The warehouse coordinator must value on-hand stock. In cell D2, retrieve the Unit Price for Product ID in A2 from Price Table G2:H5 using VLOOKUP. Then verify that Total Inventory Value in cell E2 equals Quantity (C2) * Unit Price (D2).

In target cell B2, extract the 3-letter department code from cell A2 starting at character position 6 with a length of 3 using the MID function.

When header titles exceed the column width and you want the text to display across multiple stacked lines within the same cell without widening the column, which feature is used?

Which of the following statements correctly describe the rules and limitations of the standard VLOOKUP function? (Select all that apply)

In target cell B2, remove all unnecessary leading, trailing, and repeated spaces from the dirty customer name in A2 using the TRIM function.

While editing a cell formula in the formula bar, which function key cycles through cell reference types (e.g., relative A1 -> absolute $A$1 -> mixed A$1 -> mixed $A1)?

The operations supervisor is auditing weekly timesheets. In cell D2, calculate Total Hours Worked (Regular Hours + Overtime Hours). In cell E2, verify Overtime Pay: If Overtime Hours (C2) is greater than 0, multiply Overtime Hours by $30 (Overtime Rate); otherwise pay is $0. Ensure Total Labor Expense reconciles across the team.

In target cell B2, convert the warehouse state code in A2 to full uppercase letters using the UPPER function.

Which volatile Excel function returns the current system date without any time component and automatically updates whenever the worksheet recalculates?

Which worksheet display modes are available on the View tab in the Workbook Views group? (Select all that apply)

Which Excel functions are officially part of the MO-200 domain for manipulating, extracting, and standardizing text strings? (Select all that apply)

When a cell containing formatted numbers or dates displays '#####', what action must the user take to resolve it?

The merchandiser needs to complete the catalog price list. In cell C2, retrieve the Unit Selling Price for Product Code in A2 from Supplier Catalog F2:G5 using VLOOKUP. Then verify that Gross Margin in E2 equals Selling Price (C2) minus Cost (D2).

In target cell C2, combine First Name (A2) and Last Name (B2) with a space in between using the concatenation operator (&).

Under File > Info > Check for Issues > Inspect Document, which sensitive items can Excel detect and remove before publishing? (Select all that apply)

Which keyboard shortcut toggles between displaying calculated cell values and showing the underlying formulas across the active worksheet?

In target cell E2, look up the Shipping Rate from table A2:C5 for shipping tier "Express" in D2 using VLOOKUP with exact match (0).