Why Study with PlanetCert?
The Latest Questions
Practice questions and exam topics aligned with the current exam objectives.
Detailed Explanations
Go beyond the answer. Master the material with comprehensive learning and professional explanations for every concept.
AI-Powered Insights
Personalized preparation guidance that adapts to your performance and identifies weak spots automatically.
Exam Information
Official specifications published by Microsoft
Exam Format
Registration
Validity
Skills this practice test covers
- Protect worksheets, cell ranges and workbook structure, and set formula calculation options for shared files.
- Copy macros between workbooks, reference data in other workbooks and manage workbook versions.
- Write nested IF, IFS and SWITCH formulas, and look up values with INDEX and MATCH.
- Run what-if analysis with Goal Seek and Scenario Manager, and calculate loan payments with PMT.
- Trace precedents and dependents, watch cells in the Watch Window and evaluate formulas step by step.
- Build PivotTables with slicers, grouped data and calculated fields, plus Waterfall, Funnel and Map charts.
Explore related topics
MO-201 exam content
4 domains · 57 topics · 270 of 425 questions are written for MO-201
Manage workbook options and settings42 questions
This domain prepares a workbook for other people to use. Candidates move macros between workbooks, link to data in other files, protect sheets and structure, and set calculation and language options.
Skills tested
- Copy a macro from one workbook to another, and enable macros in a workbook.
- Reference cells in another workbook, and recover or manage workbook versions.
- Restrict editing, and protect worksheets, cell ranges and workbook structure.
- Choose manual or automatic formula calculation for a large workbook.
- Configure editing and display languages, and manage comments.
Questions by topic
- Copy macros between workbooks4
- Protect workbook structure5
- Protect worksheets and cell ranges6
- Reference data in other workbooks5
- Configure formula calculation options4
- Manage comments3
- Manage workbooks5
- Prepare workbooks for collaboration7
- Use and configure language options3
Manage and format data50 questions
This domain is about entering and cleaning data quickly. Candidates fill cells from patterns, build custom number formats, validate input, add subtotals and write conditional formatting rules that use formulas.
Skills tested
- Fill cells with Flash Fill and with advanced Fill Series options.
- Create custom number formats, and data validation rules with input messages.
- Group and ungroup data, insert subtotals and remove duplicate records.
- Write a conditional formatting rule that uses a formula, then manage the order of rules.
Questions by topic
- Create conditional formatting rules that use formulas3
- Remove duplicate records4
- Configure data validation6
- Fill cells by using Flash Fill4
- Create custom number formats5
- Calculate data by inserting subtotals and totals6
- Fill cells by using advanced Fill Series options5
- Fill cells based on existing data5
- Format and validate data7
- Apply advanced conditional formatting and filtering3
- Create custom conditional formatting rules2
Create advanced formulas and macros98 questions
The largest domain covers the formulas behind expert workbooks: nested logic, lookups, dates, what-if analysis and financial functions. It ends with auditing formulas and recording simple macros.
Skills tested
- Nest IF, IFS and SWITCH, and aggregate with SUMIFS, COUNTIFS, MAXIFS and MINIFS.
- Look up values with VLOOKUP, HLOOKUP, or INDEX and MATCH, and calculate dates with WEEKDAY and WORKDAY.
- Run Goal Seek, Scenario Manager and Consolidate, and forecast loans with PMT and NPER.
- Trace precedents and dependents, use the Watch Window and evaluate formulas step by step.
- Record, name and edit simple macros.
Questions by topic
- Perform what-if analysis by using Goal Seek and Scenario Manager5
- Perform logical operations by using nested functions including the IF(),...AVERAGEIFS()... functions2
- Evaluate formulas3
- Look up data by using the VLOOKUP(), HLOOKUP(), MATCH(), and INDEX() functions2
- Record simple macros4
- Calculate dates by using the WEEKDAY() and WORKDAY() functions3
- Monitor cells and formulas by using the Watch Window4
- Perform logical operations by using nested functions including the IF(), IFS(), SWITCH()... functions2
- Perform logical operations by using nested functions including the... MAXIFS()... functions3
- Edit simple macros5
- Calculate financial data by using the PMT() function9
- Trace precedence and dependence3
- Summarize data from multiple ranges by using the Consolidate feature5
- Perform logical operations in formulas16
- Look up data by using functions7
- Use advanced date and time functions5
- Perform data analysis9
- Troubleshoot formulas6
- Create and modify simple macros5
Manage advanced charts and tables80 questions
This domain turns data into charts and summaries. Candidates build dual axis charts and chart types such as Box & Whisker, Funnel, Map and Waterfall, then create PivotTables and PivotCharts that others can filter.
Skills tested
- Create a dual axis chart to compare two series on different scales.
- Build Box & Whisker, Combo, Funnel, Histogram, Map, Sunburst and Waterfall charts.
- Create PivotTables with slicers, grouped data and calculated fields.
- Style PivotCharts and drill down into their details.
Questions by topic
- Group PivotTable data9
- Create and modify dual axis charts7
- Create and modify charts including Box & Whisker, Combo, Funnel, Histogram, Map, Sunburst, and Waterfall charts13
- Add calculated fields6
- Create slicers7
- Drill down into PivotChart details5
- Create and modify advanced charts7
- Create and modify PivotTables6
- Create and modify PivotCharts2
- Modify field selections and options3
- Create and modify charts including... Waterfall charts2
- Create and modify charts including... Sunburst2
- Create and modify charts including... Map3
- Create and modify charts including... Funnel1
- Create and modify charts including... Histogram2
- Format data3
- Create and modify charts including... Box & Whisker1
- Create PivotTables1
Requirements
- No formal prerequisites: anyone can book the exam.
- Recommended: the Microsoft Office Specialist: Excel Associate (Office 2019) certification or equal Excel skills.
- Recommended: extensive instruction and hands-on practice with Excel 2019, to the level of an industry expert.
About the MO-201 exam
Microsoft's MO-201 exam is for Excel 2019 users who build complex workbooks and want an expert-level Microsoft Office Specialist credential. Microsoft gives examples of such workbooks: custom business templates, multiple-axis financial charts, amortization tables and inventory schedules. The exam covers workbook options and settings, data management and formatting, advanced formulas and macros, and advanced charts and tables. Passing earns the Microsoft Office Specialist: Excel Expert (Office 2019) certification.
In the PlanetCert practice test, Create advanced formulas and macros has the most questions, and it is also the largest domain in Microsoft's skills outline. The explanations show why one function fits a task better than another, such as INDEX and MATCH when the value to return sits left of the lookup column. Study Mode suits the formula auditing tasks, where each step of an evaluation matters. Brainy AI Assistant can explain why PMT needs the yearly rate divided by twelve for monthly payments, or rewrite a nested IF as an IFS formula.
How to earn the certification
- Earns
- Microsoft Office Specialist: Excel Expert (Office 2019)Expert
Microsoft Office Specialist certifications do not expire.
- After
- PL-300 Power BI Data Analyst
Microsoft's associate exam for analysts who model data and build reports in Power BI. It takes analysis beyond Excel.
- PL-300 Power BI Data Analyst
How to prepare for MO-201
- 1
Practise in Excel 2019 itself
The exam asks candidates to complete tasks in Excel, so practise in the desktop version it was written for. Functions that arrived later in Microsoft 365, such as XLOOKUP, are not in the MO-201 skills outline.
- 2
Rebuild Microsoft's sample workbooks
Microsoft names amortization tables, inventory schedules, multiple-axis financial charts and business templates as typical expert workbooks. Build one of each from a blank sheet, with PMT, data validation and a dual axis chart.
- 3
Give formulas the most hours
Create advanced formulas and macros is the largest domain. Practise nested IF, IFS and SWITCH, SUMIFS and MAXIFS, INDEX and MATCH, WORKDAY and Goal Seek until each one works without a hint.
- 4
Audit and lock a shared workbook
Trace precedents, add cells to the Watch Window and step through a broken formula with Evaluate Formula. Then protect the sheets and the structure, and switch calculation to manual to see what changes.
- 5
End with timed practice
Take the MO-201 practice test in Timed Mode once each domain feels solid. Review the explanation for every missed PivotTable or chart task, and ask Brainy AI Assistant for another way to reach the same result.
Start a timed MO-201run
Microsoft lists no official training course for MO-201, so its skills outline is the main study reference. Microsoft Press publishes the MOS 2019 Study Guide for Microsoft Excel Expert.
Who MO-201 is for
- Financial analysts and accountants who build models, loan schedules and reports in Excel.
- Data analysts who summarize long lists with PivotTables, lookups and conditional formatting.
- Office and administrative staff who maintain shared workbooks with protected ranges and macros.
- Students who want an expert-level Excel credential before they start a business career.
Typical job titles: Financial analyst · Data analyst · Business analyst · Reporting analyst · Accountant · Administrative specialist
MO-201 glossary
16 terms from the exam objectives and the vendor's official material, each defined in a sentence.
- Box & Whisker
- A chart that shows how values spread, with a box for the middle half of the data and lines for the rest.
- Calculated fields
- PivotTable fields whose values come from a formula over other fields, such as sales minus cost.
- Consolidate
- An Excel feature that combines data from several ranges or sheets into one summary, for example by adding them up.
- Custom number formats
- Format codes that control how a number looks, such as showing negative values in red, without changing the value.
- Data validation
- Rules that limit what can be typed into a cell, with an optional input message and an error alert.
- Dual axis charts
- Charts with a second vertical axis, so two data series with very different scales can share one chart.
- Error checking rules
- Excel's background checks that flag likely formula problems, such as a formula that leaves out cells next to its range.
- Flash Fill
- An Excel feature that fills a column by copying a pattern it sees in the first entries, such as splitting full names into first names.
- Goal Seek
- A what-if tool that changes one input cell until a formula returns the result the user asks for.
- INDEX and MATCH
- Two functions used together: MATCH finds the position of a value, and INDEX returns the item at that position.
- PMT
- A financial function that returns the regular payment on a loan from its interest rate, number of payments and amount.
- Protect workbook structure
- A protection setting that stops users from adding, deleting, moving or renaming the sheets in a workbook.
- Scenario Manager
- A what-if tool that saves sets of input values as named scenarios, so their results can be compared.
- Slicers
- Buttons linked to a PivotTable or PivotChart that filter its data with one click.
- Subtotal
- An Excel command that inserts summary rows, such as sums, for each group in a sorted list.
- Watch Window
- A small window that keeps showing the value and formula of chosen cells while the user works elsewhere in the workbook.