MICROSOFT CERTIFICATION

MO-211 Excel Expert (Microsoft 365 Apps) Practice Exam

Exam Number: 3143 | Last updated 16-Apr-26 | 825+ questions across 4 vendor-aligned objectives

The MO-211 Excel Expert (Microsoft 365 Apps) certification validates the skills of advanced users who demonstrate expert-level competency in Microsoft Excel within Microsoft 365 Apps. This exam measures your ability to work with Microsoft Excel (Microsoft 365), Advanced Formulas, PivotTables, Power Query, Power Pivot, Macros, 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.

♥  Expert-level exam covering Microsoft 365 Excel. In addition to MO-201 topics, expect Power Query transformations, Power Pivot data models, and LET/LAMBDA custom functions. Hands-on practice is non-negotiable.

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 →

298

practice exam users

95.1%

satisfied users

93.9%

passed the exam

4.2/5

quality rating

Test your MO-211 Excel Expert knowledge

10 of 825+ questions

Question #1 - Manage Workbook Options and Settings

A financial analyst creates a complex model with custom views — one showing summary data and another showing detailed calculations. They need to switch between views instantly.

Which Excel feature saves and restores different view configurations?

A) Custom Views that save display settings, filter states, and print settings
B) Separate workbooks for each view
C) Separate worksheets
D) Screenshot comparisons

 

Correct answers: B – Explanation:
Custom Views save the current display state including hidden rows/columns, filter settings, and print area, allowing instant switching between perspectives. Separate workbooks fragment the model. Separate sheets duplicate data. Screenshots are static. Source: Check Source

A financial analyst creates a model with custom views — one showing summary and another showing detailed calculations.

Which Excel feature saves and restores different view configurations?

A) Custom Views saving display settings, filter states, and print configurations for instant recall
B) Screenshots of each view configuration stored as static images for visual reference only
C) Separate workbooks for each view fragmenting the model across multiple disconnected files
D) Separate worksheets duplicating data between summary and detail sheets within one workbook

 

Correct answers: A – Explanation:
Custom Views save the current display state including hidden rows, columns, filter settings, and print area, enabling instant switching between perspectives without data duplication. Separate workbooks fragment the model requiring manual synchronization between files. Separate worksheets duplicate data creating consistency risks when the source changes. Screenshots are static images that cannot restore interactive display configurations or filter states. Source: Check Source

A financial model has a circular reference where interest depends on balance which depends on interest. Excel shows an error.

What should the analyst enable?

A) Create a VBA workaround writing custom code to manually iterate the calculation in a loop
B) Accept the circular reference error and work around it using approximate values entered manually
C) Delete the circular formula eliminating the interdependent calculation logic from the model
D) Enable Iterative Calculation in File Options Formulas with configured maximum iteration passes

 

Correct answers: D – Explanation:
Iterative Calculation allows Excel to resolve circular references through repeated calculation passes up to a configured maximum, converging on the correct interdependent values. Deleting the formula removes essential financial model logic that requires the circular dependency. VBA workarounds add unnecessary programming complexity when the built-in iterative calculation feature handles this natively. Accepting errors and using approximations produces inaccurate financial model results that compound over time. Source: Check Source

An analyst receives inconsistent external data needing import, column splitting, type conversion, and error removal in a repeatable pipeline.

Which M365 Excel feature provides this repeatable data transformation?

A) Manual cleanup repeated from scratch every time new data is received from the external source
B) A recorded VBA macro that may break when source data structure or formatting changes
C) Power Query with saved transformation steps that replay automatically on each data refresh
D) Text to Columns applied as a single one-time operation without recording steps for replay

 

Correct answers: C – Explanation:
Power Query records each transformation step in a saved query that replays automatically when data refreshes, creating a fully repeatable and maintainable pipeline. Manual cleanup each time wastes effort and produces inconsistent results depending on who performs it. Text to Columns handles one splitting operation but cannot record a full multi-step transformation pipeline. VBA macros work but are fragile when source data structures change, unlike Power Query visual step editing. Source: Check Source

Using Power Query, an analyst merges Sales and Products tables on Product_ID to bring product names into sales data.

Which Power Query operation should be used?

A) VLOOKUP formulas in the worksheet performing row-by-row lookups after data has been loaded
B) Append Queries stacking rows from both tables vertically without any column-level joining
C) Merge Queries performing a join operation between tables on the shared Product_ID column
D) Copy and paste between the tables in the worksheet manually matching rows by visual inspection

 

Correct answers: C – Explanation:
Merge Queries performs a relational join between tables on specified key columns, combining columns from both sources in the Power Query transformation layer before loading. Append stacks rows vertically from multiple tables without the column-level joining needed to bring product names into sales records. Manual copy-paste matching is error-prone and does not create a repeatable transformation step. VLOOKUP operates after loading and does not leverage Power Query’s built-in join transformation capability. Source: Check Source

A data scientist creates a reusable CAGR function using LAMBDA in M365 Excel with start value, end value, and years parameters.

Which formula defines this named LAMBDA function?

A) A VBA function module that works but is not the native M365 LAMBDA approach for custom functions
B) #NAME?
C) An IF statement that evaluates conditions but cannot define reusable parameterized function logic
D) A regular cell formula that cannot be parameterized or saved for reuse with different inputs

 

Correct answers: B – Explanation:
LAMBDA creates a custom function with named parameters that can be saved to a Name for reuse as =CAGR(100,150,5) anywhere in the workbook like a built-in function. Regular cell formulas cannot accept parameters or be saved as reusable named functions callable from other cells. VBA user-defined functions work but LAMBDA is the native M365 approach that does not require developer tools or macro-enabled files. IF evaluates conditions but cannot define reusable parameterized function logic that accepts different inputs each time. Source: Check Source

An analyst needs a formula defining a variable once and using it multiple times to avoid redundant recalculation within the formula.

Which M365 function enables this?

A) Nested IF function evaluating sequential conditions without intermediate variable assignment
B) LET function assigning names to intermediate calculations reused multiple times in the formula
C) CONCATENATE function joining text strings without any variable definition or reuse capability
D) INDIRECT function creating dynamic cell references without intermediate calculation naming

 

Correct answers: B – Explanation:
LET assigns names to sub-expressions within a formula, enabling reuse without redundant recalculation and improving both performance and readability of complex formulas. Nested IF evaluates cascading conditions but cannot assign named variables for reuse within the formula. CONCATENATE joins text strings and has no variable definition or intermediate calculation capability. INDIRECT creates dynamic cell references from text strings without any named variable or calculation reuse. Source: Check Source

A VBA macro needs to loop through table rows and highlight those where Status column contains “Overdue”.

Which VBA approach should the developer use?

A) A For Each loop iterating through ListObject rows checking Status and applying Interior Color
B) Type Overdue in the Find search box which locates matching cells without applying formatting
C) Use conditional formatting which applies visual rules without VBA programming requirement
D) Record a macro using the macro recorder which cannot handle conditional loop logic effectively

 

Correct answers: A – Explanation:
A programmatic loop through the ListObject table rows evaluates each Status value and applies Interior.Color formatting precisely to matching rows with full programming control. The macro recorder captures sequential actions but cannot handle conditional branching or looping logic effectively. Conditional formatting applies rules without VBA but the question specifically asks about a VBA macro approach for this task. Find locates matching cells for navigation but does not apply background color formatting to the found results. Source: Check Source

An analyst builds a Power Pivot data model connecting Sales, Products, and Customers. They need a DAX measure for total revenue.

Which DAX measure should be created?

A)
B)
C)
D)

 

Correct answers: D – Explanation:
SUM aggregates all revenue values from the Sales table into a single total, which is the standard measure for calculating total revenue across all transactions. COUNT returns the number of cells containing values without summing them, providing a transaction count rather than revenue total. AVERAGE calculates the arithmetic mean which divides the total by the count, not the total itself. MAX returns only the single largest value rather than accumulating all revenue across the table. Source: Check Source

A PivotTable from Power Pivot needs each product’s contribution shown as percentage of the grand total alongside raw values.

Which PivotTable feature shows values as a percentage of the grand total?

A) Calculate percentages manually in a helper column added adjacent to the PivotTable output
B) Show Value As set to Percent of Grand Total in the Value Field Settings dropdown options
C) Create a completely separate chart visual that displays percentages independently from the table
D) Write a DAX DIVIDE measure which works but is unnecessary complexity for this built-in feature

 

Correct answers: B – Explanation:
Show Value As provides built-in calculation options including Percent of Grand Total directly within the PivotTable without helper columns or additional measures. Helper columns require manual formula construction outside the PivotTable structure and may break on refresh. Separate charts duplicate the visualization without integrating percentages into the tabular analysis view. DAX DIVIDE measures work but add unnecessary formula complexity for a calculation that is available as a native PivotTable setting. Source: Check Source

Get 825+ 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.

Start free 24-hour trial — no credit card

Exam mode & learn mode · Score by objective · Updated 16-Apr-26

Learn more...

What the MO-211 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.

  • 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

Earning this certification can open doors to several in-demand roles:

Microsoft provides comprehensive free training to prepare for the MO-211 Excel Expert (Microsoft 365 Apps) 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.

Related certifications to explore

MO-210 Excel Associate (Microsoft 365 Apps)
MO-101 Microsoft Word Expert (Office 2019)
MO-100 Word Associate (Office 2019)
AWS Solutions Architect Associate
CompTIA A
Salesforce Administrator

Related reading from our Learning Hub

Visit Our Learning Hub
PowerKram vs FocusOnForce
Visit Our Learning Hub
PowerKram vs MCMGuides