MICROSOFT CERTIFICATION
MO-200 Excel Associate (Office 2019) Practice Exam
Exam Number: 3140 | Last updated 16-Apr-26 | 822+ questions across 4 vendor-aligned objectives
The MO-200 Excel Associate (Office 2019) certification validates the skills of users who demonstrate foundational competency in Microsoft Excel 2019 for data management and analysis. This exam measures your ability to work with Microsoft Excel 2019, Formulas, Charts, Tables, Conditional Formatting, Data Validation, demonstrating both conceptual understanding and practical implementation skills required in today’s enterprise environments.
The heaviest exam domains include Perform Operations by Using Formulas and Functions (25–30%), Manage Data Cells and Ranges (20–25%), and Manage Worksheets and Workbooks (15–20%). These areas collectively represent the majority of exam content and require focused preparation across their respective subtopics.
Additional domains tested include Manage Tables and Table Data (15–20%), and Manage Charts (15–20%). Together, these areas round out the full exam blueprint and ensure candidates possess well-rounded expertise across the certification scope.
Every answer links to the source. Each explanation below includes a hyperlink to the exact Microsoft documentation page the question was derived from. PowerKram is the only practice platform with source-verified explanations. Learn about our methodology →
601
practice exam users
95%
satisfied users
89.4%
passed the exam
4.9/5
quality rating
Test your MO-200 Excel Associate knowledge
10 of 822+ questions
Question #1 - Manage Worksheets and Workbooks
An accountant maintains a workbook with 12 monthly sheets. They need to quickly group the Q1 sheets (Jan, Feb, Mar) to apply identical formatting changes to all three simultaneously.
Which technique groups multiple sheets for simultaneous editing?
A) Copy formatting with Format Painter between sheets one at a time
B) Edit each sheet individually
C) Create a macro for each formatting change
D) Hold Ctrl and click each sheet tab to select multiple sheets, then make changes
Show solution
Correct answers: B – Explanation:
Ctrl-clicking sheet tabs creates a group — any edits apply to all grouped sheets simultaneously. Individual editing triples the work. Format Painter works within sheets but requires separate application to each. Macros add complexity for a built-in feature. Source: Check Source
Question #2 - Manage Worksheets and Workbooks
An accountant maintains a workbook with 12 monthly sheets. They need to apply identical formatting to Jan, Feb, and Mar simultaneously.
Which technique groups multiple sheets for simultaneous editing?
A) Edit each of the three monthly sheets individually applying the same formatting separately
B) Copy formatting with Format Painter from one sheet and paste it to each target sheet one at a time
C) Hold Ctrl and click each sheet tab to select them as a group then make formatting changes
D) Create a VBA macro to automate the formatting changes across all three sheets programmatically
Show solution
Correct answers: C – Explanation:
Ctrl-clicking sheet tabs creates a group where any formatting edits apply to all grouped sheets simultaneously without repeating the work. Individual editing requires performing identical steps three separate times. Format Painter requires selecting a source, switching sheets, and painting each target individually. VBA macros add programming complexity for what is a simple built-in grouping feature available in two clicks. Source: Check Source
Question #3 - Manage Worksheets and Workbooks
A financial analyst prints a large spreadsheet across multiple pages. Column headers from row 1 should appear on every printed page.
Which Page Layout setting ensures headers print on every page?
A) Use Freeze Panes which only affects the screen display without any impact on printed output
B) Set Print Titles with Rows to Repeat at Top configured to include the header row
C) Reduce the entire print area to fit everything on a single page regardless of readability
D) Manually copy the header row to the top of each page in the spreadsheet before printing
Show solution
Correct answers: B – Explanation:
Print Titles repeats designated rows at the top of every printed page automatically as part of the page setup configuration. Manual header copying breaks when content changes cause page breaks to shift positions. Freeze Panes locks rows on screen during scrolling but has no effect on how pages print. Reducing everything to one page makes content unreadably small for large datasets with many rows and columns. Source: Check Source
Question #4 - Manage Data Cells and Ranges
A sales team enters data where the Region column should only accept values from a predefined list: North, South, East, West.
Which feature restricts cell input to a predefined list?
A) Cell protection that locks the cells entirely preventing any input or modification by users
B) Data Validation with a List setting containing the allowed values presenting a dropdown selector
C) Conditional formatting rules that highlight cells containing values not in the expected set
D) A comment annotation listing the valid options as informational guidance text for data entry
Show solution
Correct answers: B – Explanation:
Data Validation with List creates a dropdown restricting input to predefined values only, preventing invalid entries at the time of data entry. Conditional formatting highlights invalid values visually but does not prevent them from being entered. Cell protection locks cells completely preventing all input rather than restricting it to valid values. Comments provide informational text that users may ignore without any enforcement of valid input values. Source: Check Source
Question #5 - Manage Data Cells and Ranges
A warehouse manager has a parts list where items with fewer than 10 units should be highlighted in red automatically.
Which feature automatically highlights these low-stock items?
A) Manually color each cell red by hand reviewing the stock quantity in every row individually
B) Conditional Formatting rule applying red fill when the quantity cell value is less than 10
C) Data Validation configured to reject entry of values below 10 rather than highlighting them
D) A VLOOKUP formula that retrieves stock status text without applying any visual cell formatting
Show solution
Correct answers: B – Explanation:
Conditional Formatting applies visual formatting dynamically based on cell values — when stock drops below 10, the red fill appears and disappears automatically with data changes. Manual coloring does not update automatically when stock quantities change through new transactions. Data Validation restricts what values can be entered but does not apply visual highlighting to existing values. VLOOKUP retrieves data from lookup tables but does not change the visual formatting of cells. Source: Check Source
Question #6 - Manage Data Cells and Ranges
Product names have inconsistent trailing spaces causing VLOOKUP mismatches. The data needs cleaning.
Which function removes leading and trailing spaces?
A) UPPER converting all lowercase characters to uppercase without affecting spaces or spacing
B) LEN counting the total number of characters including spaces without removing any of them
C) REPLACE substituting specific characters at defined positions within the text string
D) TRIM removing all leading, trailing, and excess interior spaces from text string values
Show solution
Correct answers: D – Explanation:
TRIM removes all leading and trailing spaces and reduces multiple interior spaces to single spaces, fixing VLOOKUP mismatches caused by invisible whitespace. REPLACE substitutes characters at specific positions but requires knowing exactly where the spaces are located. LEN returns the character count including spaces as a number without modifying the text. UPPER changes letter case to uppercase without removing or modifying any space characters in the string. Source: Check Source
Question #7 - Manage Tables and Table Data
An HR manager tracks 500 employees needing filter, sort, frozen headers when scrolling, and auto-expanding ranges.
Which data structure should be used?
A) Create separate worksheets per department splitting the 500-employee dataset across sheets
B) Use a plain range of cells without any table conversion relying on manual filter application
C) Convert the data to an Excel Table providing auto-filter, sort, and frozen header features
D) Build a PivotTable only which summarizes and aggregates data without the list editing experience
Show solution
Correct answers: C – Explanation:
Excel Tables provide automatic filtering dropdowns, structured sorting, frozen headers during scrolling, and dynamic range expansion when new rows are added. Plain ranges lack these built-in features and require manual filter and freeze pane setup. Separate worksheets per department fragment the employee dataset making organization-wide analysis difficult. PivotTables summarize and aggregate data but do not provide the editable list browsing experience needed for record management. Source: Check Source
Question #8 - Manage Tables and Table Data
A sales data table needs a total row showing the sum of Revenue and the count of transactions.
How should the Total Row be added to an Excel Table?
A) Enable the Total Row checkbox in Table Design then select aggregate functions for each column
B) Use SUBTOTAL function in an adjacent cell outside the table structure for each aggregation
C) Create a separate summary worksheet with formulas referencing the table data from another sheet
D) Manually type SUM formulas in cells below the table which break when the table expands rows
Show solution
Correct answers: A – Explanation:
The Table Total Row provides integrated dropdown aggregate function selection per column (Sum, Count, Average) that automatically adjusts when the table grows or shrinks. Manual formulas below the table break when new rows push them down or the table auto-expands past them. Separate summary sheets disconnect the totals from the visual context of the data table. Adjacent SUBTOTAL functions work but are not part of the integrated table structure and require manual range management. Source: Check Source
Question #9 - Perform Operations by Using Formulas and Functions
A teacher assigns letter grades based on scores: 90 = A, 80-89 = B, 70-79 = C, below 70 = F.
Which formula correctly assigns grades using nested logic?
A)
B) #NAME?
C) #VALUE!
D) #NAME?
Show solution
Correct answers: D – Explanation:
Nested IF evaluates conditions sequentially from highest to lowest, assigning the correct grade for each score range through cascading logic. VLOOKUP with approximate match against a boundary table also works but the IF approach directly addresses the nested logic requirement. GRADE is not a standard Excel function and would produce an error. CHOOSE requires a specific index number (1, 2, 3, 4) and cannot directly evaluate continuous score ranges for grade assignment. Source: Check Source
Question #10 - Perform Operations by Using Formulas and Functions
An analyst needs to count how many orders in column B exceed $1,000.
Which function should be used?
A)
B)
C)
D)
Show solution
Correct answers: A – Explanation:
COUNTIF counts cells meeting a specified condition — here, values exceeding 1000 — returning the number of qualifying orders. COUNT counts all cells containing any numeric value regardless of whether they exceed the threshold. SUM totals all values in the range returning a dollar amount rather than a count of qualifying orders. AVERAGE calculates the mean value across all cells without identifying or counting those exceeding a specific threshold. Source: Check Source
Get 822+ more questions with source-linked explanations
Every answer traces to the exact Microsoft documentation page — so you learn from the source, not just memorize answers.
Exam mode & learn mode · Score by objective · Updated 16-Apr-26
Learn more...
What the MO-200 Excel Associate exam measures
- Manage Worksheets and Workbooks (15–20%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
- Manage Data Cells and Ranges (20–25%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
- Manage Tables and Table Data (15–20%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
- Perform Operations by Using Formulas and Functions (25–30%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
- Manage Charts (15–20%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
How to prepare for this exam
- Review the official exam guide to understand every objective and domain weight before you begin studying
- Complete the relevant Microsoft Learn learning path to build a structured foundation across all exam topics
- Get hands-on practice in an Azure free-tier sandbox or trial environment to reinforce what you have studied with real configurations
- Apply your knowledge through real-world project experience — whether at work, in volunteer roles, or contributing to open-source initiatives
- Master one objective at a time, starting with the highest-weighted domain to maximize your score potential early
- Use PowerKram learn mode to study by individual objective and review detailed explanations for every question
- Switch to PowerKram exam mode to simulate the real test experience with randomized questions and timed conditions
Career paths and salary outlook
Earning this certification can open doors to several in-demand roles:
- Data Entry Specialist: $45,000–$60,000 per year (based on Glassdoor and ZipRecruiter data)
- Office Administrator: $50,000–$65,000 per year (based on Glassdoor and ZipRecruiter data)
- Junior Data Analyst: $55,000–$75,000 per year (based on Glassdoor and ZipRecruiter data)
Official resources
Microsoft provides comprehensive free training to prepare for the MO-200 Excel Associate (Office 2019) exam. Start with the official Microsoft Learn learning path for structured, self-paced modules covering every exam domain. Review the exam study guide for the complete skills outline and recent updates.