MICROSOFT CERTIFICATION
MO-201 Microsoft Excel Expert (Office 2019) Practice Exam
Exam Number: 3141 | Last updated 16-Apr-26 | 823+ questions across 4 vendor-aligned objectives
The MO-201 Microsoft Excel Expert (Office 2019) certification validates the skills of advanced users who demonstrate expert-level competency in Microsoft Excel 2019 for complex workbooks. This exam measures your ability to work with Microsoft Excel 2019, Advanced Formulas, PivotTables, Macros, Data Analysis, Power Query, demonstrating both conceptual understanding and practical implementation skills required in today’s enterprise environments.
The heaviest exam domains include Create Advanced Formulas and Macros (25–30%), Manage Advanced Charts and Tables (25–30%), and Manage and Format Data (20–25%). These areas collectively represent the majority of exam content and require focused preparation across their respective subtopics.
Additional domains tested include Manage Workbook Options and Settings (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 →
495
practice exam users
92.2%
satisfied users
90.1%
passed the exam
4.7/5
quality rating
Test your MO-201 Microsoft Excel Expert knowledge
10 of 823+ questions
Question #1 - Manage Workbook Options and Settings
A finance team shares a budget workbook. Certain cells containing approved budget figures should be locked while users can still edit forecast columns.
How should selective cell protection be configured?
A) Lock the entire workbook
B) Unlock the forecast cells (Format Cells > Protection > uncheck Locked), then Protect Sheet with a password
C) Use Track Changes instead
D) Hide the budget columns
Show solution
Correct answers: B – Explanation:
By default all cells are “locked” but protection is not enforced until Protect Sheet is enabled. Unlocking specific cells before protecting the sheet allows editing only those cells. Locking the entire workbook blocks structural changes. Track Changes records edits but does not prevent them. Hiding columns is not protection. Source: Check Source
Question #2 - Manage Workbook Options and Settings
A finance team shares a budget workbook. Approved budget cells should be locked while users can still edit forecast columns.
How should selective cell protection be configured?
A) Enable Track Changes to record edits without actually preventing modification of any cells
B) Hide the budget columns from view which can be easily unhidden by any user with basic knowledge
C) Lock the entire workbook preventing any structural changes like adding or deleting worksheets
D) Unlock forecast cells in Format Cells Protection, then Protect Sheet with a password enabled
Show solution
Correct answers: D – Explanation:
By default all cells are locked but protection is not enforced until Protect Sheet is activated. Unlocking specific forecast cells before enabling sheet protection allows editing only those designated cells. Workbook-level locking prevents structural changes like sheet insertion but does not protect individual cell contents. Track Changes records modifications after the fact but does not prevent any cell from being edited by users. Hidden columns are easily revealed through the Format menu and provide no actual data protection. Source: Check Source
Question #3 - Manage Workbook Options and Settings
A workbook pulls external SQL data. The manager wants automatic refresh every 30 minutes without user intervention.
Which connection property should be configured?
A) Set the connection refresh interval to 30 minutes with the Refresh Every option enabled
B) Export data manually from SQL Server each time a fresh copy of the information is required
C) Create a macro that runs only when the workbook file is first opened by any user session
D) Configure manual refresh only requiring the user to click Refresh Data each time it is needed
Show solution
Correct answers: A – Explanation:
Connection Properties allow setting automatic background refresh intervals that execute at the specified frequency without requiring any user action. Manual-only refresh requires someone to remember to click Refresh Data at each interval. Manual SQL export is entirely outside Excel and requires database tool access each time. A file-open macro refreshes only once at the start of the session, not continuously every 30 minutes throughout the day. Source: Check Source
Question #4 - Manage and Format Data
A dataset has addresses in a single column as “City, State ZIP”. The analyst needs to split into three separate columns.
Which Excel feature separates the data?
A) Text to Columns with comma and space delimiters splitting the combined values into columns
B) Manual retyping of each address component into three separate columns for every data row
C) Find and Replace substituting commas with empty strings without splitting into separate columns
D) Conditional Formatting applying different colors to each address component within the same cell
Show solution
Correct answers: A – Explanation:
Text to Columns splits cell content by specified delimiters into adjacent columns in a single operation across all selected rows. Manual retyping is impractical for large datasets and introduces transcription errors. Find and Replace can remove characters but cannot split a single cell into multiple separate column values. Conditional Formatting changes visual appearance of cells without modifying or splitting the underlying data structure. Source: Check Source
Question #5 - Manage and Format Data
Named ranges like Revenue_2024 are used in dozens of formulas. The analyst needs to update Revenue_2024 to include a new row.
Where should the named range be modified?
A) Change the cell contents directly without updating the name definition creating a mismatch
B) Use Name Manager to edit the Revenue_2024 reference updating all formulas using it automatically
C) Delete the named range entirely and recreate it which temporarily breaks all referencing formulas
D) Find and replace the range address individually in every formula that references the named range
Show solution
Correct answers: B – Explanation:
Name Manager allows editing the reference of any named range in one centralized location, and all formulas using that name automatically point to the updated range. Find and replace in individual formulas is risky, tedious, and may miss some references. Deleting and recreating temporarily breaks all dependent formulas and may cause calculation errors. Changing cells without updating the name creates a mismatch between the name definition and actual data location. Source: Check Source
Question #6 - Create Advanced Formulas and Macros
An inventory report needs product price lookup where the formula works even if columns are inserted between the lookup and return columns.
Which formula is most resilient to column changes?
A)
B) #N/A
C)
D)
Show solution
Correct answers: B – Explanation:
INDEX-MATCH references the specific return column directly by its letter designation rather than a relative column number, so inserting columns between lookup and return does not break the formula. VLOOKUP with column number 4 breaks immediately when a column is inserted between A and D, shifting the return position. HLOOKUP searches horizontally across rows which does not match the typical vertical column-based data layout. LOOKUP has limited functionality, cannot specify exact match mode, and has weaker error handling capabilities. Source: Check Source
Question #7 - Create Advanced Formulas and Macros
A loan calculator computes monthly payment for a $250,000 mortgage at 6.5% annual interest over 30 years.
Which Excel function calculates the monthly payment?
A)
B)
C)
D)
Show solution
Correct answers: A – Explanation:
PMT calculates the periodic payment given rate per period (annual rate divided by 12), total number of periods (30 years times 12), and present value (loan amount). SUM with simple multiplication does not account for compound interest amortization over the loan life. FV calculates the future accumulated value rather than the payment needed to amortize the present value. RATE solves for the interest rate given other loan parameters rather than computing the payment amount. Source: Check Source
Question #8 - Create Advanced Formulas and Macros
A manager automates weekly formatting: bold header, currency format column C, auto-fit all columns. This runs on new files.
Which approach automates this?
A) Use conditional formatting for all three requirements even though it cannot auto-fit columns
B) Send detailed written instructions to each team member describing how to format their files
C) Record a macro performing all three formatting steps and assign it to a Quick Access button
D) Perform the identical formatting steps manually each week on every new data file received
Show solution
Correct answers: C – Explanation:
Recording a macro captures the complete formatting sequence as reusable VBA code, and a toolbar button enables one-click execution on any new data file. Manual repetition each week wastes time and produces inconsistent results depending on who formats. Conditional formatting can apply visual styles based on rules but cannot bold specific rows or auto-fit column widths. Written instructions depend on voluntary compliance and consistent interpretation by each team member. Source: Check Source
Question #9 - Manage Advanced Charts and Tables
A sales manager needs total revenue by region and category in a cross-tabulated format with drill-down capability.
Which Excel feature provides this interactive summarization?
A) A standard filtered table showing raw data rows without cross-tabulation or aggregation
B) PivotTable with Region as rows, Category as columns, and Revenue as values with drill-through
C) A standalone chart visual showing aggregated data without the interactive tabular drill-down
D) SUMIFS formulas in a manually constructed grid requiring individual formula per cell intersection
Show solution
Correct answers: B – Explanation:
PivotTables dynamically cross-tabulate data with drag-and-drop field placement, automatic aggregation, and interactive drill-down to source records at any intersection. SUMIFS require constructing individual formulas for every row-column intersection in a manual grid layout. Standard tables show raw data rows with filters but do not aggregate or cross-tabulate values at category intersections. Charts visualize aggregated data but lack the interactive tabular drill-down to underlying source records. Source: Check Source
Question #10 - Manage Advanced Charts and Tables
A PivotTable shows quarterly sales. The manager wants quick visual filtering to show only Q3 and Q4 data with clickable buttons.
Which feature provides this interactive filtering?
A) Create a completely separate PivotTable for each quarter duplicating the analysis four times
B) Use only the PivotTable filter dropdown which is functional but less visually prominent
C) Manually hide the rows for Q1 and Q2 data which must be re-hidden each time data refreshes
D) Insert a Slicer connected to the PivotTable Quarter field providing visual toggle buttons
Show solution
Correct answers: D – Explanation:
Slicers provide prominent visual toggle buttons for interactive PivotTable filtering, making it easy and intuitive to select which quarters to display or hide. Manually hiding rows requires re-hiding after each data refresh since hidden rows reset when PivotTable data updates. The dropdown filter works functionally but lacks the visual prominence and multi-select usability that Slicers provide. Separate PivotTables per quarter quadruple the maintenance effort and prevent unified quarter comparison. Source: Check Source
Get 823+ 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-201 Microsoft Excel Expert exam measures
- Manage Workbook Options and Settings (15–20%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
- Manage and Format Data (20–25%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
- Create Advanced Formulas and Macros (25–30%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
- Manage Advanced Charts and Tables (25–30%) — 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:
- Senior Data Analyst: $65,000–$90,000 per year (based on Glassdoor and ZipRecruiter data)
- Financial Analyst: $70,000–$100,000 per year (based on Glassdoor and ZipRecruiter data)
- Business Intelligence Analyst: $75,000–$105,000 per year (based on Glassdoor and ZipRecruiter data)
Official resources
Microsoft provides comprehensive free training to prepare for the MO-201 Microsoft Excel Expert (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.