Microsoft MO-200 Excel Associate (Office 2019) Practice Exam

An accountant maintains a workbook with 12 monthly sheets and needs to apply identical formatting to the Q1 sheets (Jan, Feb, Mar) at the same time.

1. Edit each of the three sheets individually, repeating the same steps
2. Copy formatting with Format Painter from one sheet to each other sheet one at a time
3. Hold Ctrl and click each sheet tab to group them, then make the formatting change once
4. Write a VBA macro to apply the formatting across the sheets

A financial analyst prints a large spreadsheet across many pages and needs the row-1 column headers to appear at the top of every printed page.

1. Use Freeze Panes, which only affects the on-screen display
2. Set Print Titles → Rows to Repeat at Top to the header row in Page Layout
3. Shrink the whole print area to a single page regardless of readability
4. Manually copy the header row onto each page before printing

A sales team’s Region column should accept only values from a fixed list: North, South, East, West.

1. Cell protection that locks the cells and prevents all input
2. Data Validation with a List setting containing the allowed values, shown as a dropdown
3. Conditional formatting that highlights values not in the expected set
4. A comment listing the valid options as informational guidance

A warehouse manager wants parts with fewer than 10 units on hand to turn red automatically as stock changes.

1. Manually color each low-stock cell red by reviewing every row
2. A Conditional Formatting rule applying a red fill when the quantity is less than 10
3. Data Validation that rejects entry of values below 10
4. A VLOOKUP that returns a status text without changing cell formatting

Product names have inconsistent trailing spaces that cause VLOOKUP mismatches; the text needs cleaning.

1. UPPER, which changes letter case without touching spaces
2. LEN, which counts characters including spaces without removing them
3. REPLACE, which substitutes characters at fixed positions
4. TRIM, which removes leading, trailing, and excess interior spaces

An HR manager tracks 500 employees and needs filtering, sorting, headers that stay visible when scrolling, and ranges that auto-expand as rows are added.

1. Split the data into separate worksheets per department
2. Use a plain range and apply filters manually
3. Convert the data to an Excel Table, which provides auto-filter, sort, sticky headers, and dynamic expansion
4. Build a PivotTable, which summarizes rather than provides a record-editing list

A sales data table needs a summary row showing the sum of Revenue and the count of transactions, staying correct as the table grows.

1. Enable the Total Row in Table Design, then choose the aggregate function for each column
2. Put SUBTOTAL in adjacent cells outside the table for each aggregation
3. Build a separate summary sheet with formulas referencing the table
4. Type SUM formulas in cells below the table by hand

A teacher assigns letter grades from a score in A2: 90+ = A, 80–89 = B, 70–79 = C, below 70 = F.

1. =GRADE(A2)
2. =CHOOSE(A2,"A","B","C","F")
3. =IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C","F")))
4. =A2>=90

An analyst needs to count how many orders in range B2:B200 exceed $1,000.

1. =COUNTIF(B2:B200,">1000")
2. =COUNT(B2:B200)
3. =SUM(B2:B200)
4. =AVERAGE(B2:B200)

A manager has monthly sales for four regions and wants a single chart comparing each region’s trend over the 12 months.

1. Select the data range including headers, then Insert a chart (such as a line or clustered column chart) and adjust the chart elements
2. Take a screenshot of the numbers and paste it as an image
3. Retype the values into a separate drawing app to build the chart
4. Apply conditional formatting to color the cells instead of charting